In the previous video, we looked at several basic time-intelligence functions in DAX. Today, we’ll continue to focus on the time-intelligence functions and learn how to calculate the difference between current and last year sales.
Time-intelligence functions are group of DAX functions that give you insights from different time dimensions. With the help of these functions, you can make calculations over days, weeks, months, quarters and years.
In our example, we used the following calculations:
Created the first measure Total Sales to calculate the sum of all sales in the Fact table.
- Total Sales = SUM(Sales[SalesAmount])
After that, let’s calculate last year sales.
- Sales LY = CALCULATE( [Total Sales], SAMEPERIODLASTYEAR([Date]))
Once we have the two measures, we can put them in a table side-by-side. To find out the difference, simply subtract one from another. It’s a best practice to remove empty and repeating rows in the first year, using the IF and ISBLANK functions
- Difference = IF ( ISBLANK ([Total Sales PY]), BLANK(), [Total Sales] – [Total Sales PY])
ISBLANK checks whether a value is blank, and returns TRUE or FALSE. Wrap this function inside the IF logic.
Finally, we can calculate a percent difference between the current year and last year sales.
- % diff = IF( ISBLANK([Total Sales PY]), BLANK(), DIVIDE( ([Total Sales] – [Total Sales PY]), [Total Sales]))
Calendar table for time-intelligence functions
It is important to remember that before you can use any DAX time-intelligence functions, you must have a proper date dimension in your data model (e.g. a calendar table).
The easiest way to quickly create a calendar is to use the function CalendarAuto. It will automatically take the first and last dates from your data and produce a calendar. However, using the CalendarAuto function is a bad practice, as in some cases it may lead to mistakes in your data model.
That is why it is better to use the Calendar function:
TableName = Calendar ([Start Date], [End Date]).
Watch one of our previous videos to learn how to build a calendar table using DAX.