DAX is a simple but complex language. It can be very powerful if you understand how to use it properly. In this post, we're going to look at a specific group of functions in DAX: Time Intelligence.
Time Intelligence DAX functions
Time Intelligence functions in DAX change the filter context on the Date table of your model.
Step 0: Prepare
Before using Time Intelligence functions, you should:
- Check out the documentation at https://dax.guide/functions/time-intelligence/
- Make sure your model has a Date table that's marked as such: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html
- Have a basic understanding of the CALCULATE function - it's crucial to this solution
Time Intelligence functions in DAX are not vital functions. We can calculate the same results without their use, just need to think a bit differently about the problem. Greg Deckler has a great post on this here:
My post here aims to debunk the mysteries behind using Time Intelligence functions and provide you with an easy pattern that works every time.
Easy Time Intelligence DAX Pattern
Now that we know what time intelligence is, how do we use it?
Step 1: Create a base measure
You may have already completed this step. The base measure is what you are reporting on, it should come from a table related to your Date table. Consider the 'Example 2' in my post on Relationships in Power BI: https://excelwithallison.blogspot.com/2020/08/its-complicated-relationships-in-power.html
We have a 'Date' dimension table and an 'Incident Reports' fact table. These are the only two tables we need to consider for this solution.
Our base measure will be Incident Count:
Incident Count = DISTINCTCOUNT( 'Incident Reports'[IncidentID] )
Your base measure could be anything, and use any aggregate function or filters, or combination of DAX functions, as long as it returns the results you expect when put in a table/matrix with a column from your Date table.
Step 2: Select your time intelligence function
This may be the most difficult step, and I may write a separate blog on this if I get time. For this post, I'm going to use my favorite time intelligence function: DATEADD. I like this function because it's versatile and works for a lot of the scenarios my clients ask for, without requiring me to learn /memorize new functions.
For this example, I want to compare my [Incident Count] base measure above to the same period last year. DATEADD will work nicely for this.
Step 3: Apply the Time Intelligence Pattern
Now we're ready to apply the easy Time Intelligence Pattern.
Time Intelligence Pattern Measure = CALCULATE ( [Base Measure], FUNCTION ( 'dimDate'[Date], functionparameters ) )
What does this look like in practice? Let's apply it to our example using DATEADD and [Incident Count]:
Incident Count Prior Year = CALCULATE ( [Incident Count], DATEADD( 'Date'[Date], -1, YEAR) )
Step 4: Use in the context of your Date table
This final step is critical, and one that can cause many well-written time intelligence functions to fail if forgotten. You MUST use this measure in the context of your Date dimension table.
In my example, I've created an [Incident Count Prior Year] measure. If I don't put a filter for year, or use this in a table / visual split by Year, I'll just get the result of all Incidents except the maximum year in my date table (this is by design and how time intelligence functions work, something that I may expand on in another blog post).
For many people, their date tables include future years (for budgeting and forecasting purposes), so when they create a time intelligence function correctly, it simply returns the same value as the base measure. That is, until they apply a date filter.
Use the Date dimension in any visuals or filters for this measure.
Conclusion
That's it! You've now got the easy time intelligence pattern for DAX. It can be applied to all sorts of different scenarios.
Additional Examples
Total Sales = SUM( Sales[Sales Amount] )
Total Sales Prior Year = CALCULATE( [Total Sales], DATEADD( dimDate[Date] , -1 , YEAR) )
Total Sales YTD (calendar) = CALCULATE( [Total Sales], DATESYTD( dimDate[Date] )
Total Sales YTD (fiscal) = CALCULATE( [Total Sales], DATESYTD( dimDate[Date], '6-30' )
Remember
Key tips:
- Create a base measure first - it makes things easier to follow
- Use the CALCULATE function with the Time Intelligence function as a filter
- Use the Date dimension table in:
- Your time intelligence function wherever it asks for a date
- Your visuals and filters whenever using the new measure
