How do pivot table groupings interact with calculated filters in DAX? And how can I override them?

Question

I have a table in my data model that has on-hand product counts, dates, and inventory source table names. I want to create a pivot table that shows the total product counts for each source but only on the last recorded date in the table.

Here's a simplified version:

I have a pivot built that uses the following measure:

=VAR LastDay = MAX(FactTable[Date]) 
Return 

CALCULATE(
    sum(FactTable[On Hand Quantity]),
    FILTER(FactTable,FactTable[Date] = LastDay)
)

The ideas is that it looks up the last date in the FactTable then filters the table to only include rows with a date that equals LastDay, then provides the sum of the on hand quantity for the item in the pivot filter.

Something like this:

Instead here's the real pivot (filtered for a specific item):

The dates in Manual_Inv are all old and do not contain the LastDay. The pivot is showing the sum(FactTable[On Hand Quantity]) for the max date where the source table is Manual_Inv. The weirder thing though is that it seems to understand that the value it's showing in the table is not what I'm asking for so it does not include it in the grand total. The grand total is the actual total count for the last date available.

I'm super confused as to how the pivot table groupings interact with my DAX code. From what I can tell the pivot groups (Source Table) are applied to the FactTable, and then the DAX code is run independently on each grouping and providing a result as though each group was its own table. And then afterwards it looks at the result, checks which ones have dates that match LastDay and only include those values in the grand total.

Can someone offer some insight into what is going on here and how I can go about fixing it? My expected pivot looks the same as above, but the Current On Hand cell for Manual_Inv should be blank.

Will be away for a few days but I'll respond when I can.

Alexis Olson Alexis Olson · Accepted Answer · 2020-09-03T21:21:40

The maximum you are calculating for the LastDay variable is evaluated within the local filter context. This means that for Manual_Inv, it's looking up the last date only for the rows from the Manual_Inv source.

To fix this, you can define LastDay to consider everything selected, not just rows that correspond to the current row label. If there aren't any other tables involved (e.g. a calendar table with a relationship to your fact table) then you can probably write your variable like this instead:

VAR LastDay = CALCULATE ( MAX ( FactTable[Date] ), ALLSELECTED ( FactTable ) )

How do pivot table groupings interact with calculated filters in DAX? And how can I override them?

1 Answers