Help understanding effect of using a measure within a calculate filter

[Ibu]

I have a list of transactions and a date table. Each transaction has an amount and a due date. My goal is to use a date slicer to view the sum of amounts that aren't due yet, relative to any point in time.

For this purpose, I created a measure on the Calendar table that gives me the end of the period date (MAX('Calendar'[Date])).

In crafting the measure for the "Before Due" card, I came across a behavior I don't understand.

These two ways of writing the measure without using the "PeriodEndDate" measure yield the expected result:

Before Due Amounts With Filter With Expression In Filter = 
    CALCULATE(
        SUM([Amount]),
        FILTER(
            Transactions,
            DATEDIFF([Due_Date], MAX('Calendar'[Date]), DAY) <= 0
        )
    )

Before Due Amounts With Inline Filter = 
    CALCULATE(
        SUM([Amount]), 
        Transactions,
        DATEDIFF(Transactions[Due_Date], MAX('Calendar'[Date]), DAY) <= 0)

While this one using the "PeriodEndDate" measure doesn't (it seems to do a sum of some kind, ignoring a date filter):

Before Due Amounts With Measure In Filter = 
     CALCULATE(
        SUM([Amount]),
        FILTER(
            Transactions,
            DATEDIFF([Due_Date], [PeriodEndDate], DAY) <= 0
        )
    )

The difference is in how I specify the end of period date. It works when I inline the MAX(...) logic, but ideally I would like to keep this as a measure on the Date table (as a way to re-use the code).

I'm looking for an explanation of why introducing the [PeriodEndDate] measure in my filter calculation yields an incorrect output. 

Here is a link to my sample PBIX: SampleForQuestion.pbix

[bcdobbs · Accepted solution]

Really interesting question!

You've already noted that the incorrect value 147 is the same as the sum of the Amount column in your transaction table as if it's not being filtered.

You've then realised that CALCULATE really does a FILTER statement under the hood:

    CALCULATE(
        SUM([Amount]),
        FILTER(
            Transactions,
            DATEDIFF([Due_Date], MAX('Calendar'[Date]), DAY) <= 0
        )
    )

FILTER is an iterator and so roles over each row in the Transactions table in turn. We can run the following query in DAX Studio to get a feel for what is happening on each row in the FILTER statement (using ADDCOLUMNS which is also an iterator):

EVALUATE
	ADDCOLUMNS (
  		Transactions,
  		"Max Date" , MAX ( 'Calendar'[Date] )
  	)

Returns:

(Kept it simple so 31 Jan 2022 is the last date in your calendar table when not filtered)

Running the same but with your measure:

EVALUATE
	ADDCOLUMNS (
  		Transactions,
  		"Max Date" , [PeriodEndDate]
  	)

Returns:

You can see that the max date being used on each row is actually the transaction date which is before each due date and so every row gets included hence the sum of the whole table.

Why though!

Firstly a measure forces a context transtion (eg it's automatically wrapped in its own CALCULATE... it moves all the columns of the expanded table into the filter context. The key word here is "expanded". Starting from Transaction the expanded table is all the columns in the transaction table AND all the columns on the one side of any many to one relationships come from it. 

Therefore when the first row is in the iterator the transaction date is 1st of january.... on the other side of the relationship the date column of your calendar is the 1st of january and so the max of the first of january is the 1st of january.

Have a read of Expanded tables in DAX - SQLBI

and https://www.sqlbi.com/articles/understanding-context-transition/

A better measure would be:

New Measure = 
VAR _MaxDate = [PeriodEndDate]

VAR _Result =     
    CALCULATE(
        SUM([Amount]),
        Transactions[Due_Date] >= _MaxDate
    )

RETURN _Result

Here we read the max date outside of the iterator and so it's just the max date you'd expect. I also think you can do away with the DATEDIFF.

[Ibu]

Thanks @bcdobbs, I was indeed missing the notion of implicit context transition when calling a measure from within a row context. Great links to understand the concepts too.

I like your suggestion to capture the measure value while it's still in the outer context. It seems like a safe practice in general.

While reading the linked articles I thought of an alternative solution where the context transition is scoped down to a single column (Due_Date), that doesn't affect the related Calendar table (because there is no relationship between the two tables using that field):

     CALCULATE(
        SUM([Amount]),
        FILTER(
            ALL(Transactions[Due_Date]),
            DATEDIFF([Due_Date], [PeriodEndDate], DAY) <= 0
        ) 
    )

Only posting this as well for the thought exercise as it seems to work as well (though I don't grasp the need to use `all(...)` instead of simply `Transactions[Due_Date]`).