Average of prior months

[ryan14]

I have a detail which shows cities in the rows and months in the columns with sales for each location for each month. I would like to set up a 3 month average based on the month we are currently in, and give the file the ability to move forward automatically based on the month were in. I understand how to manually input an average, but am unable to figure out a solution to have it move forward each month when we change months.

[ryan14]

Here is an example of what im working with... we are now in P6 so i would like an average of P3/P4/P5. but once we get to P7 P6 will be filled in via the input file, and then P7 would need to average P4/P5/P6.

Sales	P1	P2	P3	P4	P5
florida	100	104	108	112	116
georgia	101	105	109	113	117
NC	102	106	110	114	118
SC	103	107	111	115	119

[kelsey_kincaid · Accepted solution]

Hi @ryan14 ,

A transpose tool would work well for this.  You can then sample the last 3 records (grouped by city), compute the average and then combine it back with the original data. I've mocked up a workflow below - let me know if this works for you or if you have additional questions.