DAX Time Intelligence Explained - Excelerator BI

DAX Time Intelligence Explained

Level: Beginners

I help a lot of people on forums who ask questions about time intelligence for DAX.  If you are just starting out then the chances are that you may not even be clear what time intelligence is and hence sometimes you don’t even know what to ask.  Often the question is something like “I want to show this year and last year on a chart”, or “total year to date this year compared with last year” etc. If you want to do any time based comparison using DAX, Power Pivot and or Power BI, then this article explaining time intelligence is the right article for you.

Definition of Time Intelligence

Time intelligence is the collective name for a set of patterns (DAX in this case) that can be used to solve time comparison problems.  Examples include comparing:

  • Same period prior month, quarter, year etc.
  • Same period next month, quarter, year etc.
  • Same period year to date compared with prior year, next year etc.
  • Rolling 30 days, 60 days, 90 days, 12 months etc.
  • etc. – there are many many more

Time intelligence is used when you want to “time shift” any period with another period of time for comparison purposes or to simply display a different period than the selection.

Power BI DAX Book

 

Understanding Filter Context

Before you can understand why time intelligence needs a special approach in DAX, you first need to have a clear understanding of Filter Context.

Pivot Tables and Power BI visuals both slice data so that you can “drill” and “filter” to see a sub-set of data.  Take the image shown below.  On the left is a pivot table and on the right is a Power BI visual.

image

Starting with the Excel Pivot table on the left, every value cell in the pivot table has been filtered more or less by the Rows, Columns, Filters and Slicers that make up the Pivot Table.  The cell highlighted as 1 (above left) has the following filters applied.

  • Territory[Country] = “Australia” – this comes from the Rows in the Pivot
  • ‘Calendar'[Year] = “2003” – this comes from the Year in the slicer
  • Product[Category] = “Bikes” – this comes from the Category in the filter.

After these filters are applied, the calculation for the cell is evaluated and the answer $2,947,789 is returned to the cell.  Every value cell in the pivot table is evaluated in exactly the same way – including the Grand Total row in the pivot table.  In the case of the grand total row, Product[Category] and ‘Calendar'[Year] have the same filters, but there is no filter on Territory[Country].

On the right hand side in the image above is a Power BI visual.  Filtering in Power BI visuals fundamentally works the same way as a pivot table however there are more places for cross filtering to come from.  In the image above, the same filtering is applied as in the Pivot table but in the Power BI example the filters are applied in a different way.

  • Territory[Country] = “Australia” – this comes from the bar chart Axis
  • ‘Calendar'[Year] = “2003” – this comes from the Year in the slicer
  • Product[Category] = “Bikes” – this comes from the Category in the tree map visual.

When filter context is passed from a visual to the underlying data model, all the relevant tables are filtered before the calculation is completed.  Filter first, evaluate second is a fundamental principle for all DAX formulas.

The Time Intelligence “Problem”

Let’s assume you want to compare total sales on a particular year vs prior year.  One way to do this (in Excel) is to put the years onto Columns in a pivot table as shown below (a similar approach can be used in Power BI).

image

But doing it this way causes many problems, including:

  • There are years in the pivot table that you don’t want (eg 2001, 2004).  You will need to somehow manually hide or filter the ones you don’t need.
  • If you want to calculate the absolute change or % change year on year you will need to hard code some formulas in the cells next to the spreadsheet and hence they can’t be reused in other visuals later.
  • If you want to look at a different year you will potentially have to go through the process of doing the filtering and formulas again from scratch.

A better way to solve this problem is to select the current period (using a slicer or filter of some sort) and have a universal formula that returns the result relative to the selection.  So if you select 2003, the formula will automatically select 2002 for you.  If you select 2002, it will automatically select 2001 (and so on).

Filtering is Now Your Enemy

If you want to write a relative time intelligence formula, then the natural filtering behaviour of a visual becomes your enemy. Imagine a calendar table with a filter applied Calendar[Year] = 2003.  If you imagine what the filtered data model would look like “Under the hood”, it should be clear that the filtered calendar table will show rows starting on 1 Jan 2003 and ending on 31 Dec 2003 – it will contain 365 unique days for the single year 2003.  The filter has already been applied to the data model so only days in 2003 are available for the calculation – how then can the data model possibly access sales for the year 2002?  There needs to be a way to go back in time and fetch a different period (in this case the period is 1 year earlier in time than the selected period), yet the 2003 filter has already been applied preventing this from occurring naturally.  This is the reason why there needs to be a special set of time intelligence functions – to solve this natural filtering “problem”.

