Power BI Blog: Dating Part 3

Welcome back to this week’s edition of the Power BI blog series. This week, we continue to develop the simple Date Table, Calendar1.

Last time, we generated a simple Date Table using the DAX CALENDAR() function, which we called Calendar1. We used Calendar1 to link the other tables in our simple Data Model:

This enabled us to display the data from Costs and Sales correctly in a matrix visualisation:

This time, we will create the additional columns on Calendar1 that we will need to drill up and down into our data. We would like to be able to drill from year to quarter to month to day. Since Calendar1 has been created by using the DAX function CALENDAR(), we will add the new columns from the Data tab. We will look at creating a Date Table using the Power Query engine later in the series.

Before we add any new columns, we should mark Calendar1 as a Date Table from the ‘Table tools’ tab. This will:

• check the designated Date column to make sure it is populated with contiguous unique dates
• ensure any time intelligence calculations using Calendar1 are performed correctly.

We are prompted to provide the ‘column to be used for the date’.

Having selected Date as the column holding the dates, notice the warning on the page:

This is not an issue for this model, as we opted to remove the automatic Date Table creation in Power BI Blog: Dating Part 1 . We click OK and add the first new column, Year:

The DAX code we have used is:

The YEAR() function extracts the year from the Date column.  Similarly, we can add the quarter:

The DAX code we have used is:

The QUARTER() function extracts the quarter from the Date column.  We add the Month Number next:

The DAX code we have used is:

The MONTH() function extracts the month number from the Date column.  Finally for now, we add the Day number.

The DAX code we have used is:

The DAY() function extracts the day number from the Date column.

We now have the columns we need to create a date hierarchy to allow us to drill into our data, and we will create the hierarchy next time.

Check back next week for more Power BI tips and tricks!