Time Intelligence Using DAX - Excelerator BI

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.

Power BI DAX Book

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” https://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 https://exceleratorbi.com.au/product-category/books/all-ebooks/
My Online Power Query Training https://xbi.com.au/pq
My Online DAX (Supercharge Power BI) Training https://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.

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
            )
       )

Power BI Online Training

Watch the Video

Here is the full recording from the session.

13 thoughts on “Time Intelligence Using DAX”

  1. Hi,
    Thanks for great explanation of the Sales YTD and the filtering question! However I get a bit confused when referring to a similar issue in your other post here: https://exceleratorbi.com.au/filtering-column-via-visual-calculate/, where we see that the below formula results in a table where the visual filter is not respected (ie showing the same amount regardless of row in the visual).

    Total Northern Hemisphere Sales in 2003 = CALCULATE([Total Sales],
    ‘Calendar'[Year]=2003,
    Territory[Country]”Australia”),

    To my understanding the same formula could behind the scenes written with FILTER(ALL…) and according to info in this blog post above would hence consider the filter in the visual since the filtering takes place within FILTER and not CALCULATE?

    Total Northern Hemisphere Sales in 2003 = CALCULATE([Total Sales],
    FILTER (ALL (‘Calendar), ‘Calendar'[Year]=2003,
    Territory[Country]”Australia”))

    Hope to hear from you,
    Helena

    1. I don’t even remember writing the article you refer to above, but I guess I did :-). I am not sure I completely understand what you are asking, but let me clarify a few things and see if that helps, otherwise you can ask a follow up question.
      * CALCULATE can have filter parameters
      * filter parameters can be of two types, “simple” or “table”
      * a simple filter parameter is syntax sugar for a table parameter,
      so Calendar[Year]=2003 is identical to FILTER(ALL(Calendar[Year]),Calendar[Year]=2003)
      * the filter function does not change filter context. the filter function can be considered to be creating a virtual table with the filters applied AND with lineage to the rest of the model.
      * it is CALCULATE that changes the filter context, not the FILTER function. But CALCULATE uses the prescribed filters as determined by the FILTER function to set the new filter context.
      * you can therefore consider that CALCULATE “outsources” the responsibility of working out “what” the new filter context will look like, and then CALCULATE takes that work done by FILTER and “applies” that filter.

      I hope that helps

      1. Hi,
        Thanks for quick reply.
        What confuses me is information that seem contradicting to me:
        In your post about FILTER function it says: (https://exceleratorbi.com.au/the-filter-function-in-dax-part-2/)
        -The ALL() function will contain all the unique values in the column after removing all the filters affecting the visual.
        -The VALUES() function contains all the unique values in the column but RETAINS the filters affecting the visual.

        While I in another post read: (https://exceleratorbi.com.au/time-intelligence-using-dax/)
        -ALL function does not affect the filter context in the visual.  Only CALCULATE (and CALCUALTETABLE) can alter the filter context of a visual.

        Is there a difference between removing filters versus affecting filter context that I am not aware of?

        1. In short, yes. It is quite involved. if you have CALCULATE([measure],FILTER(table,expression)) the “table” is impacted by the initial filter coming from the visual. If you want the table to ignore the filter from the visual, you have to write CALCULATE([measure],FILTER(ALL(table),expression)). In this second formula, FILTER works through and unfiltered copy of “table”. But ALL does not alter filter context, it just ignores it. The only functions that can change the filter context sent to the measure expression is CALCULATE. That is why FILTER and CALCULATE work together so often. The FILTER function works out what the new filter should be and then CALCULATE applies the new filter (changes filter context) prior to the measure being evaluated.

          1. Hello Matt, thank you for the explanation. So, if i understand correctly, the ‘Calendar'[Date] and ‘Calendar'[Year] are columns in the unfiltered virtual table created by FILTER(ALL)? and these 2 date columns in the unfiltered table are compared to the last dates as they appear in the visual? So, effectively, if there were no FILTER(ALL) but just FILTER, ‘Calendar'[Date] and ‘Calendar'[Year] in this FILTER table would equal the MAX date in the visual?

            CALCULATE(
            [Total Sales],
            FILTER(
            UnfilteredCalendar,
            ‘Calendar'[Date] <= LastDateInFilter
            && 'Calendar'[Year] = FilteredYear

            1. Yes, the ‘Calendar'[Date] and ‘Calendar'[Year] are columns in the unfiltered virtual table created by FILTER(ALL)

              These 2 date columns in the unfiltered table are filtered by the last dates as they appear in the visual

              If there were no FILTER(ALL) but just FILTER, ‘Calendar'[Date] and ‘Calendar'[Year] in this FILTER table would already be filtered by the visual. Eg if the visual said “Mar”, then there would already be a filter on the calendar for March. You would then be trying to add an additional filter as above (ie, <=end of march) but given there is already a filter on March, you would just end up with the March dates still. There would be no way to be able to access sales for Jan and Feb in order to create the YTD calculation because these months were already filtered by the visual. The only way to reach back in time and grab Jan and Feb is to work on an unfiltered copy of the calendar, not a filtered copy.

  2. 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!

    1. 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.

  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,

    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

  5. 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?

Leave a Comment

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

Scroll to Top