Forum Discussion
Moving average over non-date values
Hi All,
I need to calculate a moving average over time, however the time is set as numerical iterations, not dates.
The average needs to be over the 2 iterations - for example, the current iteration being 5, the calculation needs to take the average cycle time of this iteration and previous iteration 4. Then I also need the average of iteration 4 and previous iteration 3, and so on and so forth moving back in time. I eventually want a line chart tracking this moving average as we move forward in iterations.
Some sample data:
| Iteration | Cycle Time | id |
| 1 | 20 | cc-1 |
| 1 | 17 | cc-2 |
| 1 | 22 | cc-3 |
| 2 | 23 | cc-4 |
| 2 | 5 | cc-5 |
| 2 | 15 | cc-6 |
| 2 | 10 | cc-7 |
| 2 | 11 | cc-8 |
| 2 | 18 | cc-9 |
| 3 | 21 | cc-10 |
| 4 | 20 | cc-11 |
| 4 | 14 | cc-12 |
| 4 | 25 | cc-13 |
| 5 | 9 | cc-14 |
| 5 | 11 | cc-15 |
| 5 | 12 | cc-16 |
| 5 | 27 | cc-17 |
| 5 | 20 | cc-18 |
The resulting chart data should be similar to the below:
| Iteration | Avg |
| 5 | 17.3 |
| 4 | 20.0 |
| 3 | 14.7 |
| 2 | 15.7 |
Can anyone assist? I have tried adapting a previous question of mine HERE but this code won't adapt to when I change sum to average.
Hi aslee
Please try the following calculated measure but change Table4 to the name of your table.
Avg = FORMAT( CALCULATE( AVERAGE('Table4'[Cycle Time]), FILTER(ALL('Table4'), 'Table4'[Iteration]>=MAX(Table4[Iteration])-1 && 'Table4'[Iteration]<=MAX('Table4'[Iteration]) ) ), "##.0")
3 Replies
Hi aslee
Please try the following calculated measure but change Table4 to the name of your table.
Avg = FORMAT( CALCULATE( AVERAGE('Table4'[Cycle Time]), FILTER(ALL('Table4'), 'Table4'[Iteration]>=MAX(Table4[Iteration])-1 && 'Table4'[Iteration]<=MAX('Table4'[Iteration]) ) ), "##.0")Thank you Phil_Seamark! This has worked and looks great in a table - but for some reason I get this error when I go to put it in a line chart
My x-axis is Iteration, which is set to numerical integer format.
Anyone have any ideas what this means?
Nevermind - worked it out! I took out the formatting section and it fixed it.
Thanks again!
