3 months average of historical sales the year before

[riyazpasha9]

Hi,

I need to calculate the three months average based on the historical sales data the year before.

For ex: Jan 20 Feb 20 Mar 20 - Average values should go under Jan 21.

100,0,100 - 66.7 Jan 21 value

Please could you suggest the optimal solution for the same? I have attached the screenshots for reference 

[ChrisAHeathcote]

@riyazpasha9 

Try using MOVINGSUM setting the start period as -12, end period -9 and AVERAGE keyword. 

[riyazpasha9]

Hi @ChrisAHeathcote ,

I think the end period instead of -9 should be -10 then values would be right I guess. The test forecast is the line item that has an end period as -9 and the test line item has -10 as end period

Also if you could help me understand the behaviour of the end period. 

Thank you

Regards,

Riyaz Pasha

[ChrisAHeathcote]

@riyazpasha9 

The start and end dates refer to the relative difference from the time period from which to calculate the aggregation.

-12 would be 12 time periods offset and -9 9 time periods.

[riyazpasha9 · Accepted solution]

Hi @ChrisAHeathcote  ,

I got the logic. 12-9 = 3 which gives the average for 3 months including the current period(Jan 21 value - Jan 21, Jan20, Feb20,) 

Thank you so much, Chris

Regards,

Riyaz Pasha

[LizySH]

this is difficult for me

[ChrisAHeathcote]

So the value for Jan 21 would include Jan20, Feb20 and Mar20.
If you found my comment answered your question can you please mark it as the solution.

Thanks