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: this column is a merge from a separate lookup table. It returns a period number associated to the record

Programme names: a list of programme names

Category: a set list of categories per programme

RAG: the RAG status assigned to each category

 

 

Problem:

 

It is simple enough to transform the data to display the latest period data only. However, I want to return the previous RAG status for each category as an additional column, based on the whatever the previous period number was for that record.

 

The problem is that the period number can be variable, so I cannot simply subtract 1 from the sort index.

 

In the example above, we have 3 entries for Programme 1. An entry from Period 8 (the current period), as well entries for Period 2 and Period 1.

 

The expected result is to display data for Period 8, but also return the RAG from the previous Period (for Programme 1 this would be Period 2) as an additional column.

 

If a previous period does not exist at all (such as in Programme 2), then return blank.

 

 

Any support to point me in the right direction would be appreciated.

 

Best regards,

AmiK

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.

10 Replies

  • 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?

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi imranamikhan ,

     

    I create calculated columns, can this meet your requirements?

    LastIndex_ = 
CALCULATE (
    MAX ( 'Table'[Index] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Programma_Name], 'Table'[Category] ),
        'Table'[Index] < EARLIER ( 'Table'[Index] )
    )
)

    Last RAG = 
IF (
    NOT ( ISBLANK ( 'Table'[LastIndex_] ) ),
    CALCULATE (
        MAX ( 'Table'[RAG] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[LastIndex_] ) )
    )
)

     

     

    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.