Time Intelligence Using DAX

Level: Intermediate

Last week I presented a webinar with Chuck Sterling (from Microsoft) on the topic of Time Intelligence.  This was a YouTube broadcast event that anyone could join live, however it has also been recorded and can be watched at any time (see below).

This article is a follow up from that webinar with the following purposes:

  • Make people that missed the webinar aware so they can watch it if they want.
  • Make it available to those who want to watch it again – custom time intelligence is a tricky topic that can be hard to learn.
  • Provide the slide deck from the webinar.
  • Provide the links I referred to in the webinar.
  • Answer a follow up question I had.

I have another article about time intelligence here https://exceleratorbi.com.au/dax-time-intelligence-beginners/

Session Summary

There are 3 different types of Time Intelligence in Power BI and I covered them all in this video.

  1. Auto Time Intelligence
  2. Inbuilt Time Intelligence
  3. Custom Time Intelligence

I explained what each of these are and showed specific examples to understand these concepts in the video.  Here is a quick summary of the 3 types.

Types of Time Intelligence

Auto Time Intelligence

Auto Time Intelligence is the Microsoft’s way of letting anyone get started with Power BI and to bring a date field into the report and then be able to get calculations done by year, quarter, and month into the report. This does not require much knowledge of Power BI.

Drawbacks of this approach:

  • Every date field brings in a hidden calendar table into the data model (as I demonstrated in the video).
  • This makes the file size bigger.
  • There is no way of doing week-by-week calculations.
  • There is no easy way to have a calendar lookup table to connect to multiple data tables in the data model. There are of course some tips and tricks to do this as I explained in the video.

Inbuilt Time Intelligence

Inbuilt Time Intelligence comes with a standard set of DAX functions that enable different time-based calculations easy to create and that cover most of the common scenarios. But you can only use Inbuilt functions if you follow the standard rules.

Rules for Inbuilt Time Intelligence:

  • The Data Model must have a Calendar table with contiguous dates.
  • The Calendar must be a standard calendar – you cannot have a 445 calendar or an ISO calendar.

Drawbacks:

  • This is not great if you have month-level or week-level data. But, you can hack it as I discuss in the video.

Custom Time Intelligence

Custom Time Intelligence enables you to have any granularity and any span you need (milliseconds to decades). Weekly granularity is often used in the retail calculations.  You can therefore have a calendar table with weekly granularity and write custom time intelligence patterns to complete the required calculations.

Custom Time Intelligence is the most flexible approach but it is advanced and the hardest of the three to learn. You should have a clear understanding of the complex DAX functions CALCULATE() and FILTER() if you want to use Custom Time Intelligence.

I have demonstrated the custom time intelligence patterns in the video.

Follow Up Question

I had a really good follow up question from masterelaichi at PowerPivotForum.com.au and I wanted to answer that question here.  In short, the question is this.

Question

It’s not clear how the filtering works in the evaluation of the Sales YTD code.

Given the ALL(Calendar) portion of FILTER removes the filter, why does the MAX(Calendar[Date]) return the 30th June 2002 and not the last Date in an unfiltered copy of the Calendar Table?

Answer

This is a really insightful question and hence I wanted to answer it here in case others are interested.  The answer relates to how and when each portion of the formula is evaluated.  I want to talk about the various portions of the formula by referring to their line numbers.

Firstly line 3.  CALCULATE always executes the filter portion of the formula first.  The filter portion in this case is the FILTER function (lines 4 – 8).  Line 3 is executed LAST and hence has no role to play in answering this question.

The filter parameter of CALCULATE (lines 4-8) happen to be a FILTER function in this example.  The filter parameters of CALCULATE are always executed in the original filter context.  In this case is the filter context coming from the visual.

Line 5 tells the FILTER function which table to iterate over – in this case it iterates over an unfiltered copy of the Calendar table.  But this ALL function does not affect the filter context in the visual.  Only CALCULATE (and CALCUALTETABLE) can alter the filter context of a visual.  At this point in time, the CALCULATE starting on line 2 has not finished executing – it is still WIP.  As at line 4, control has just been handed from CALCULATE to FILTER to work out which rows to keep in the Calendar table.  The CALCULATE on line 2 does not modify the filter context until after FILTER has finished its job, but FILTER has only just started.  As a result, this CALCULATE on line 2 does not change the filter context of the visual and neither does FILTER (because it can’t).  So nothing has changed the filter context at this point in time.

Lines 6/7 inside the FILTER function are therefore respecting the filter context from the visual as originally presented (because nothing has changed it) and hence MAX(Date) will return the last date in the initial filter context created by the visual.

If there are any follow up questions, please add them to the comments and I will respond.

Slide Deck

Here is the slide deck from the session – Time Intelligence Using DAX.

Referred Links

Topic Link
“Local Host Workbook” http://xbi.com.au/localhost
My Blog Post “Power Pivot Calendar Tables” https://exceleratorbi.com.au/power-pivot-calendar-tables/
Marco’s “DAX Date Template” https://github.com/sql-bi/DaxDateTemplate/blob/master/DAX Date Template.pbit
My Blog Post “Build a Reusable Calendar Table with Power Query” (my preference) https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/
DAX Language Quick Reference Guide http://xbi.com.au/ebooks
My Online Power Query Training http://xbi.com.au/pq
My Online DAX (Supercharge Power BI) Training http://xbi.com.au/scpbiot
Marco's Power BI Visual Reference Power BI Visuals Reference

Custom Time Intelligence VAR Syntax

Following is a DAX Custom Time Intelligence Pattern that I explained in the video. You can copy-paste this into your Power BI workbook to see how it works.

Watch the Video

Here is the full recording from the session.

Share?

Comments

  1. Matt, I’m sorry I missed this webinar. Would it be possible that next time one of these events is planned that we receive an email invitation as well?

  2. Hi Matt,

    just wanted to say thanks for this informative presentation on time intelligence.

    Over time you and the “Italian Brothers – Marco and Alberto” have been my go to sources for learning Power BI, Dax, Power Pivot.

    Cheers,

    Kerry

  3. Sorry Steve, I didn’t think of that. I did promote it on twitter and LinkedIn, but didn’t think of adding it to my email broadcast. I will try to remember for next time. If you have any questions about the video, I would be happy to answer them here.

  4. Hi Matt, thank you for the video on this complicated topic! In the Rolling 90 day Average Example you switched to a different table with a Day ID Column. What is the syntax for the Day ID formula? Thanks!

    • Lora, I don’t believe I swapped tables – it was always there. Technically you don’t need an ID column for DAY in a day level calendar. You can use the custom time intelligence pattern on a date column using simple subtraction (kind of like you can in Excel). Calendar[Date] >= MAX(Calendar[Date]) -89

      You can add an ID column in Power Query by simply sorting by date first and then inserting an ID column.

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x