Get previous value according to the Slicer

[NerdFlanders]

Hi,

I hope you can help.

I have a sliver with 3 options, OP1, OP2, OP3.

No I have a line graph with these Options in the x-Axis and some values.

Now I want to show in the tooltip eg at OP1previous, which would be OP2, in OP2 the previous is value of OP3. This works fine so far.

But now, when i select in the slicer OP1 and OP3, I want to see in OP1 not as previous OP2, but OP3.

Does someone know how to implement this?

Thanks!

[v-yulgu-msft]

Hi @NerdFlanders,

Below is the result in my test for your reference.

Suppose this is the source table.

In this table, please add an index column in Query Edit mode.

 

Create a measure named as [Rank!] which returns order number.

Rank! =
RANKX (
    ALLSELECTED ( 'Get previous' ),
    CALCULATE (
        SUM ( 'Get previous'[Index] ),
        ALLEXCEPT ( 'Get previous', 'Get previous'[Category] )
    ),
    ,
    ASC
)

Create a measure to dynamically get previous value based on slicer selection.

Previous =
VAR currentrank = [Rank!]
RETURN
    CALCULATE (
        FIRSTNONBLANK ( 'Get previous'[Values], 1 ),
        FILTER ( ALLSELECTED ( 'Get previous' ), [Rank!] > currentrank )
    )

Result.

Best regards,
Yuliana Gu

[NerdFlanders · Accepted solution]

Hi @v-yulgu-msft,

thanks for your reply. It was almost what was looking for! Your code only works if you have two values selected.

I analysed what you did and changed it that way, that it works for as many selection as I need.

I changed the rank to two groupings

Rank = 
RANKX (
    ALLSELECTED ( 'Get Previous' );
    CALCULATE (
        SUM ( 'Get Previous'[Index] );
        ALLEXCEPT ( 'Get previous'; 'Get Previous'[Opt];'Get Previous'[Account] )
    );
    ;
    ASC
)

and the main changes in the "Previous" measure:

Previous = 
VAR currentrank = [Rank]
RETURN
    CALCULATE (
        sum('Get Previous'[Amount]);
		FILTER ( ALLSELECTED ( 'Get previous' ); [Rank] = currentrank + 1 )
    )

I hope that also helps others.