Difference in Ranks over different years

[batmit25]

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 

Country	Rank	Year
A	1	2017
B	2	2017
C	3	2017
B	1	2018
A	2	2018
C	3	2018
B	1	2019
C	2	2019
A	3	2019

Wanted Result 

slicer selection : year 2018 

Country	Rank	Year	Difference From Prev Year
B	1	2018	+1
A	2	2018	-1
C	3	2018	0

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.

[batmit25]

It worked! Thank you so much. 

[Jihwan_Kim · Accepted solution]

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"
    )