How Time Intelligence Functions Work

Time intelligence functions are specifically designed to solve the filtering issue described above.  All time intelligence functions execute the following 4 steps:

  1. first “detect” the current filter context to determine what the “current” selected period is
  2. then remove the calendar filtering from the underlying tables so that data for “all time” is once again available.
  3. then perform a time shift to find a different period in time (as specified in the formula).
  4. Finally reapply filters on the data model for the time shifted period prior to calculating the final value.

Power BI Online Training

Custom vs. Inbuilt Time Intelligence

There are 2 types of time intelligence functions – Custom and Inbuilt.  Inbuilt time intelligence functions are easier to write because they have been designed to protect the user (ie you) from the complexity.  I am not going to cover Inbuilt time intelligence in depth here because they are relatively easy to use.  See link at the bottom of the page if you would like to download the DAX reference guide I produced (including all the time intelligence functions).

The reason inbuilt time intelligence functions are easier to learn is they actually are what is known as “Syntax Sugar”.  Microsoft has created these special functions to make them easier to write and easier to understand.  You follow the simple syntax and Power Pivot does the rest.  But under the hood the inbuilt time intelligence functions are converted to the full syntax prior to evaluation.  Consider the following two examples (just to illustrate the potential complexity in the custom version).

Total Sales Year to Date (inbuilt) = TOTALYTD([Total Sales], 'Calendar'[Date])
Total Sales Year to Date (custom) =
    CALCULATE(
          [Total Sales],
          FILTER(
             ALL('Calendar'),
            'Calendar'[Year] = MAX('Calendar'[Year])
             && 'Calendar'[Date] <= MAX('Calendar'[Date])
          )
     )

Both of these formulas return the exact same result, and in fact both use the same approach to calculating the result under the hood.  The only difference is the inbuilt version is much easy to write because you (the DAX author) are protected from the full syntax.

The end result (regardless which formula is used) looks like this in a Pivot Table.

image

Looking at the highlighted cells, even though cell 1 above is filtered to show only sales for the month of July 2003, the Time Intelligence function (cell 2 above) is able to display sales for the period Jan – Jul 2003.  It does this because the formula performs the following 4 steps.

  1. It first “detects” the current filter context to determine what the “current” selected period is.  In this case July 2003.
  2. It then removes the Calendar table filtering from the underlying tables so that all data is once again available.
  3. It then performs a time shift to find a different period in time – in this case it holds the end date in the current filter context the same (31 July 2003) but shifts the start date back to 1 Jan 2003.
  4. Finally it reapplies filters on the data model for the time shifted period prior to calculating the final value.

How to Read a Custom Time Intelligence Formula

The custom time intelligence formulas can be daunting when you first see them – but actually they are easy to understand as long as you clearly understand the role of each part of the formula.  Below is the same formula again (from above) to make it easier for me to refer to.

Total Sales Year to Date (custom) =
    CALCULATE(
          [Total Sales],
          FILTER(
             ALL('Calendar'),
            'Calendar'[Year] = MAX('Calendar'[Year])
             && 'Calendar'[Date] <= MAX('Calendar'[Date])
          )
     )

Line 2 (CALCULATE) is the only function that can change the natural filtering behaviour coming from a visual – that’s what it does.   CALCULATE always filters first, evaluates second (as mentioned above).  So lines 4 – 8 are executed first (filters applied) and then the formula on line 3 is evaluated last.

Lines 4 – 8 (FILTER) is where the filtering occurs.  There are a few confusing things here.

  • Line 5 refers to ALL(‘Calendar’) instead of simply ‘Calendar’.
  • Lines 6 and 7 seem to be evaluating against themselves – very confusing.
  • Line 7 starts with a double ampersand &&

Let me explain each line.

Line 5 ALL(‘Calendar’)

