Forum Discussion

GeertBaan's avatar
GeertBaan
Regular Visitor
3 years ago
Solved

Get previous text value based on date filter

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 🙂

 

DateTicketnr.Status.Value.ID
26-9-2022FIM22090874580feb2-841e-4459-a412-66a494f1390b
13-9-2022FIM2209087243ddef3-9f1c-46d0-96f4-f97fe5f55bbd
8-9-2022FIM2209087b2e12f1a-2613-4c6a-8818-ad385b9a3ae8
1-9-2022FIM2209087b2e12f1a-2613-4c6a-8818-ad385b9a3ae8
Need Help
  • DataInsights's avatar
    DataInsights
    3 years ago

    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

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    3 years ago

    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