Filter and Row Context in DAX - Example

Overview

I'm currently working with an intern on all things Microsoft Business Intelligence (I'll need to post about the 'track' later).

We are working through the SQLBI course, Power Pivot Workshop, and she had a question about the following expression: [Sales Amt MAT] (below)

I thought this was a great 'part two' (I still need to publish part one!) in the Filter/Row Context series. Here we go!

dax.do link

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

Let's get a basic understanding about the data model (shown below):

There is a 1:* (one to many) relationship between Date and Sales.
The active relationship being Date[Date] 1:* Sales[OrderDate]

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

Evaluation

Let's start with the general structure:

The grain of the table is YearMonth
For every YearMonth (in 2007 & 2008), we are returning the Amount and Amount MAT
Amount is straight forward: Iterate over the sales table doing the following: ( Unit Price - Unit Discount ) * Quantity

Sales Amount MAT (Moving Annual Total)

Let's break this metric into it's components:

Function: CALCULATE
- Expression: [Sales Amt] (See above)
- Filter Context: DATESBETWEEN
  - Date Range: 'Date'[Date]
  - Start Date: (See below)
  - End Date: (See below)

    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 allows the user to do more without explicitly programming every needed command. I'll touch on this lightly later on.

Row Context

Look at the evaluation. No seriously. Each row is based on a YearMonth summarization. Remember the Data Model, and how I said the date grain was important? It's because the only linked relationship between these tables are based on dates. Not months, not years, etc... So aggregating by a month is like summarizing all the dates within the date range called a 'month'. Not impressed? Follow me here.

Reference February 2008 in the report/evaluation (below, left). That one line is really the aggregation of the individual dates in that month (below, right).

Additionally, if you run the code in DAX studio with Server Timings on, you can see that the vertipaq enginge obtains the data in the same way (i.e., by obtaining all the Sales Data based on the Date column).

Wow, lot of talking to say, it's important to know the context for your data. In this instance, it's like saying, "What data can your data see based on where it's sitting in the report/visual". In our example, your data is the data available to 'February 2008'. So it has access to the all dates in February 2008. We will use this next.

DATESBETWEEN - Date Range

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

So if we were manually coding this, we would say something like this:

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

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

DATESBETWEEN - End Date

Let's start with the third expression in the argument. This is the end date that the user wants [Sales Amt] to be evaluated for.

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

We can prove this out by adding the following expression to our DAX (same thing as creating a metric and adding to a report utilizing a matrix)

Hopefully you see why I spent so much time on the Row Context Section 🙂

DATESBETWEEN - Start Date

If you understand the 'End Date', hopefully this makes this section easier.

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

Let's start inside out:

LASTDATE ( 'Date'[Date] ) = This is the last day of the month (as shown above)
SAMEPERIODLASTYEAR (LastDate) = This takes the last day of the month, and moves it back one year. So '2008-02-29' turns into '2007-02-28'
NEXTDAY (sameperiodlastyear) = This finds the next sequential day based on the argument value. In this instance, it takes '2007-02-28' and adds 1 day, '2007-03-01'

Therefore, the final conclusion being (🎺): "the start date is the first day after the last available date moved back one year"... 🤪 Lol.

Effectively, the start is the first non-contiguous date based on the last available date in the row context (i.e., what the data can see). I'm not sure that's much more helpful, but thing of the metric as a rolling 12 month expression.

SAMEPERIODLASTYEAR is another Time Intelligence function. However, it's a bit more complex so just read about it 😉

Sales Amount MAT conclusion

Now that we have all the components, we can understand the metric better:

"calculate the Sales Amount based on a date range that starts one year ago based on the last available date in being shown"

So, if the matrix is showing "Feb 2008", the metric will calculate everything based on 2007-03-01 until 2008-02-29. Same thing will happen for all the other year-month values.

Final Thoughts

Sometimes I feel really bad for my intern! These are incredibly complex topics, but they are SO fundamental to truly understanding the world of Power BI and DAX. I really hope this helps you on your Power BI journey!