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

     

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