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 

 

 

riyazpasha9

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

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.

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