Measure for "latest value in time period" not behaving as expected

[ArvindSh-MSFT]

Here's a simple example to illustrate my problem. Here's the model:

Here are the rows in the "fact" table:

My goal is to display the order value, by product, in the latest month selected in a slicer. For example, if the Date slicer is between Jan 1 2025 and March 1 2025, then the matrix should look like this:

LatestOrderValue is defined like this:

LatestOrderValue =

CALCULATE(

    SUM(Orders[Value]),

    FILTER ( Orders,  Orders[OrderDate] == LASTDATE(ALLSELECTED(Dates[Date]))

))

But when I use this as-is, I get the following:

If I remove the ProductID "row" from the matrix, I get the expected value which is 56:

Workarounds I have found:

1. Put a visual level Top N filter for Date to only include the latest date. I'd rather not do this as I need other Top N filter in the same visual.

2. Re-write the measure like this:

LatestOrderValue =

VAR MaxSelectedDate = LASTDATE(ALLSELECTED(Dates[Date]))

RETURN CALCULATE(

    SUM(Orders[Value]),

    FILTER ( Orders,  Orders[OrderDate] == MaxSelectedDate)

)

OR this:

LatestOrderValue =

VAR MaxSelectedDate = MAX(Dates[Date])

RETURN CALCULATE(

    SUM(Orders[Value]),

    FILTER ( Orders,  Orders[OrderDate] == MaxSelectedDate)

)

I'm at a loss to explain the behavior. Appreciate any clarification you can provide. Thanks in advance!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I think, we can try to use columns from dimension table in the CALCULATE function.

In the latest month order value: =
VAR _latestdate =
    MAX ( 'date'[Date] )
VAR _latestmonth =
    EOMONTH ( _latestdate, 0 )
RETURN
    CALCULATE ( SUM ( 'order'[value] ), EOMONTH ( 'date'[Date], 0 ) = _latestmonth )

[ArvindSh-MSFT]

@Jihwan_Kim many thanks for the effort to analyze and propose a solution. As you saw in my original message, I too had similar workarounds in place which are working for me. My main question is that of understanding the difference between why having an explicit variable for the max data outside of the CALCULATE, works, while having basically the same expression as a filter inside the CALCULATE, does not. In my model, the Date dimension filters the fact table with a 1-way relationship; so my presumption is that asking for the LASTDATE of selected values for that Date dimension, would have worked correctly and always return the "max data" regardless of the context (variable outside, or inline inside the CALCULATE). This is the core question. Thanks again for your help!

[v-aatheeque · Accepted solution]

Hi @ArvindSh-MSFT 

Based on your scenario, here are a few thoughts from my end that might help clarify the behavior:

• CALCULATE modifies the filter context before evaluating any filter expressions inside it.
• When LASTDATE(Date[Date]) is used directly inside CALCULATE (e.g., CALCULATE([Measure], Date[Date] = LASTDATE(Date[Date]))), the LASTDATE is evaluated after CALCULATE begins applying filter logic.
• This can cause LASTDATE to return a different result than expected, especially if the filters being applied by CALCULATE change the rows visible in the Date table.
• When you define a variable like VAR MaxDate = LASTDATE(Date[Date]) outside of CALCULATE, it's evaluated before any context changes take place.
• This means MaxDate holds the correct value based on the original filter context, regardless of what CALCULATE does after.
• The behavior difference is due to timing of context evaluation, not the expression itself & both expressions are logically the same, but CALCULATE’s internal evaluation order makes them behave differently.
•  models with a one way relationship from Date to Fact, the Date table drives filtering, so LASTDATE(Date[Date]) is only meaningful when evaluated in the correct (unmodified) context.
• Therefore, the variable approach works consistently because it avoids filter context interference by CALCULATE.

Hope this helps!!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

I think, we can try to use columns from dimension table in the CALCULATE function.

In the latest month order value: =
VAR _latestdate =
    MAX ( 'date'[Date] )
VAR _latestmonth =
    EOMONTH ( _latestdate, 0 )
RETURN
    CALCULATE ( SUM ( 'order'[value] ), EOMONTH ( 'date'[Date], 0 ) = _latestmonth )