Help with comparing rankings

[IIN_role]

Hi
I'm ranking some people on my report, month by month but now I need to see if the rank of the selected month is greater o lower (and how many positions) versus the previous period
example

my formula for the ranking is this:

Rank Usuario = RANKX(

FILTER('rank_men_promo_aut',

AND('rank_men_promo_aut'[Mes]=EARLIER('rank_men_promo_aut'[Mes]),'rank_men_promo_aut'[Año]=EARLIER('rank_men_promo_aut'[Año]))),

'rank_men_promo_aut'[EF Mens],,DESC,Skip)

Then my DBB look like this:

I've tried but my measure just gives me the same rank for the actual and the previous month
Can anybody help me please

[v-deddai1-msft · Accepted solution]

Hi IIN_role,

Do you want to compare this month's rank with last month's rank? IF so, please refer to the following steps:

1.Create a calculated column as a parameter month1 to get the month of the previous month:

month1 = 'rank_men_promo_aut'[Año]*12 + 'rank_men_promo_aut'[Mes]

2.Create a ranking column to rank by month, You already have this ranking on your chart:

Rank =

RANKX (

    FILTER (

        'rank_men_promo_aut',

        'rank_men_promo_aut'[Año] = EARLIER ( 'rank_men_promo_aut'[Año] )

            && 'rank_men_promo_aut'[Mes] = EARLIER ( 'rank_men_promo_aut'[Mes] )

    ),

    'rank_men_promo_aut'[EF Mens],

    ,

    ASC,

    DENSE

)

3.Create a measure to get last month's rankings:

ranklaset month =

VAR mindate =

    CALCULATE ( MIN ( 'rank_men_promo_aut'[month1] ), ALL ( 'rank_men_promo_aut' ) )

VAR pre =

    CALCULATE (

        MAX ( 'rank_men_promo_aut'[month1] ),

        FILTER (

            ALLSELECTED ( 'rank_men_promo_aut' ),

            'rank_men_promo_aut'[month1] < MAX ( 'rank_men_promo_aut'[month1] )

        )

    )

RETURN

    IF (

        MAX ( 'rank_men_promo_aut'[month1] ) = mindate,

        BLANK (),

        CALCULATE (

            MAX ( 'rank_men_promo_aut'[Rank] ),

            FILTER (

                ALLSELECTED ( 'rank_men_promo_aut' ),

                'rank_men_promo_aut'[month1] = pre

            ),

            VALUES ( 'rank_men_promo_aut'[Usuario] )

        )

    )

4.Create a column to subtract the previous month's ranking from this month's ranking, and if it is the smallest month, it will be 0：

compare =

IF (

    'rank_men_promo_aut'[Rank] - [ranklaset month] = 'rank_men_promo_aut'[Rank],

    0,

    'rank_men_promo_aut'[Rank] - [ranklaset month_]

)

The following is the test sample I created for your reference. You can download the pbix file:https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EXKCdGtszHJMgcCZgH...

Best Regards,

Dedmon Dai