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.

  1. for 2021 Values line item
  2. for 2022 Values line item
  3. Same Period Last Yr = (2021 Values line item + 2022 Values line item) / 2
  4. Input line item = 30
  5. Previous 12 Month Average = Previous(Average 12 months)
  6. Average 12 months = Movingsum(Data, -12,-1,Average)

The calculation:

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.



  • Hi @innocent you can achieve this by taking previous year and 2 years back data by offset then you can take the average of both and if its actual take actual data else this average value for your reference you can check the example below.

    First I made a time settings module to identify actual and forecast

    i have actual data from 2021-2022 and forecast 2023

    then in main module i had created some line items

    Data for actuals- actual data

    Previous year no - helps for doing offset to get previous year data

    2 year before no -helps for doing offset to get 2 year before data

    previous year data- where we got previous year data

    2 year before data- where we got 2 year before data

    average- average of above 2

    forecast- if actual will take it from data for actual else the average

    I hope this screenshots will be helpful for you.

  • Monpero

    Thanks a lot @AkhilEmmanual for the build, with the formula (YEAR(ITEM(Time)) - 2024) * 12 to get the Previous year no which is used for forecast 2023.

    I know this is a silly question, if the forecast is from 2023,2024 and 2025 is this formula (YEAR(ITEM(Time)) - 2024) * 12 valid

    or will it be (YEAR(ITEM(Time)) - 2026) * 12 and the (YEAR(ITEM(Time)) - 2026) * 24 for 2 years before no?

  • @innocent if forecast starts from 23 till any year formula will be ((YEAR(ITEM(TIME))-2024)*12. If forecast starts from 24 then just change it to -2025 instead of -2024. Just one year greater than forecast start year.

    Here it will function like for 2023 average of 2021 and 2022 will come. for 2024 average of 2023 and 2022 will come and so on.

  • Monpero

    Hi @AkhilEmmanual thanks I really appreciate your effort. The formula does not provide value for 2024, I'm thinking it's subtracting it out. Is there a different calculation to bring numbers in for 2024.

  • @innocent Can you try to Subtract 2022 instead of 2024 and instead of multiplying with 12 multiply with -12

  • Monpero

    Thanks @AkhilEmmanual very much for your help, I really do appreciate it.

    The formula was okay but it didn't produce the right result. Thank you once again for finding time to assist, I sincerely appreciate it.