topic Re: Difference in Ranks over different years in DAX Commands and Tips

Difference in Ranks over different years

batmit25 — Fri, 01 Dec 2023 17:17:44 GMT

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.

Re: Difference in Ranks over different years

Jihwan_Kim — Sat, 02 Dec 2023 06:38:42 GMT

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

Re: Difference in Ranks over different years

batmit25 — Sat, 02 Dec 2023 19:06:31 GMT

It worked! Thank you so much.