Golden Rules of working with Time Intelligence functions in Power BI

Golden Rules

One of the most common calculations performed in data analysis is to compare some number to a comparable number for a different time period. Calculations that make comparisons to last month or to the same period from a year ago are very important for any business intelligence tool. DAX includes more than 35 functions expressly for the purpose of working with time based data. These functions make working with time easier and are called Time Intelligence functions. Working with these functions can de tedious and quite difficult for a novice DAX user.

Always create a separate date table. 

Defining a separate date table is the common practice in the Kimball star schema data models and even suggested for other data models as well. The date table in the data model should consist of at least single date column that covers the list of first and last dates you want to report on and any columns that you want to use in your report. For example, Year, Month, DayName, WeekNo etc. You can use the built in DAX functions/ M code to create the date table in Power BI.

Make sure your date table must include a continuous date range. 

DAX uses the date column to navigate through time in Power BI. Say that you have selected January 2017 in a Matrix Visual, and you want to show the sales for the last year. DAX will automatically determine the date range from Dec 2017 using your date table, then it will use that date range to determine the date range for Dec 2016. Now if the date table is missing some dates in Dec 2017, that same day will be missing for the previous year that might have some sales that are missing for the calculations.

Create Relationships between fact tables and the date table. 

This is required to slice the values of the fact tables with the filters coming from Date table. If you have multiple date columns in your fact table, you can create multiple relationships to the date table with only one of them being the active at a time by default in the model. Alternatively, you can also choose to create a different date table for each date column of the fact tables. For Example, Order Date Table, Delivery Date Table etc.

The date columns in the date table should be at the day granularity. 

DAX Time intelligence calculations does not support dates smaller than the day granularity. It is often a good idea to remove the time parts of dates unless necessary for performance and compression reasons. Adding a DateTime data types in the Date Column can create high number of distinct values and we all know that Power BI runs on the in memory engine so it is always advisable to save the precious RAM as and when necessary to get the best possible performance. It would be better to split it into two columns to get the smaller number of distinct values.

Always name the date table with something other than Date. 

Date is a reserved keyword in DAX and thus it would be preferable to use Dates/Calender/DateTable/DimDate for the naming conventions of the table.

Posted in: DAX