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.

imranamikhan's avatar
imranamikhan
Helper V
5 years ago

Thanks for your help on this Icey  it is really appreciated.

 

This is almost there, but I have noticed the calculation only works if the RAG value is the same for each category per period.

 

For example, if I change the RAG value to different RAGs (e.g. A & G) in Period 2, the calculation fails and only returns one RAG value.

 

Icey's avatar
Icey
Community Support
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.

  • imranamikhan's avatar
    imranamikhan
    Helper V
    5 years ago

    You are amazing. Thank you for your support and help on this.