Explore Variants of Time Intelligence in DAX | by Salvatore Cagliari | Nov, 2023

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.

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.

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.