Forum Discussion

tgjones43's avatar
tgjones43
Helper IV
3 years ago
Solved

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...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    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))

     

amitchandak's avatar
amitchandak
Super User
3 years ago

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's avatar
tgjones43
Helper IV
3 years ago

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!