Explore variants of Time Intelligence in DAX

Author:Murphy | View: 22136 | Time: 2025-03-23 12:10:01

We have some valuable functions to calculate the previous year, YTC, etc., in DAX. But other functions allow us to calculate the same result differently. Let's explore our options and why they matter.

Introduction

When we look at the values in a report, we must give them meaning.

The most common way of doing this is by comparing them with the values of the previous period.

This is when Time Intelligence kicks in as one of the principal things we want to show in reports.

For example, look at the following report page:

Here, you can see the comparison of the Sales figures with the previous year, the Year-to-Date, and the rolling total values.

This is a typical report that makes extensive use of Time Intelligence logic.

Now, I will show you how to create the Measures to calculate these results and the variants using different Dax functions.

I will show the results only in tables for better readability.

Time Intelligence in DAX

We have a large number of Time Intelligence functions in DAX. Looking at the dax.guide, we find a list of over 35 functions.

Some functions incorporate the complete logic for Time Intelligence, like TOTALYTD() or SAMEPERIODLASTYEAR(). Alternatively, we can use more fundamental functions that must be used in conjunction with CALCULATE(), like DATEADD() or DATESBETWEEN().

I want to show you how to use the "easy to use" functions and then how to use the underlying functions to explore their benefits.

Sometimes, we get different results depending on the used function, and sometimes, we can avoid problems by using the correct function.

In the conclusion, I will explain why it matters to know the different functions and your options to do the calculations.

Date table

We must have a proper date table to use the Time Intelligence functions properly.

When I search Google for "Why do we need a date table in Power BI," I get 149,000,000 results. So, there is a lot of stuff out there to learn why we need it.

And one of my first articles here on Medium was about Date tables:

3 Ways to Improve your reporting with an expanded date table

To leave it short, I can do many cool things with a central date table.

For example, I can add columns with particular logic to the date table to facilitate my work in DAX and Power BI.

For the rest of this article, I suppose you know why and how to build a date table and that you have marked this table as a "date table" in the Data model (Set and use date tables in Power BI Desktop – Power BI | Microsoft Learn).

Year-to-Date (YTD)

I take the YTD function as a placeholder for the other two variants for Quarter-To-Date (QTD) and Month-To-Date (MTD).

YTD calculates the expression for all dates from the start of the year to the current date (of the current filter context).

Let's start with the TOTALYTD() function:

Sales YTD = TOTALYTD([Sum Sales], 'Date'[Date])

This function works flawlessly if the Date table is marked as "Date table" in the Data model.

If you don't want to do this, this function doesn't work.

In this case, the DATESYTD() function must be used in conjunction with the ALL() function:

Online Sales (YTD) =
VAR YTDDates = DATESYTD('Date'[Date])

RETURN
  CALCULATE([Sum Online Sales]
          ,YTDDates
          ,ALL('Date')
          )

Sidenote: If you don't want to set your date table as a "Date table" in your data model, you must add the ALL(‘Date') filter in all Measures.

I prefer using the form with CALCULATE() anyway.

As I always mark my date tables as "Date table", I don't need to add the ALL(‘Date') filter:

Online Sales (YTD) =
VAR YTDDates = DATESYTD('Date'[Date])

RETURN
  CALCULATE([Sum Online Sales]
          ,YTDDates
          )

Any of these three forms gives the same result:

The form with CALCULATE() and DATESYTD() is more flexible and allows additional logic if necessary. And TOTALYTD() performs the same process as combining the former two functions.

I checked it in DAX Studio using the Server Timings, and the Execution Plans are identical.

Previous Year (PY)

To get the value for the previous year, we can use the SAMEPERIODLASTYEAR() function:

Sum Online Sales PY =
CALCULATE([Sum Online Sales],
        SAMEPERIODLASTYEAR('Date'[Date])
        )

Like TOTALYTD(), this is an easy way to calculate the previous year's value.

To understand what this function does, we can use DATEADD().

Previous Year – with DATEADD

The expression to calculate the previous year's value with DATEADD() is the following:

