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 Icey for looking into this.

 

This is close but the calculation looks like it is running in ascending order.  The calculation is returning the RAG for the lowest Period Number (Period 1), and then returning the RAG value for previous Period in the Last RAG column (Period 2).

 

This is incorrect because Period 8 is the latest/current RAG for the selected Programme, and the Last RAG column should be derived from whatever the previous Period number is for the Programme in descending order.

 

So for example, Programme 1 has three periods. Period 8, Period 2, and Period 1. If Period 8 is selected, then the Last RAG should be derived for whatever the values are in Period 2.

 

You can see this is happening because in Period 8, the Last RAG column is displaying Blank.

 

 

Icey's avatar
Icey
Community Support
5 years ago

Hi imranamikhan ,

 

If so, just try to create a calculated column or measure like so:

 

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'[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'[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.