DAX for last date value of measure

[insandur]

I have a table with shipment number ,dates and a measure "Backlog". Need to calculate the Total backlog of that shipment and consider the latest date value.

Date	Shipment	Measure(backlog)	Required Backlog (last date value)
30/10/2022	AX	720	0
31/10/2022	AX	0	0
30/10/2022	BX	840	0
31/10/2022	BX	0	0
30/10/2022	CX	35	0
31/10/2022	CX	12	12

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new column

Required backlog CC =
VAR _latestdate =
    MAXX (
        FILTER ( Data, Data[Shipment] = EARLIER ( Data[Shipment] ) ),
        Data[Date]
    )
VAR _backlog =
    MAXX (
        FILTER (
            Data,
            Data[Shipment] = EARLIER ( Data[Shipment] )
                && Data[Date] = _latestdate
        ),
        Data[Measure(backlog)]
    )
RETURN
    IF ( Data[Measure(backlog)] = _backlog, _backlog, 0 )