- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Click here to visit my LinkedIn page
Click here to schedule a short Teams meeting to discuss your question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It worked! Thank you so much.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Click here to visit my LinkedIn page
Click here to schedule a short Teams meeting to discuss your question.

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
03-17-2025 07:40 AM | |||
05-07-2025 11:45 AM | |||
11-13-2024 04:40 AM | |||
12-14-2024 04:34 PM | |||
Anonymous
| 03-27-2025 08:07 AM |
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |