Moving Average Issue
For my Forecast Jan 2023 I want to bring the (Actuals) AVERAGE of Jan 2021 and Jan 2022. And for my Feb 2023 I want the AVERAGE of Feb 2021 and Feb 2022, e.t.c.
To achieve this I split the line item actuals values into the ones below, for calculation and then bring it to the forecast.
- for 2021 Values line item
- for 2022 Values line item
- Same Period Last Yr = (2021 Values line item + 2022 Values line item) / 2
- Input line item = 30
- Previous 12 Month Average = Previous(Average 12 months)
- Average 12 months = Movingsum(Data, -12,-1,Average)
IF Actuals THEN Data
ELSE Same Period Last Yr > Previous 12 Month Average + Input line item
Same Period Last Yr < Previous 12 Month Average - Input line item
Previous 12 Month Average
After doing this I don't get value for the forecast and the values are not correct. Please is there a better way to bring the Average Value of 2021 and 2022 of equal months to forecast months.
Any solution or suggestion will be really helpful.
EXAMPLE SCREEN SHOT