Moving Average Issue

Hi,

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)

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

THEN

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

Accepted answers

All comments

AkhilEmmanual

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

Screenshot 2023-06-07 10.59.33 AM.png

Screenshot 2023-06-07 10.58.06 AM.png

i have actual data from 2021-2022 and forecast 2023

Screenshot 2023-06-07 11.00.29 AM.png

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

Screenshot 2023-06-07 11.07.01 AM.png

Screenshot 2023-06-07 11.07.24 AM.png

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?

AkhilEmmanual

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

AkhilEmmanual

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

Quick Links

Can you help?

Unanswered questions

Polaris workaround for FIRSTNONBLANK
Polaris doesn't support FIRSTNONBLANK as a function parameter. I have a solution workaround for this already but not too happy with it. So wanted to know what solution workaround people have done to get around this limitation
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385