Measure on historical states

[Anonymous]

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!

[Jihwan_Kim · Accepted solution]

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]
    )

[Anonymous]

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!