DAX to retrieve most recent value before a date

[Anonymous]

Hi there. My sample data is as follows

DateTime	ID	TimeStamp	Value
2023-11-01 13:00	1	2023-11-01 12:48	100
2023-11-01 13:00	1	2023-11-01 12:52	150
2023-11-01 13:00	2	2023-11-01 12:32	50
2023-11-01 13:00	2	2023-11-01 12:40	75
2023-11-01 14:00	1	2023-11-01 13:15	20
2023-11-01 14:00	1	2023-11-01 13:25	30
2023-11-01 14:00	2	2023-11-01 13:35	40

I am trying to retrieve the latest timestamp before each datetime for each ID and then summing them together.

Results table should look like this

DateTime	Sum
2023-11-01 13:00	225 (150 + 75)
2023-11-01 14:00	70 (30 + 40)

I am using direct query mode

Can I have some help with some DAX that would achieve this?

Thanks

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Expected result measure: =
VAR _datetime =
    MAX ( Data[DateTime] )
VAR _t =
    FILTER (
        SUMMARIZE ( Data, Data[ID], Data[TimeStamp], Data[Value] ),
        Data[TimeStamp] <= _datetime
    )
VAR _resulttable =
    INDEX ( 1, _t, ORDERBY ( Data[TimeStamp], DESC ),, PARTITIONBY ( Data[ID] ) )
RETURN
    IF ( HASONEVALUE ( Data[DateTime] ), SUMX ( _resulttable, Data[Value] ) )

INDEX function (DAX) - DAX | Microsoft Learn