Forum Discussion

batmit25's avatar
batmit25
Frequent Visitor
2 years ago
Solved

Difference in Ranks over different years

Hi everyone, 

 

I'm trying to work on a Country Ranking Report and to write a DAX Measure which would help me to see what the Difference in Rank of a Country is from it's rank in the previous year.

 

Sample Data 

CountryRank Year
A12017
B22017
C32017
B12018
A22018
C32018
B12019
C22019
A32019

 

Wanted Result 

slicer selection : year 2018 

 

CountryRankYearDifference From Prev Year 
B12018+1
A22018-1
C320180

 

Also there are some countries in the dataset that are ranked in a particular year but maybe not in the year before or next so hence there's no record of that country alongside those years after or before. If possible would there be a way to show that Country's Rank difference as [N.R] in that particular case.

  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

     

    Rank measure: = 
IF( HASONEVALUE(country_dim[Country]), MAX(rank_fct[Rank]))

     

     

    Diff from prev year measure: = 
VAR _prevyear =
    MAX ( year_dim[Year] ) - 1
VAR _prevyearrank =
    CALCULATE ( [Rank measure:], year_dim[Year] = _prevyear )
RETURN
    IF (
        NOT ISBLANK ( [Rank measure:] ) && NOT ISBLANK ( _prevyearrank ),
        SWITCH (
            TRUE (),
            [Rank measure:] > _prevyearrank,
                "+ " & [Rank measure:] - _prevyearrank,
            [Rank measure:] < _prevyearrank,
                "- " & _prevyearrank - [Rank measure:],
            "0"
        ),
        "NR"
    )

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    2 years ago

    Hi,

    Please check the below picture and the attached pbix file.

     

     

     

     

    Rank measure: = 
IF( HASONEVALUE(country_dim[Country]), MAX(rank_fct[Rank]))

     

     

    Diff from prev year measure: = 
VAR _prevyear =
    MAX ( year_dim[Year] ) - 1
VAR _prevyearrank =
    CALCULATE ( [Rank measure:], year_dim[Year] = _prevyear )
RETURN
    IF (
        NOT ISBLANK ( [Rank measure:] ) && NOT ISBLANK ( _prevyearrank ),
        SWITCH (
            TRUE (),
            [Rank measure:] > _prevyearrank,
                "+ " & [Rank measure:] - _prevyearrank,
            [Rank measure:] < _prevyearrank,
                "- " & _prevyearrank - [Rank measure:],
            "0"
        ),
        "NR"
    )