The key thing to understand here is that the filter portion of calculate always operates in the current filter context.  That means that the Calendar table in line 5 has already been filtered by the visual (eg the Pivot Table).  Looking back at the pivot table image above, this means that the line 5 is already filtered by the pivot table and hence the Calendar only has dates from 1 July 2003 until 31 July 2003 available – all other dates have been filtered away.  The purpose of the ALL Function therefore is to remove all filters from the current filter context.  If it didn’t remove these filters, it would not be possible to access dates outside of the month of July 2003.

Now they have all been removed, it is time to add back the date filters that are needed.

Line 6 MAX( )

Line 6 reads “where ‘Calendar'[Year] = MAX(‘Calendar'[Year])” and hence it seems to be referring to itself. The way to read and understand line 6 (and 7) is as follows:

  • Whenever you see an aggregation function (in this case MAX) around a column, it means “go and read the value from the current filter context”.
  • Whenever you see a “naked” reference to a column (ie there is no aggregation function around the column), then it means “go and apply a new filter on this column in the data model.

Taking these 2 rules, it should be clear that MAX(‘Calendar'[Year]) in the current filter context is = 2003.  Line 6 therefore says “Go and apply a new filter on ‘Calendar'[Year] to be equal to 2003.

Note that you can use any aggregation function in these formulas that does the job.  In lines 6 and 7, you could use SUM, MIN, MAX, AVERAGE or any other similar aggregation function and still get the same result.  You could also use VALUES or DISTINCT in the case of line 6, and LASTDATE in the case of line 7.  There is no right or wrong answer, simply think about the need and then find the best function to use.

Line 7 && and MAX( )

Line 7 reads “and ‘Calendar'[Date] <= MAX(‘Calendar'[Date])”.  The double ampersand && is the inline syntax for a logical AND.  Using this knowledge and the same logic as with line 6, the way to read line 7 is as follows:

“AND also go and apply another new filter this time on ‘Calendar'[Date] to be less than or equal to 31 July 2003”.

With both of these filters applied in a logical AND, the Calendar table will contain all of the dates from 1 Jan 2003 until 31 July 2003.

The Result

Once the time intelligence function has been written, it can be added to a visual as shown below (Power BI Desktop).  Note how the YTD formula seems to “defy” the filter context coming from the visualisation due to the custom time intelligence function that has been written and explained.

image

The Trouble with Syntax Sugar

Syntax sugar is great as it makes otherwise hard formulas very easy to write with a limited understanding of filter context, row context, filter propagation, context transition etc.  There are a few down sides however.  Firstly is that you don’t get to learn these very important concepts and hence you are delayed in becoming a true Power Pivot and Power BI Ninja.  Secondly the inbuilt time intelligence functions only work in certain circumstances where you have a day level Gregorian calendar.  Read more about that here https://exceleratorbi.com.au/power-bi-calendar-tables/

Granularity

I personally hardly ever use the inbuilt time intelligence functions unless my data is at a day level of granularity (which is rare), and I find the custom functions become easy to write with practice.  Custom time intelligence functions become much more important when your data is not at a day level of granularity.  Most of the work I do for clients is either weekly or monthly data.  If you are in this situation you could “trick” Power Pivot that you are using daily data by loading all data in any given week or month on the same date (eg last day of the month) and use inbuilt time intelligence however this is hardly best practice.  A much better approach I think is to write custom time intelligence functions as outlined in this article.  If you are going down the  path of writing custom time intelligence, you should read my advice about adding an ID column into a Calendar table to make custom time intelligence functions easier to write. https://exceleratorbi.com.au/power-bi-calendar-tables/

Sales vs Prior Year

Time for a different example.  Now that I have covered how a custom time intelligence function works, let me show you a couple of inbuilt time intelligence measures that solve the original problem (Sales vs Prior Year).

Sales Prior Year 1 = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))
Sales Prior Year 2 = CALCULATE([Total Sales], DATESADD('Calendar'[Date],-1,YEAR))

Both of the above formulas use inbuilt time intelligence functions (shown in bold), but they also use CALCULATE.  Now you have an understanding that CALCULATE is performing a time shift, it should be much easier to understand what is happening in these formulas.  Both of these formulas in bold produce a table of dates that has been time shifted back by 1 year.  CALCULATE then takes this new table of dates, removes the current filter context from the Calendar table and then moves back in time by 1 year before reapplying the filter context and then doing the calculation.  One you have [Sales Prior Year] it is easy to write:

