DAX Calculate the YOY score change

Viewed 20

enter image description here

I need to calculate the difference between each year and plot that change in a line graph

1 Answers

In Power BI your pivoted (Excel) table will get you nowhere. Be sure to unpivot all your "YYYY Score" columns in PowerQuery first. This should give you something like:

enter image description here

Next create an explicit measure for your score:

Max Score = MAX('Table'[Score])

With this you can now create your desired "Score Change":

Score Change =
VAR cursor =
    MAX ( 'Table'[Year] )
VAR prevYear =
    CALCULATE (
        MAX ( 'Table'[Year] ),
        'Table'[Year] < cursor
    )
VAR prevScore =
    CALCULATE (
        [Max Score],
        'Table'[Year] = prevYear
    )
RETURN
    IF ( NOT ( ISBLANK ( prevScore ) ),
        [Max Score] - prevScore
    )

Finally put the results in a matrix visual or a line chart:

enter image description here

Related