# How to get the average of the last 3 month from the same line item for Forecast version?

Options

Hi,

I am doing an OpEx forecast. How do I write a forumula to get the average of the past 3 month from the same line item for the Forecast Version only? I also want to be able to override the amount manually or by upload if needed. Is this possible or do I have to create another module to link it?

Tagged:

## Best Answer

• Answer ✓
Options

Sure!
Try this in the 3 months average: IF MOVINGSUM(Upload Amount, -2, 0, AVERAGE) = 0 THEN POST('3 Months Average', 1) ELSE MOVINGSUM(Upload Amount, -2, 0, AVERAGE)

## Answers

• Options

Hi,

You cannot have a formula and a data entry line item simultaneously. So I would suggest creating a line item called '3 month average' with the following formula; this is to get the average of the last three months:

MOVINGSUM(Amount, -2, 0, AVERAGE)

And then create a second line item called 'Override' which is the data entry line item. Finally, create a third line item called 'Final average forecast' with the formula: IF Override <> 0 THEN Override ELSE '3 Month Average'

Regards,

Anirudh

• Options

Thank you. It almost work but the moving sum formula ended in June since the last 3 months trail off. Any idea on how to push the forumula until the end of the year or to what ever time length I want without me adding the amount manually?

• Options

Thank you so much. It worked. I have one last question.

Is there any way to apply to different formula to the same line item? For example, on my Amount line item, its the final amount i want to show. I want the number to be exactly the same and not calculated for my actual and the first 3 month. I tried using switch over but it still calculate my actual when I switch to forecast.

• Options

Yes

there is a little known technique that works for numeric values

You create a line item subset and then you can specify a different formula by version

The details can be found here:

https://help.anaplan.com/anapedia/Content/Modeling/Build%20Models/Add_Version_Formula.htm?

I hope that helps

David

• Options

Thank you for pointing me in the right direction. I have another question, do I need to create another module to bring the final amount in? For Example, I created a Line item subset call Final Amount. I tried to make it equal Upload Amount in the Actual but I keep getting an error.

Do I need to create another module to do the calculation?