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

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.

Total Sales YTD Custom VAR Syntax =
   VAR UnfilteredCalendar =
       ALL('Calendar') //returns an unfiltered copy of the calendar table
   VAR LastDateInFilter =
       MAX('Calendar'[Date]) //max returns the last date in the filtered table
   VAR FilteredYear =
       MAX('Calendar'[Year]) // max returns the only year that is filtered
   RETURN
       CALCULATE(
           [Total Sales],
           FILTER(
               UnfilteredCalendar,
               'Calendar'[Date] <= LastDateInFilter
                   && 'Calendar'[Year] = FilteredYear
            )
       )

Watch the Video

Here is the full recording from the session.