Get variable previous record

[imranamikhan]

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

[Icey]

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]

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]

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]

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 · Accepted solution]

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]

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

[imranamikhan]

Hi @amitchandak, any thoughts on this?

[amitchandak]

@imranamikhan , not able to check your reply yet.

[amitchandak]

@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]

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?