How to Dynamically calculate Same month moving average in Anaplan

uditbhageria

Lets say I am in the month of July'23 I have to calculate moving average of past years(number of years could be dynamic) lets say 3 years for now, then the average would be of July 22, July 21, July 20.

alexpavel

In that case, it is needed some criteria to distinguish Actual Months from Forecast Months ( below an example with Version using Switcover Jul 23. The idea is to have for Actual months the fixed value (Value Input ACT) and the Forecast months calculated as moving averages.

Anaplan allows to reference the same Line-item in formulas without circular reference if you refer to previous months in functions like PREVIOUS, LAG, OFFSET and use the previous months' final results in the Current month calculation.

Below an example where the final result of Jul 23 (calculated as 4 year average) is taken into account when calculating Jul 24)

Hope it helps

Alex

durgaprasad

I think you have to define item(time) -24 formula for the line item consist of time as dimension and you have to use this line item in movingsum.

Sai_Bharadwaj_Venati

Dynamically i dont think it is possible using simple logics…U have to write If-else condition and take assumption b/w lower- higher end….

uditbhageria

The main problem here is even if I calculate using if and else for current year, I fail on future year(July'24) as it will give me circular reference

Sai_Bharadwaj_Venati

Agree…If-else condition will work only for 1 year…For 2nd Year it will not work… can we take following assumptions to get solution?

Historical data: - Min (1 year) - Max(4 year)

Forecast Periods: - Min (1 year) - Max (2 year)

If we want solution in this bound we can try something but if limits are not provided then i'm not sure how to get output

alexpavel

@uditbhageria I think there is no easy way to solve this really dynamically. As far as I know, there is no function to give you dynamically the same months of previous years multiple times and the only way to reference the same month of previous years is to use functions like LAG or OFFSET.

The issue is that LAG and OFFSET can reference only 1 value at the same time as there is no LOOP functionality to be applied internally to a formula and refer dynamically to multiple months.

To simulate a LOOP it is needed to add to the module a dummy list with elements like 1,2,3..etc.

Without using an additional list, the only way I found to solve your request is to have this approach:

• You need to have defined a max of the nr. of years that you can go back in time (I choose to be 4 years in the below formula)
• You can create a formula like below using IF statements to check if the previous years value needs to be part of the Average or not

(LAG(Value Input, 48, 0) * (    IF        'Nr. Years' >= 4    THEN        1    ELSE        0) + LAG(Value Input, 36, 0) * (    IF        'Nr. Years' >= 3    THEN        1    ELSE        0) + LAG(Value Input, 24, 0) * (    IF        'Nr. Years' >= 2    THEN        1    ELSE        0) + LAG(Value Input, 12, 0) *    IF        'Nr. Years' >= 1    THEN        1    ELSE        0) / 'Nr. Years'

Hope it helps

Alex

uditbhageria

Hey @alexpavel, Thank you for the solution,

I need to calculate for July 24, in the same line.

uditbhageria

THis is helpful, Thanks