A Date with Microsoft Power BI - Part 2

This blog post is about how to create a date table in Microsoft Power BI. In the last blog post, we looked at what happens when you import data with a date column into Power BI. We identified a few limitations when you don't change the defaults. If you’ve not read it, take a look.

So why do you need a separate date table? Here’s two reasons:

• To report on time periods that are different from the standard ones
• To report on time periods that span more than one year.

There are several ways of creating a date table:

• Import it – from a database or data warehouse or another service
• Create it using M
• Create it using DAX

To create a new table with a single column of type date/time using DAX:

• In Power BI Desktop, select Modeling.
• From the Calculations Group, click New Table. The DAX panel appears.
• Rename the table name to MyCalendar and to the right of the equals sign type:

• MyCalendar = CALENDAR (DATE(2020,01,01), DATE(2020,12,31))
• Click the tick symbol to create the table.

This creates a single column date table from the first date to the second date. The syntax is:

CALENDAR(<start_date>, <end_date>)

The start_date and end_date must be valid date values, so use the DATE function with the syntax:

DATE(<year>, <month>, <day>)

Alternatively, you can use a value from your data, such as:

MyCalendar = CALENDAR(MIN(Sale[OrderDate]), MAX(Sale[OrderDate]))

The important thing is that the date table contains the full range of dates in your data, with no gaps.

There is an alternative DAX function - CALENDARAUTO() which scans your data, and finds the first and last dates and creates an appropriate date table. This has one optional parameter – fiscal_year_end_month.

CALENDARAUTO([fiscal_year_end_month])

If your tax year ends in March, for example, the DAX is:

MyAutoCalendar = CALENDARAUTO(3)

This creates a table with a single column called date, with the first date being the 1st April of the earliest year in your data, and the last date being the 31st March of the last year in your data.

Just two things to remember:

• In Options, remove the Auto date/time selection
• In Table Tools, select Mark as data table. Then select the date column. A warning is given to tell you that the hidden date table will be removed. Click OK.

Now that you have a table with dates in it, you can add columns to suit your reporting purposes.

To create a new column for month name:

MonthName = FORMAT(Sale[OrderDate], “MMMM”)

To create a new column for the month number:

MonthNo = MONTH(Sale[OrderDate])

To create a new column for year number:

OrderYear = YEAR(Sale[OrderDate])

To create a new column that combines the year and month values:

YearMonth = Sale[OrderYear] & "-" & Sale[OrderMonth]

Your calendar table is now beginning to take shape, and you can now create slicers containing months from specific years.  This is progress - but there's a lot more to working with dates in Power BI.

In the meantime, leave a comment to let me know how it's going. And if you want some help with your Power BI reporting or data modeling, then get in touch.