Forum Discussion
3 year rolling average required
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!
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))
5 Replies
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))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?
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:
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!
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.
