Under the Hood: How DAX Works with Filters

Contents

Let’s start with a simple table: Figure 1 – The simple table to start with (Figure by the Author) Each row in the matrix visual shows the total online sales for each month.So far, so good. The interpretation is that we see the total online sales filtered by month. But this isn’t the entire truth. Let’s take a look at the data model: Figure 2 – The part of the data model with the Date table and the fact table (Figure by the Author) When you look carefully, you see that the relationship is built between the two date columns. There is no relationship to the month column. When we take this route, the interpretation above is not entirely accurate. The complete interpretation should be: Each row shows the total of online sales filtered by the date table. The rows of the Date table are grouped by the months. Each row shows the total sales for all days for each month. We are a step closer to understanding DAX in general, and time intelligence functions in particular, when we realize this detail. Let’s go one step further. YTD and the Basic query Go back one year – some examples Custom logic Conclusion References

YTD and the Basic query

Now, let’s add a YTD Measure to examine what happens:

Figure 3 – The YTD Measure and the results are in the same table as before (Figure by the Author)

The Measure is nothing special, and the results are easy to understand.

Now, let’s take a look at what precisely the DATESYTD() function does.

The explanation from dax.guide says: “Returns a set of dates in the year up to the last date visible in the filter context”.

What does this mean exactly?

To dive into this question, let’s write a DAX query first, to get the list of dates in June 2024, as is done in the visualization above:

DEFINE
    VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
    VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
    
    
EVALUATE
    SUMMARIZECOLUMNS('Date'[Date]
                        ,YearFilter
                        ,MonthFilter
                        )

The result is a list of 30 days for June:

Figure 4 – Basis Query to get all days for June 2024 (Figure by the Author)

This is the filter applied to the row for June 2024 in the Matrix shown above.

What is the result when we apply the DATESYTD() function to the result?

Here is the query:

DEFINE
    VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
    VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
    
    VAR BasisDates = CALCULATETABLE(
                            SUMMARIZECOLUMNS('Date'[Date]
                                        ,YearFilter
                                        ,MonthFilter
                                        )
                                    )
    
    VAR YTDDates = DATESYTD(TREATAS(BasisDates, 'Date'[Date])
                                    )
                                    
EVALUATE
    YTDDates

And here, the result:

Figure 5 – List of dates starting on the first of January up to the last day of June 2024 (Figure by the Author)

It’s a list of 182 rows, containing all dates starting from the beginning of the year up to the last day of June 2024.

This is the definition of YTD.

When we look at the following Measure:

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

RETURN
    CALCULATE([Sum Online Sales]
                ,YTDDates
                )

We realize that the variable YTDDates is “only” a list of dates applied as a filter to the CALCULATE() function.

This is the key to all Time intelligence functions.

Go back one year – some examples

What happens when applying another function to the result?

For example, SAMEPERIODLASTYEAR()?

To answer this question, I use the following DAX query:

DEFINE
    VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
    VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
    
    VAR BasisDates = CALCULATETABLE(
                            SUMMARIZECOLUMNS('Date'[Date]
                                        ,YearFilter
                                        ,MonthFilter
                                        )
                                    )
    
    VAR YTDDates = DATESYTD(TREATAS(BasisDates, 'Date'[Date])
                                    )
                                    
    VAR YTDDatesPY = SAMEPERIODLASTYEAR(YTDDates)
                                    
EVALUATE
    YTDDatesPY

I intentionally separated the calling of SAMEPERIODLASTYEAR() from DATESYTD() to make it easier to read. It would have been possible to nest DATESYTD() into SAMEPERIODLASTYEAR().

This time, we have 181 rows, as 2024 was a leap year.
And the dates are shifted back by one year:

Figure 6 – The result of the query after applying SAMEPERIODLASTYEAR() (Figure by the Author)

So, again, when we apply a Time Intelligence function to a measure, the function, for example, DATESYTD(), returns a list of dates.
Please note: When applying the filter, any existing filters on the date table are removed.

Custom logic

Now, let’s use this knowledge on custom time intelligence logic.

First, let’s slightly change the filter for the year and month:

DEFINE
    VAR YearMonthFilter = TREATAS({ 202406  }, 'Date'[MonthKey])
    
EVALUATE
    SUMMARIZECOLUMNS('Date'[Date]
                        , YearMonthFilter
                        )

The result of this query is the same as at the beginning of this article.

This time, I set the filter with a numerical value on the [MonthKey] column.

How can I go back to the previous year?

If you think mathematically, it’s just by subtracting 100:

202406 – 100 = 202306

Let’s try it:

Figure 7 – The result of the query after deducting 100 from the [MonthKey] column (Figure by the Author)

You can also do this with other numeric formats.

When you take a fiscal year, for example, like this: 2425 (For the Fiscal Year 24/25)

You can deduce 101 to get the previous fiscal year: 2425 – 101 = 2324

Another example of a custom time intelligence logic is a running average, where for each day, we calculate the average value over the past 10 days:

Figure 8 – The Code and the results of the Measure for a moving average over the previous ten days (Figure by the Author)

As the content of the variable DateRange is again a list of dates, I can apply the SAMEPERIODLASTYEAR() function to it, and get the result I need:

DEFINE
    VAR YearFilter = TREATAS({ 2024 }, 'Date'[Year])
    VAR MonthFilter = TREATAS({ 6 }, 'Date'[Month])
    
    // 1. Get the first and last Date for the current Filter Context
    VAR MaxDate = CALCULATE(MAX( 'Date'[Date] )
                            ,YearFilter
                            ,MonthFilter
                            )
    
    VAR MinDate =
        CALCULATE(
            DATEADD( 'Date'[Date], - 10, DAY )
            ,'Date'[Date] = MaxDate
            )
    
    // 2. Generate the Date range needed for the Moving average (Four months)
    VAR  DateRange =
     CALCULATETABLE(
        DATESBETWEEN( 'Date'[Date]
            ,MinDate
            ,MaxDate
        )
    )
    
EVALUATE
    SAMEPERIODLASTYEAR( DateRange )

And this is the result:

Figure 9 – The result for the moving average for the previous year (Figure by the Author)

This logic returns 11 rows, as it includes the last day of the month. Depending on the required result, we need to adjust the way we calculate the first and last dates of the date list (the filter applied to the measure).

Of course, this is a repetition of what I showed above. However, it demonstrates that the same approach can be applied to various scenarios.

As soon as you understand this, your work with time intelligence functions and other functions that accept tables of values as input will become much easier to comprehend and master.

Conclusion

While I used DAX Studio for the queries, you can use the same queries in the DAX Query tool within Power BI Desktop.

I intentionally used these queries to demonstrate that we work with tables all the time in DAX, even though we may not always be aware of it.

But it’s an important detail that helps us on our way to understanding DAX.

Although some of the DAX code shown here may be obsolete with the advent of the new calendar-based time intelligence feature in Power BI, the principles explained here remain valid. The functions, such as DATESYTD() or SAMEPERIODLASTYEAR(), still exist and work in the same way as before. For the time being, nothing will change on this side, as the concepts described here are still valid.

References

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

The Contoso Data can be freely used under the MIT License, as described in this document. I changed the dataset to shift the data to contemporary dates.