Another Circular Reference Question

Highlighted
Occasional Contributor

Another Circular Reference Question

I am building a Cost Forecasting Model which uses different methods for calculating the forecasted amount depending on the cost category and the number of years into the future. One of the latest use cases is to be able to taken the calculated cost from the previous year, increase it by inflation and divide by 12 to give the expected monthly amount for this current year. My problem is getting the total for the previous year, which itself is a product of the calculation. Lookup on year gives a circular reference, so I ended up using "Lag(amount, month no,0,STRICT) + Lag(amount, month no +1 ,0,STRICT)" etc out to +12 where month no is the number of the current month. This effectively fetches the monthly total for each month in the previous year and adds them together. This works but seems overly complex. Is there a better way?

5 REPLIES 5
Highlighted
Community Boss

Re: Another Circular Reference Question

PREVIOUS(YEARVALUE(line item)) should get you what you want

Nathan Rudman, Anaplan Model Builder
Highlighted
Master Anaplanner/Community Boss

Re: Another Circular Reference Question

Hi Nathan,

I think PREVIOUS on a month dimensioned line item will return the previous month value and not the previous year value as required in this case, even though YEARVALUE is wrapped in PREVIOUS
Highlighted
Community Boss

Re: Another Circular Reference Question

indeed, answered too fast.
LAG should be used

Nathan Rudman, Anaplan Model Builder
Highlighted
Master Anaplanner/Community Boss

Re: Another Circular Reference Question

Hi Jonathan,

 

Not sure if I fully understood the question

But if you're trying to fetch previous year's data, then apply an inflation factor and divide it by 12, all in the same line item which contains the source data, try this formula.

 

POST(YEARVALUE(Amount), 12) * 1.05 / 12

 

where amount is the line item containing the source data and also where this formula is being applied

Change 1.05 to the actual inflation factor

 

Screenshot where I put 100 for each month in 2020 and calculation for months 2021 and onwards

anirudh_0-1594289212551.png

 

 

Let me know if this was useful

 

Regards,

anirudh

Highlighted
Occasional Contributor

Re: Another Circular Reference Question

Great, that worked. Thanks for the swift and accurate response