Calculating score change year on year

[Ems800]

Hi all,

I'm going around in circles a little and hoping someone can point me in the right direction as I can't seem to work it out. I'd like to create a new measure/calculated column that calculates the difference between scores year on year. This is for survey data, so will include survey year, theme, theme score, team name etc. Example table below - 

Survey year	Team	Theme Code	Theme	Score
2021	Team A	M1	Morale	5.2
2022	Team A	M1	Morale	6.3
2023	Team A	M1	Morale	6.1
2021	Team B	M1	Morale	5.2
2022	Team B	M1	Morale	6.3
2023	Team B	M1	Morale	6.1
2021	Team A	E1	Engagement	7.7
2022	Team A	E1	Engagement	7.8
2023	Team A	E1	Engagement	8.0
2021	Team B	E1	Engagement	5.4
2022	Team B	E1	Engagement	5.3
2023	Team B	E1	Engagement	5.7

Any pointers would be greatly appreciated!

[v-xuxinyi-msft · Accepted solution]

Hi @Ems800 

You can create measures as follow.

2023-2022 = 
VAR _2022 = CALCULATE(SUM(survey[Score]), FILTER(survey, survey[Survey year] = YEAR(TODAY()) - 2))
VAR _2023 = CALCULATE(SUM(survey[Score]), FILTER(survey, survey[Survey year] = YEAR(TODAY()) - 1))
RETURN
_2023 - _2022

2022-2021 = 
VAR _2021 = CALCULATE(SUM(survey[Score]), FILTER(survey, survey[Survey year] = YEAR(TODAY()) - 3))
VAR _2022 = CALCULATE(SUM(survey[Score]), FILTER(survey, survey[Survey year] = YEAR(TODAY()) - 2))
RETURN
_2022 - _2021

Is this the result you expect?

If I've misunderstood you, please provide the result you are hoping for: How to provide sample data in the Power BI Forum - Microsoft Fabric Community. Or show it as a screenshot. Please remove any sensitive data in advance.

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sjoerdvn]

you can try a measure like this:

Score yoy change = 
VAR pyr = MAX(Survey[Survey year]) -1
RETURN AVERAGE(Survey[Score]) - CALCULATE(AVERAGE(Survey[Score]), Survey[Survey year] = yr)