Search

Search this blog:

Search This Blog

DAX Time Intelligence - Easy pattern to get DAX Time Intelligence functions to return the correct results


 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:

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: 

https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/td-p/434008

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
Enjoy!

Custom Visual Review: Charticulator

This is not your ordinary custom visual - this is EVERY custom visual. Charticulator puts the power to design and develop custom visuals to ...