topic Re: Measure on historical states in DAX Commands and Tips

Measure on historical states

Anonymous — Sat, 03 Feb 2024 09:53:28 GMT

Hi - I have been stuck on this seemingly simple problem for the last couple of days...
I have a table that stores changes in attributes of objects (Deal ID) that looks like this:

I have a separate DateDim table and I now need a measure to perform a calculation on the most recent historical state based on a Date slicer.
So for example on 31/12/2023 Deal ID 25 had an amount 90000 and stage D, and Deal ID 13 had an amount 30000 and stage C.
The measure should then show this result:

Many thanks for pointing me in the right direction!

Re: Measure on historical states

Jihwan_Kim — Sat, 03 Feb 2024 10:42:54 GMT

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

Expected result measure: = VAR _currentdate = MAX ( 'Calendar'[Date] ) VAR _currentstage = VALUES ( Stage[Stage] ) VAR _t = SUMMARIZE ( ALL ( Data ), 'Calendar'[Date], DealID[DealID], Stage[Stage], Data[Amount] ) VAR _lastnonblankdate = ADDCOLUMNS ( _t, "@lastnonblankdate", MAXX ( FILTER ( _t, DealID[DealID] = EARLIER ( DealID[DealID] ) && 'Calendar'[Date] <= _currentdate && Data[Amount] <> BLANK () ), 'Calendar'[Date] ) ) VAR _filter = SUMMARIZE ( _lastnonblankdate, DealID[DealID], [@lastnonblankdate] ) RETURN SUMX ( FILTER ( CALCULATETABLE ( SUMMARIZE ( Data, 'Calendar'[Date], DealID[DealID], Stage[Stage], Data[Amount] ), TREATAS ( _filter, DealID[DealID], 'Calendar'[Date] ) ), Stage[Stage] IN _currentstage ), Data[Amount] )

Re: Measure on historical states

Anonymous — Sat, 03 Feb 2024 11:40:53 GMT

This is amazing, it is exactly what I was looking for. I will need to study it for a while, because I don't think I would have ever gotten there by myself. Thank you so much Jihwan_Kim!