average totals based on year and show % difference

[jonbox]

Hi,

I'm looking to createa measure that averages the totals from the past 5 years (not including current year) and then compares against average of the total of current year e.g.:

(Average of total spend 2017, total 2018, total 2019, total 2020, total 2021), Current year spend (2022), % difference of both.

e.g based on below table: 

(Average total spend 2017 (20000+5000), 2018 (15000), 2019 (16000), 2020 (14500), 2021 (8000)) = 15700

Current year spend = 10000

% difference of average last 5fy (15700) and Current year (10000) = 44.3%

Project name	Year	Spend
Proj1	2017	20000
Proj2	2017	5000
Proj3	2018	15000
Proj4	2019	1000
Proj5	2019	15000
Proj6	2020	7000
Proj7	2020	7500
Proj8	2021	8000
Proj9	2022	10000

[CNENFRNL · Accepted solution]

Make it dynamic for fun,

[Anonymous]

Hello

M1==> 

average prev years =

var maxyear=max('Average'[Year])

var nbyear=DISTINCTCOUNT('Average'[Year])-1

var amounttoaver=calculate(sum('Average'[Spend]),'Average'[Year]<>maxyear)

return

divide(amounttoaver,nbyear)

M2==> 

Amount Year N =

var maxyear=max('Average'[Year])

return

calculate(sum('Average'[Spend]),'Average'[Year]=maxyear)

Final ==> 

final pect = divide('Average'[Amount Year N],'Average'[average prev years])