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

    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'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!