Forum Discussion

Anonymous

Not applicable

8 years ago

Solved

Calculate average across rolling previous 4 rows

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.

Tips and Tricks

Perhaps:

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

4 Replies

Greg_Deckler
Community Champion
8 years ago
Perhaps:

Column = AVERAGEX(FILTER(ALL(Table12),[Year Week Sort]>=EARLIER([Year Week Sort])-3),[CountRef])
- Anonymous
  Not applicable
  8 years ago
  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
- Anonymous
  Not applicable
  8 years ago
  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
    New Member
    7 years ago
    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?