Online Sales (PY 2) =
CALCULATE(
        [Sum Online Sales]
          ,DATEADD('Date'[Date], -1, YEAR)
        )

In both cases, the result is the same:

Like with the YTD calculation, SAMEPERIODLASTYEAR() executes the same process as when we write it with DATEADD().

But what sets DATEADD() apart from SAMEPERIODLASTYEAR() is that I can define the period and the distance from which I want to get the data.

For example, I want to get the previous Quarter:

Online Sales (PQ) =
CALCULATE(
        [Sum Online Sales]
          ,DATEADD('Date'[Date], -1, QUARTER)
        )

Or do I want to get the previous Month:

Online Sales (PM) =
CALCULATE(
        [Sum Online Sales]
          ,DATEADD('Date'[Date], -1, MONTH)
        )

And for the previous Semester, I can do it in this way:

Online Sales (PS) =
CALCULATE(
        [Sum Online Sales]
           ,DATEADD('Date'[Date], -6, MONTH)
        )

The result of these variants is the following:

It's essential to understand that each of these Measures calculates the previous period (Year, Semester, Quarter, and Month, at each level of the Calendar hierarchy:

For me, it's intuitive that the results look like this, but I think it's worth pointing out this detail.

One important detail: DATEADD() can work with Days, Months, Quarters, and Years. This is why I need to go back six months or two quarters when I have to get the previous semester.

And there is more to explore when we talk about Time Intelligence.

What's about PARALLELPERIOD()?

The PARALLELPERIOD() function is very interesting.

I strongly recommend reading the description on dax.guide and watching the attached video.

In short, PARALLELPERIOD() expands the current Filter context on a Date to the entire period.

Look at the following Measure:

Online Sales PP 3 Month =
CALCULATE([Sum Online Sales]
          ,PARALLELPERIOD('Date'[Date], -3, MONTH)
          )

At first glance, it looks similar to a Measure that uses DATEADD() to go back 3 Months.

But look at the Results:

As you can see, the result for the Sales three months ago is the same even at the Day level.

Whereas, when using DATEADD(), the result at the Day level is the same as at the Month level, but for the previous period, as set in the Measure (for example, the previous quarter).

When I write another Measure, like this:

Online Sales PP 2 Quarter =
CALCULATE([Sum Online Sales]
          ,PARALLELPERIOD('Date'[Date], -2, QUARTER)
          )

The result is the following:

As you might expect, the result is the same for the Days within each month.

As mentioned earlier, look at the description for this function. It might surprise you.

Sliding Window (Last three months)

The last scenario to explore is the so-called "Sliding Window".

This is when you want to calculate the sum of a defined time window.

For example, "Last three months" will calculate the sum of the last three months based on the actual date.

We use the DATESINPERIOD() and the PARALLELPERIOD() functions to achieve this.

But take care. These two functions work differently and give you different results.

Let's look at both and check the result.

To calculate the result for the last three months using DATESINPERIOD(), we can write this Measure:

Online Sales (Last three Months) =
VAR FirstSelDate = MAX('Date'[Date])

RETURN
  CALCULATE([Sum Online Sales]
            ,DATESINPERIOD('Date'[Date], FirstSelDate, -3, MONTH)
            )

Let's look at the result:

As you can see in the screenshot above, the sum for each month is the sum of the actual month plus the previous two months.

But take care: When we look at the single days, the result is slightly different:

This is because the Measure gets the last date for each row and uses it as the starting point to "travel" back for three months.

Consequently, when we look at single days, the starting point is that specific day, not the entire month.

A variant to get the sum of the previous three months can be the following:

Online Sales (Last three Months) v2 =
VAR FirstSelDate = MIN('Date'[Date]) - 1

RETURN
  CALCULATE([Sum Online Sales]
            ,DATESINPERIOD('Date'[Date], FirstSelDate, -3, MONTH)
            )

This Measure returns the sum of Online Sales for the last three months starting from the first date of the selected period minus one day. So, the last day of the previous period.

I use the word "period". But I should use the word "interval", as, according to dax.guide, I can use DATESINPERIOD() with the interval of Day, Month, Quarter, and Year.