Change vs Prior Year = [Total Sales] - [Sales Prior Year]
%Change vs Prior Year = DIVIDE([Change vs Prior Year], [Sales Prior Year])

So where can you find a list of all the inbuilt time intelligence functions?

 A Free DAX Reference Guide

One of my students at a recent training class asked me if there was a list of all DAX Functions – kind of like a cheat sheet.  I wasn’t able to find such a thing so I produced exactly that and I am making it available free to anyone that would like a copy here.

This reference guide covers all of the inbuilt time intelligence functions on page 14 as well as every other function across the language all nicely laid out to make them easy to find.  You can download this reference guide below.  If you haven’t ready done so, why not sign up for my weekly newsletters at the same time so you are kept up to date with my latest tips and tricks about Power Pivot, Power Query and Power BI.

Download the DAX Reference Guide

https://mattallington.com.au/shop/

34 thoughts on “DAX Time Intelligence Explained”

  1. Dear Matt, I deal with the following issue: I have created in my calendar table a column called flag_YTD and I use it as a slicer in my report. So when I select it, it gives me the YTD comparisons. But for leap years it also counts the date 29th of February, so when it comes to comparisons, it compares YTD sales up to including 12th of March for 2024 (today) with YTD sales up to including 13th of March for 2023. How should I reconstruct my flag_YTD column to take this into account and give me directly comparable results?

    1. There is no right and wrong, here. The calculation you describe accurately compares the same number of days each year. If you make a change, by the end of Feb there will be 1.5% difference in the number of days in comparison. One way I have seen this “solved” is to add a column of numbers from 1 to 365. When you get to 29th Feb, mark that as day 59, the same as 28th Feb. You can then use VALUES(Calendar[Numeric Column I mentioned]) to “catch” the days that are being used and then use that in Calculate to shift the year.

      There are plenty of articles that cove the different approaches, like this one.
      https://eriksvensen.wordpress.com/2020/02/02/dax-time-intelligence-and-the-29th-of-february-powerbi/

  2. Hi Matt,
    Thank you for your clear explanation. I am new to power Bi and DAX. I am trying to find the percentage change in the last compared to the first year i.e. [(YL-YF) -1] * 100. I have a single table data model where one column is of a date data type. To do that I write the this DAX formula CO2 2010 = CALCULATE( [Totaal CO2 M.ton per Year]; FILTER(‘Total CO2’;’Total CO2′[Year] = 2010)). But, I am not able to do that. Do you have a tip for me, please? Thank you for your time.
    Regards,
    Mogos

  3. Well it is not my suggested approach, but it is what it is. Maybe something like this
    =CALCULATE([Total Sales],All(Calendar[Date]),TREATAS(VALUES(Calendar[Date LY]),Sales[Date]))

    This is a guess. Please let me know either way. If it doesn’t work I will need a sample workbook to take a more detailed look.

  4. Hi,

    I love your guide. It’s very helpful for understanding time functions.

    I have a specific request in mind I was hoping you could help with.
    So I have a preexisting date table which already contains the current date and the associated last year’s date (with granularity of 1 day) in the same row.
    I was hoping, as every data is also labelled with the current date, to find out how I can associate every dataset with the appropriate last year dataset by linking two dates together and pulling up the previous year’s data that way and presenting it in a side by side comparison chart.

    If you have any ideas on how I might accomplish this I would greatly appreciate it.

    Thank you.

  5. Am not so experienced in DAX and have the following requirement:
    It should be represented as follows on each date of sales …
    … YTD + MTD + the same for the previous year + deviation – all no problem …

    BUT it should also be shown in addition to the selected date the entire previous year …

    Is this possible with DAX?

    So you choose for example 15.03.2018 and then the following columns should be calculated:
    from 01.01.2018 to 15.03.2018 -> OK
    from 01.03.2018 to 15.03.2018 -> OK
    from 01.01.2017 to 15.03.2017 -> OK
    Deviation year to last year -> OK
    from 01.01.2017 to 31.12.2017 -> ???

    I thank you in advance.

  6. Thanks, Matt.
    It’s really a good post.
    I have one question on dimensional modeling. In MDX generally we can create one dimension as Date intelligence and the attribute in the dimension have all members like YOY, MOM,YTD, QOQ,QTD, Previous month, etc… And with MDX code we can define individual members with their respective formulas. In that case its helpful for me to get all time intelligence by all measure groups. For example: I have 5 measure group and each group have let’s take 10 measures. If i need time intelligence for all measures I need to write individual code for individual measure. But with dimensional modeling same dimension can use for all measures and as a result it will give all time intelligence value. so that one formula can be use for number of measures. How can we build the same model in DAX?

    1. I guess there are various ways. One way is to write all the measures (Act, YTD, YOY etc) and then write a SWITCH measure to swap between these things. Yes you have to write all the measures, but that is not that hard.

  7. Hi Matt, I am new to BI. Seeing the slicer in your example, it seems to me that the maximum year value is 2004. Thus, MAX(Calendar[Year]) would be equal to 2004 and not 2003 since ALL(Calendar) removes slicer filter . Please can you help me to understand this point?

    1. FILTER has 2 parameters FILTER(Table,Formula). The Formula portion operates in the initial filter context without regard to what has happened in the Table portion. That is why it returns 2003 and not 2004

      1. Hi Matt,
        Wow! This is the first time I’ve seen this important detail! Could you please refer me to any documentation or link were this is explained?
        Thanks!

  8. I’m new to Power BI, I’m looking for rolling 12 months calculation in Power BI.

    if I select any one value from the month slicer, then, the bar chart should show last 12 months of values (each month value not cumulative).
    Example :- if I select “November 2017” from the slicer, then, bar chart should show from “December 2016” to “November 2017” (which means, including selected value also).

    Hope I have explained clearly.

    Can any one please help me…

  9. Dušan Roknić

    Reference guide for DAX could be downloaded from Microsoft web site. It doesn’t include newer functions. To get download link just type this search in google:
    data analysis expressions – dax – reference filetype:pdf site:download.microsoft.com

  10. Dillon Danielson

    Someone reached out to me about your Guide, the link is at the Top of the Article, depending on what browser you are using.

      1. Thanks for your blog. It is extremely well written and is my primary source along with the aussie books recommended by you.

        I´m quite new to BI. I tought I was doing fine with an “advanced” excel including array formulas and basic VBA. After that I changed to a job doing more operational tasks so I pretty much stopped studying excel since 2010.

        A couple weeks ago since I discovered the Power BI suite and SSBI supplements I am extremely excited and want to implement it pretty much in everything!

        One problem that I am having trouble to find some literature is about dealing with SCD. I feel I still have no base or the solutions are pretty much out of the box. Can you do some post about that? I would appreciate it very much!

        Thanks again, and excellent work!
        PS: Sorry for my bad and rusted english.

          1. As far as I know SCD is slowing changing dimensions in which the values of your lookup tables change over time. Depending on how your database is structured you may encounter some problems to tie your data in a historically correct manner.

            The closest thing I got from this by googling was at this post http://blog.gbrueckl.at/2012/02/handling-scd2-dimensions-and-facts-with-powerpivot/

            but the sample file doesn´t have any relationship between the tables, so I think the implementation might be not trivial.

            In my specific case, I have an inventory problem with several tools that are used in several locations, so each row in the data table is the tool going from location A to location B, but the total amount of tools may change every 6 months or so. Every week we take a snapshot of which tools are in which locations, so you start with the snapshot of the number of each tool inevery location possible and build the final status in the end of the report.

            The other thing is that the responsible for each location changes every couple months, so it´s kinda hard to keep track of it too.

            Thanks in advance!

          2. Ah yes, slow changing dimensions. I know what they are (clearly I just don’t know the abbreviation). It is an interesting problem because it is something that is normally dealt with by IT, not by self service BI users. But I understand there may be a need. I will add it to my list of blog topics (although I am normally driven by what ever takes my fancy at the time 🙂 ).

  11. The inbuilt time intelligence of DAX covers 80% of most business requirements. The irritation comes from the other 20%. In my 20% list are these,
    1. Current MAT, MAT LY, MAT 2 Years.
    2. Calculating Week number based upon MAT.
    3. 3,3,4 or 3,4,3 weeks to a Qtr.
    2. Changing YTD, Qtr or MAT to a Financial Year start, changing the week start from Sunday to Monday.

    I’ve worked around these irritations by adapting the Date Table. However, I would have thought that these measures are De-rigueur now and would have been included.

Leave a Comment

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

Scroll to Top