Forum Discussion

imranamikhan's avatar
imranamikhan
Helper V
5 years ago
Solved

Get variable previous record

Hello everyone,   Context:   I have a query which displays a log of programme status information across multiple periods.   Index: a standard INDEX generated in Power BI Period Sort Index: thi...
Need Help
  • Icey's avatar
    Icey
    5 years ago

    Hi imranamikhan ,

     

    Try this:

    Last RAG Column =
VAR LastPeriod_ =
    CALCULATE (
        MAX ( 'Table'[Period Sort Index] ),
        FILTER (
            'Table',
            'Table'[Programma_Name] = EARLIER ( 'Table'[Programma_Name] )
                && 'Table'[Period Sort Index] < EARLIER ( 'Table'[Period Sort Index] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[RAG] ),
        FILTER (
            'Table',
            'Table'[Programma_Name] = EARLIER ( 'Table'[Programma_Name] )
                && 'Table'[Category] = EARLIER ( 'Table'[Category] )           -----------added
                && 'Table'[Period Sort Index] = LastPeriod_
        )
    )

    Last RAG Measure =
VAR LastPeriod_ =
    CALCULATE (
        MAX ( 'Table'[Period Sort Index] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Programma_Name] ),
            'Table'[Period Sort Index] < MAX ( 'Table'[Period Sort Index] )
        )
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[RAG] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Programma_Name], 'Table'[Category] ),  ---------edited
            'Table'[Period Sort Index] = LastPeriod_
        )
    )

     

     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.