3 year rolling average required

[tgjones43]

I've seen a few rolling average posts and YouTube videos but can't seem to find one that works for my dataset, which contains the concentration of elements in rivers.

I have frequent measurements of multiple parameters across many sites over many years. This is a snippet of what the data looks like (there are many more sites, elements and dates):

Site	Element	Date	Concentration
A	Phosphate	05/01/2019	0.395
A	Phosphate	06/04/2019	0.108
A	Phosphate	03/07/2019	0.208
A	Phosphate	08/10/2019	0.234
A	Phosphate	01/02/2020	0.029
A	Phosphate	11/06/2020	0.301
A	Phosphate	01/08/2020	0.289
A	Phosphate	23/11/2020	0.094
A	Phosphate	03/01/2021	0.409
A	Phosphate	04/04/2021	0.041
A	Phosphate	05/08/2021	0.132
A	Phosphate	09/11/2021	0.203

and the column at the end is what I would like to compute:

Site	Element	Year	Annual Average	3 year rolling average
A	Phosphate	2019	0.23625	0.23625
A	Phosphate	2020	0.17825	0.20725
A	Phosphate	2021	0.19625	0.20358

so the 3 year rolling average for 2019 is the same as the annual average for 2019, because there isn't any data prior to that year, but the 3 year rolling average for 2021 is the average of all values from 2019-2021 inclusive.

Any help would be greatly appreciated!

[steph1234]

I was reading through this thread and was wondering what formula was used to calculate the annual average, if you could let me know that would be great. 

[amitchandak · Accepted solution]

@tgjones43 , with help from date table

3 Year Avg = CALCULATE(AverageX(Values('Date'[Year]),calculate(Average('Table'[Concentration] )))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,Year))

[tgjones43]

Hi @amitchandak

I have just realised this isn't performing the calculation as required, but that is my fault for not describing it properly. I think your solution is calculating the average value for each year, then calculating the average of those averages for the last 3 years. But I don't want it to calculate an average of averages, it should calculate an average from all the datapoints in that 3 year period.

Would you be able to modify the formula to do that?

Thank you!

[tgjones43]

thanks @amitchandak in my report the 3 year average is just matching the annual average. Would you mind attaching your pbix so I can try to figure out where I am going wrong?

[tgjones43]

OK now I understand the role of the date table, and the measure is now working for me. For anyone viewing this thread, this explains how to create a date table and why it is important in this scenario:

How to Create Date Tables in Power BI Tutorial | DataCamp