Get previous text value based on date filter

[GeertBaan]

Hi!

I'm trying to return a previous text-value, based on a date. I am reporting on Incidents (tickets) and I have a snapshot table containing multiple rows per ticket. Everytime the ticket-status is changed, a new row is created. I need a measure that gives me the latest status-change, based on a date-filter.

Lets say my report-page is filtered by this period: 25-09-2022 until 01-10-2022. The latest change previous to this period, occured on 13-09-2022. My measure should return this Status.Value.ID: 243ddef3-9f1c-46d0-96f4-f97fe5f55bbd. 

I've been trying some things with firstnongblankvalue, but I couldn't get it done. I would appreciate your help 🙂

Date	Ticketnr.	Status.Value.ID
26-9-2022	FIM2209087	4580feb2-841e-4459-a412-66a494f1390b
13-9-2022	FIM2209087	243ddef3-9f1c-46d0-96f4-f97fe5f55bbd
8-9-2022	FIM2209087	b2e12f1a-2613-4c6a-8818-ad385b9a3ae8
1-9-2022	FIM2209087	b2e12f1a-2613-4c6a-8818-ad385b9a3ae8

[DataInsights · Accepted solution]

@GeertBaan,

This solution uses a date table DimDate with a relationship to the fact table. The slicer uses DimDate[Date].

Measure:

Latest Change Previous Period =
VAR vMinSelectedDate =
    FIRSTDATE ( ALLSELECTED ( DimDate[Date] ) )
VAR vPreviousRows =
    FILTER ( ALL ( Table1 ), Table1[Date] < vMinSelectedDate )
VAR vMaxDate =
    MAXX ( vPreviousRows, Table1[Date] )
VAR vResult =
    MAXX (
        FILTER ( vPreviousRows, Table1[Date] = vMaxDate ),
        Table1[Status.Value.ID]
    )
RETURN
    vResult