The date dimension is the table that controls your time analysis and the most important to get right, especially with PowerBI and other visualization tools. Having various tags and descriptors that aid in your analysis gives your end users several options in creating their visualizations in addition to a base for DAX time intelligence calculations. The following article uses Excel as the starting point.
Note: A future article will review a TSQL based date dimension and includes the topic of dealing with a fiscal year starting on a date other than January 1.
One (Date) Key to Rule Them All
The first significant step is how to link the date table to other tables in your model. Using an integer surrogate key based on the date is the best way to do this. Using this format for the join key will avoid errors that occur because of different date formats. Some columns have a date-time value so they may look like the same date, however, will not join together because the time is different.
The Excel calculation below uses the DateValue column and converts the date into an integer value. If you have a different date granularity, such as time to seconds, you can change the calculation to include those values. Even if you have a date table only down to the month level, creating a date column with each month’s first date will allow for easier DAX calculations.
CalendarID = (YEAR([DateValue]) *10000) + (MONTH([DateValue]) *100) + (DAY([DateValue])) DateValue= Calendar Date
Get the Data Types Right!!
When using Excel most columns classify themselves as “General.” As you add in the formulas, change the column formats to the correct data types, text for text, a date for date and number for number columns. These will get picked up when you import the table into PowerBI.
Prepare the Slicers
In PowerBI, as with other tools, you will want totals or at least the ability of filter based on different parts of the date. By having a slicer for Year, Month and Week Number, you can add flexibility to your visualizations and filters. You will also need numeric values to provide sort values as many visualization tools will sort them alphabetically.
You will also need numeric values for some columns that are text formatted in order to provide sort values as they will sort them alphabetically. MonthNum and WeekDayNum are examples pictured above. PowerBI allows you to sort one column based on the value of another.
The Excel example is available on Github. This gets you the basic time dimension that will allow you to enhance your data model in PowerBI as you can add in various time intelligence calculations using DAX.
NOTE: If you want to use Time Intelligence in Power BI models, you have to join the tables using data values.