Skip to content
Analytical AntsAnalytical Ants

Filter and Row Context in DAX — A Worked Example

By William Rodriguez
InsightsPower BIDAX

A Moving Annual Total looks like a single measure, but it only works because filter context and row context are each doing a different job at once. Here's the full breakdown.

TL;DR

Overview

I was working through the SQLBI Power Pivot Workshop with an intern, and a question came up about the [Sales Amt MAT] expression below. It is a great worked example for the interplay of filter and row context, so here we go.

DEFINE
    MEASURE Sales[Sales Amt] = /*Matches [Sales Amount] */
        SUMX (
            Sales
            , ( Sales[Unit Price] - Sales[Unit Discount] ) * Sales[Quantity]
        )
    MEASURE Sales[Sales Amt MAT] =
        CALCULATE (
            [Sales Amt]
            , DATESBETWEEN (
                'Date'[Date]
                , NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) )
                , LASTDATE ( 'Date'[Date] )
            )
        )
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Calendar Year Number]
    , 'Date'[Calendar Year Month Number]
    , TREATAS ( {2007,2008} , 'Date'[Calendar Year Number] )
    , "Amt" , [Sales Amt]
    , "Amt MAT" , [Sales Amt MAT]
)
ORDER BY [Calendar Year Month Number]

Data Model

A basic understanding of the data model matters here:

We will circle back, but it is prudent to understand that the grain is at the date level.

Evaluation

Start with the general structure:

Sales Amount MAT (Moving Annual Total)

Break this metric into its components:

    MEASURE Sales[Sales Amt MAT] =
        CALCULATE (
            [Sales Amt]
            , DATESBETWEEN (
                'Date'[Date]
                , NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) )
                , LASTDATE ( 'Date'[Date] )
            )
        )

DATESBETWEEN is a Time Intelligence Function. Effectively, there is "syntax sugar" that lets the user do more without explicitly programming every needed command.

Row Context

Look at the evaluation. Each row is based on a YearMonth summarization. Remember the data model, and how the date grain was important? It is because the only linked relationship between these tables is based on dates — not months, not years. So aggregating by a month is like summarizing all the dates within the date range called a "month."

Reference February 2008 in the evaluation. That one line is really the aggregation of the individual dates in that month. If you run the code in DAX Studio with Server Timings on, you can see that the VertiPaq engine obtains the data the same way — by pulling all the Sales data based on the Date column.

The takeaway: it is important to know the context for your data. In this instance, it is like asking, "what data can your data see based on where it is sitting in the report or visual?" In our example, your data is the data available to February 2008 — so it has access to all the dates in February 2008. We will use this next.

DATESBETWEEN — Date Range

The first argument wants a date range. As noted above, DATESBETWEEN has some Time Intelligence sauce that allows it to do more than normal.

If we were manually coding this, we would write something like:

CALCULATE(
    [Metric]
    , FILTER(
        ALL(Date[Date])
        , Dates[Date] > Start && Dates[Date] < END
    )
)

For now, it is sufficient to say that Date[Date] is the column that will provide the list of dates for the function.

DATESBETWEEN — End Date

Start with the third expression in the argument. This is the end date the user wants [Sales Amt] evaluated for.

In our February 2008 example, LASTDATE( 'Date'[Date] ) returns the last date available to "200802" — namely 2008-02-29.

DATESBETWEEN — Start Date

If you understand the end date, this section is easier.

NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Date'[Date] ) ) )

Working inside out:

So the start date is the first day after the last available date, moved back one year. Effectively, think of the metric as a rolling 12-month expression.

SAMEPERIODLASTYEAR is another Time Intelligence function, and a bit more complex — worth reading about on its own.

Sales Amount MAT — conclusion

Now that we have all the components, we can read the metric plainly:

So if the matrix is showing February 2008, the metric calculates everything from 2007-03-01 through 2008-02-29. The same happens for every other year-month value.

Final Thoughts

These are incredibly complex topics, but they are fundamental to truly understanding Power BI and DAX. Get the context model straight and the time-intelligence functions stop feeling like magic. I hope this helps you on your Power BI journey.

Frequently Asked Questions

What is the difference between filter context and row context in DAX?

Row context is the current row an expression iterates over; filter context is the set of filters applied to the model when a measure is evaluated — driven by the visual, slicers, and CALCULATE. A Moving Annual Total relies on both: row context sets the current month, and DATESBETWEEN modifies the filter context to a rolling 12-month window.

How does DATESBETWEEN build a moving annual total?

DATESBETWEEN takes a date column, a start date, and an end date. Using NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(...))) as the start and LASTDATE(...) as the end, it returns every date from one year plus a day back through the last visible date — a rolling 12-month range that shifts with each row.

Why does the date grain matter in this measure?

Because the only relationship between the Date and Sales tables is on the date column, a month total is really the aggregation of every date inside that month. Understanding that grain is what makes the time-intelligence functions predictable when the visual summarizes by year-month.

Working through your own DAX

Getting the numbers to tie is an architecture problem as much as a formula problem. If your reports don't reconcile or your model has grown past what one person can hold in their head, let's talk.

About the author — William Rodriguez is the founder of Analytical Ants, the delivery arm of Analytical Solutions. He spent roughly a decade architecting enterprise BI and data platforms for operations running $10M–$60B in revenue, and holds an MBA from Emory University's Goizueta Business School. More about Analytical Ants.