Power BI Desktop Date Table/Dimension

A date table in PowerPivot will be part of the vast majority of models. Date tables allow slice and dice of data by weeks, months, quarters, years and so on. When working with PowerPivot many people turn to the Azure Data Marketplace and use the excellent (and free) DateStream. Alternatively you can add a table to an Excel worksheet and manually create your own date data.

Power BI Desktop and Excel 2016 offer another alternative by allowing us to add a new table into the model.

The DAX CALENDARAUTO() function will return a date table with a single column names “Date” that contains a contiguous set of dates calculated automatically based on data already in the model.

Alternatively the DAX CALENDAR function takes a start date and end date as parameters if you want more control.

Now we have a date column we can start to build out our date dimension by adding calculated columns such as:

Year

YEAR([Date])

MonthNumber

MONTH([Date])

Month

FORMAT([Date], “MMMM”)

Short Month

FORMAT([Date], “MMM”)

Short Day

FORMAT([Date], “ddd”)

Quarter

“Q” & ROUNDUP(MONTH([Date])/3,0)

Week

WEEKNUM([Date])

 

Here is the final result:

Now we’ve created a calculated table it will behave like any other table allowing you to create relationships with other tables. I joined the ‘Date’ column of my new table to the ‘Date’ column of my Sales table allowing me to look at sales data by week, month, quarter and year.

About the author