Moving average over non-date values

[aslee]

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. 

[Phil_Seamark · Accepted solution]

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

[aslee]

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? 

[aslee]

@Phil_Seamark

Nevermind - worked it out! I took out the formatting section and it fixed it.

Thanks again!