3 months average of historical sales the year before

riyazpasha9
riyazpasha9
in Modeling

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

 

Tagged:

Best Answer

  • riyazpasha9
    riyazpasha9
    Answer ✓

    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

Answers

  • ChrisAHeathcote
    ChrisAHeathcote

    @riyazpasha9 

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

  • riyazpasha9
    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

    riyazpasha9_0-1638085305391.png

     

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

     

    Thank you

     

    Regards,

    Riyaz Pasha

  • ChrisAHeathcote
    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
    LizySH

    this is difficult for me

  • ChrisAHeathcote
    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

Categories