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.

amitchandak's avatar
amitchandak
Super User
5 years ago

imranamikhan , Try a new column like

 


new column =
var _max = maxx(filter(Table, [category] = earlier([category]) && [period] < earlier([Period])),[Period])
return
maxx(filter(Table, [category] = earlier([category]) && [period] =_max),[Rag])

  • imranamikhan's avatar
    imranamikhan
    Helper V
    5 years ago

    Thanks amitchandak .

    I was having issues with the solution but then as a test, I filtered the data to one programme only in query editor and the solution worked:

     

    For example:

     

     

    However, the reality is that the data includes multiple programmes:

     


    In the below screenshot I would expect the Previous RAG for Budget RAG to be G, not R.

     

    I cannot quite identify what I need to change in the DAX to get this work?