Average and percentage of change for each month //Revised and added files\\

[Anonymous]

//Revised and added files\\

Hi, 

I have some values (e.g. consumption for heat) on a monthly basis from 2017 (please see below image and/or Power BI file in the link). How can I add the average of each month (for the last three years), also the percentage of change of the values of this year compared with the last two years with some DAX formulas? I added two Excel files (one raw data and one to describe what I want to achieve). 

Note: Data continues to add up regularly per month (2021=>2022=>...) then the DAX formula should be compatible.

LINK for files: https://www.dropbox.com/sh/notqpq6nuvr779o/AABKz8BVY64DprrjrbamVC68a?dl=0 

Many thanks for your tips!

[lbendlin · Accepted solution]

I wish you wouldn't use the built in date hierarchies. They are bad to work with.

Avg_3LY_Same_Mth = 
var s=year(SELECTEDVALUE(data[Merged]))
var m=month(SELECTEDVALUE(data[Merged]))
return AVERAGEX(FILTER(ALL(data),month(data[Merged]) =m && year(data[Merged]) in {s-1,s-2,s-3}),data[Value])

YoY = 
var s=year(SELECTEDVALUE(data[Merged]))
var m=month(SELECTEDVALUE(data[Merged]))
var a= AVERAGEX(FILTER(ALL(data),month(data[Merged]) =m && year(data[Merged]) in {s-1}),data[Value])
return DIVIDE(AVERAGE(data[Value]),a)-1

Yo2Y = 
var s=year(SELECTEDVALUE(data[Merged]))
var m=month(SELECTEDVALUE(data[Merged]))
var a= AVERAGEX(FILTER(ALL(data),month(data[Merged]) =m && year(data[Merged]) in {s-1,s-2}),data[Value])
return DIVIDE(AVERAGE(data[Value]),a)-1

[Anonymous]

@lbendlin many thanks for your help, really appreciated it! I just a little bit fine-tuned your formulas and used them. Also added a new PBI file in the link:  https://www.dropbox.com/sh/notqpq6nuvr779o/AABKz8BVY64DprrjrbamVC68a?dl=0

Avg_3LY_Same_Mth =

var s=year(SELECTEDVALUE(data[Merged]))

var m=month(SELECTEDVALUE(data[Merged]))

return AVERAGEX(FILTER(ALL(data),month(data[Merged]) =m && year(data[Merged]) in {s,s-1,s-2}),data[Value])

YoY =

var s=year(SELECTEDVALUE(data[Merged]))

var m=month(SELECTEDVALUE(data[Merged]))

var a= AVERAGEX(FILTER(ALL(data),month(data[Merged]) =m && year(data[Merged]) in {s-1}),data[Value])

return IF((DIVIDE(AVERAGE(data[Value]),a)-1)=-1,"",(DIVIDE(AVERAGE(data[Value]),a)-1))

Yo2Y =

var s=year(SELECTEDVALUE(data[Merged]))

var m=month(SELECTEDVALUE(data[Merged]))

var a= AVERAGEX(FILTER(ALL(data),month(data[Merged]) =m && year(data[Merged]) in {s-2}),data[Value])

return IF((DIVIDE(AVERAGE(data[Value]),a)-1)=-1,"",(DIVIDE(AVERAGE(data[Value]),a)-1))

[lbendlin]

Please provide sample data in usable format (not as a picture) .

[Anonymous]

@lbendlin sorry for not sharing enough information. Now I revised the post and also added a link with files: 

https://www.dropbox.com/sh/notqpq6nuvr779o/AABKz8BVY64DprrjrbamVC68a?dl=0