3 months average of historical sales the year before

riyazpasha9
Occasional Contributor

3 months average of historical sales the year before

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_0-1637867340180.png

riyazpasha9_1-1637867398637.png

 

riyazpasha9_2-1637867469722.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
riyazpasha9
Occasional Contributor

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

View solution in original post

6 REPLIES 6
ChrisAHeathcote
Community Boss

@riyazpasha9 

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

Chris
HeathcoteAndHerran.com
riyazpasha9
Occasional Contributor

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

riyazpasha9_0-1638085305391.png

 

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

 

Thank you

 

Regards,

Riyaz Pasha

ChrisAHeathcote
Community Boss

@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.

Chris
HeathcoteAndHerran.com
riyazpasha9
Occasional Contributor

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
New Contributor

this is difficult for me

ChrisAHeathcote
Community Boss

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

Chris
HeathcoteAndHerran.com