comparing none numeric data from this period to last period and displaying a trend indicator

[Embers]

Hi,

I am very new to Power BI and have a requirement that i cant quite get my head round.  DS is a sharepoint list.  I have a series of data grouped by period (these are bespoke periods so dates can't be used).  Data looks like this:-

Period    Parent Group     Subgroup    Area    Rag status

  10              Parent 1            Sub1          A1         Green

  10              Parent 1            Sub1         A2            Red

  10              Parent 1            Sub2         A3          Amber

  10              Parent 2            Sub3         A4           Amber

  9               Parent 1            Sub1          A1         Green

  9               Parent 1            Sub1         A2           Amber

  9               Parent 1            Sub2         A3          Green

  9               Parent 2            Sub3         A4           Red

so what i want to do is assuming the current period is 10 then i want to compare the rag status for a given Area (areas can only belong to 1 sub and 1 parent and same for subs) from the current period against the Rag Status from the pervious period and determine if it has improved degraded or satyed the same.  

Any help would be greatly appreciated.

[Mariusz]

Hi @Embers 

Try this, but you probably want to include the year in your period, sol like 20191, 20192....

Column = 
VAR __period = 'Table'[Period]
VAR __test = 
    CALCULATETABLE(
        SUMMARIZE( 'Table', 'Table'[Period], 'Table'[Rag Status] ),
        ALLEXCEPT( 'Table', 'Table'[Area ] ),
        'Table'[Period] < __period
    )
RETURN 
CALCULATE(
    SELECTEDVALUE( 'Table'[Rag Status] ),
    TOPN( 1, __test, 'Table'[Period], DESC )  
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Embers]

@Mariusz 

I can see what is going on there and what you are trying to do but it doesn't quite work.  I just get blanks rather than the data i am looking foris the TOPN at the end meant to be the alt for the SELECTEDVALUE ?  

[Icey · Accepted solution]

Hi @Embers ,

If you want to create a Column, try this:

Previous Period Rag Status = 
CALCULATE (
    MAX ( 'Table'[Rag status] ),
    FILTER (
        'Table',
        'Table'[Period]
            = EARLIER ( 'Table'[Period] ) - 1
            && 'Table'[Parent Group] = EARLIER ( 'Table'[Parent Group] )
            && 'Table'[Subgroup] = EARLIER ( 'Table'[Subgroup] )
            && 'Table'[Area] = EARLIER ( 'Table'[Area] )
    )
)

If you want to create a Measure, try this:

Previous Period Rag Status Measure = 
CALCULATE (
    MAX ( 'Table'[Rag status] ),
    FILTER (
        ALLSELECTED('Table'),
        'Table'[Period]
            = MAX ( 'Table'[Period] ) - 1
            && 'Table'[Parent Group] = MAX ( 'Table'[Parent Group] )
            && 'Table'[Subgroup] = MAX ( 'Table'[Subgroup] )
            && 'Table'[Area] = MAX ( 'Table'[Area] )
    )
)

 

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Embers]

@Icey 

Thats work brilliantly thank you very much. 

[Greg_Deckler]

If I am reading this correctly, you want a calculated column and use EARLIER. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[amitchandak]

You can do like

This period = calculate(countrows(table),filter(all(table),table[period] =max(table[period]))
last period = calculate(countrows(table),filter(all(table),table[period] =max(table[period])-1)

But prefer creating a separate table for period and do

This period = calculate(countrows(table),filter(all(period),period[period] =max(table[period])-1)
last period = calculate(countrows(table),filter(all(period),period[period] =max(table[period])-1)

[Embers]

Not sure i follow.  That just seems to give me the number of rows in a given period...

[amitchandak]

You can get like this

This period = calculate(max(status),filter(all(period),period[period] =max(table[period])-1)
last period = calculate(max(status),filter(all(period),period[period] =max(table[period])-1)

You can compare and get desired result