In the case below: The Sales for the three months before the current month.

This Measure returns the same result as the first one when observing the results for the days.

The following variant uses DATESBETWEEN():

Online Sales Last three Months (DP) =
VAR FirstDateInPeriod = EOMONTH(MIN('Date'[Date]), -3) + 1
VAR LastDateInPeriod = EOMONTH(MAX('Date'[Date]), 0)

RETURN
  CALCULATE([Sum Online Sales]
           ,DATESBETWEEN('Date'[Date], FirstDateInPeriod, LastDateInPeriod)
           )

The function DATESBETWEEN() returns a list of Dates between the first and the second date (second and third parameter).

In this case, I use the EOMONTH() function to calculate the first and the last day of the three-month windows

The result is almost the same as with DATESINPERIOD():

But there are two significant differences between using DATESINPERIOD() and DATESBETWEN():

The result is the same when using DATESBETWEEN(), even at the Day level.
But the result is wrong at the level above the Month (Quarter, Semester, and Year). This happens as the Measure gets the current period's first and last date. At the levels above the Month, they are the dates based on the current level.

To solve the second issue, we can change the Measure to the following:

Online Sales Last three Months (DP) v2 =
VAR FirstDateInPeriod = EOMONTH(MIN('Date'[Date]), -3) + 1
VAR LastDateInPeriod = EOMONTH(MIN('Date'[Date]), 0)

RETURN
  CALCULATE([Sum Online Sales]
          ,DATESBETWEEN('Date'[Date], FirstDateInPeriod, LastDateInPeriod)
          )

This is the result:

I marked the previous result, with the wrong results for the Years, Semesters, and Quarters in red and the new results in Blue.

At this point, you must decide which variant will best suit your needs, as the requirements might change from case to case.

Why do it differently

So, why should we consider the different methods of calculating Time Intelligence Results?

There are many reasons for doing it.

For example, to understand the effect of the different functions.

All these functions return a table with one column and a list of Dates. But the different functions build this table in slightly different ways.

Consequently, now we are in a better position to fulfill the requirements for the reporting as we know more ways to provide the needed result.

Moreover, we can propose different solutions and challenge our Stakeholders to think about them and find the best solution for their needs.

The next reason is flexibility.

Consider the two variants for YTD. While TOTALYTD() delivers the correct result, we are more flexible when using the second variant with CALCULATE() and DATESYTD() without losing anything in performance or efficiency.

And now we are at the last reason in understanding the different variants for these Measures: Performance and efficiency.

A specific calculation might work without problem in one scenario but could be slow in another.

So, knowing how to do it differently could be a lifesaver.

For example, the different variants for "Last three Months" deliver different results while being very efficient. I analyzed all four variants, and the performance is very similar.

Conclusion

After exploring the different variants, we have a more extensive toolbox when calculating results involving Time Intelligence functions.

Given the many ways we can calculate a result and the many differences in our stakeholders' understanding of the business logic, the larger the toolbox, the better.

But sometimes, we must take care of many details when elaborating a possible solution as "the devil lies in the detail" and we must consider how our users can use the Measures in their reports.

Here, I covered only a small number of Time Intelligence functions available in DAX. There are a lot more that cover specific requirements. I strongly recommend looking at dax.guide and exploring the possibilities.

As usual, I learned some details while writing about this complex topic, and I hope you learn more about it.

Leave a comment if there is any topic you are interested in that I could cover in future articles.

References

The SQLBI article Time Intelligence in Power BI Desktop is a very useful source about this topic. You can find more information on working with Time Intelligence functions there.

I analyzed the performance with DAX Studio and the built-in Server Timings feature. To learn more about this feature, I recommend reading my article on this topic:

How to get performance data from Power BI with DAX Studio

I use the Contoso sample dataset, like in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described here.

I enlarged the dataset to make the DAX engine work harder. The Online Sales table contains 71 million rows (instead of 12.6 million rows), and the Retail Sales table contains 18.5 million rows (instead of 3.4 million rows).