Calculate average across rolling previous 4 rows

[Anonymous]

Hi,

I have a table containing the value count of rows over a grouped year/week column. I have sorted the data by year/week ascending:

CountRef | Year Week Sort

6                201831

4                201832

9                201833

13              201834

17              201835

7                201836

15              201837

21              201838

I need to calculate the average value across the rolling previous 4 rows. For example, the average across: 21+15+7+17, then 13+9+4+6 and so on....

Any help appreciated.

[Greg_Deckler · Accepted solution]

Perhaps:

Column = AVERAGEX(FILTER(ALL(Table12),[Year Week Sort]>=EARLIER([Year Week Sort])-3),[CountRef])

[Anonymous]

This works for me:

Column = AVERAGEX(FILTER(ALL(Table12),([Year Week Sort]>=EARLIER([Year Week Sort])-3) && [Year Week Sort] <= EARLIER([Year Week Sort])-0),[CountRefs])

Thanks!

[JayN]

I have a requirement somewhat similar to this...I need to find the latest running average velocity for the last 3 sprints. 

This is the error I get when I try to use the same formular as listed to at least start calculating the averages.

Any idea what I could be doing wrong?

[Anonymous]

Thanks for your reply.

 This seems to work for the first average but the averages following this aren't correct. The output I expect is:

CountRef | Year Week Sort | Avg

6                201831               

4                201831

9                201831

13              201831

17              201831                  10.75

7                201831                  11.5

15              201831                  13

21              201831                  15  

Thanks