Another Circular Reference Question

Options
jonathan_fogden
jonathan_fogden Member, ALL USERS, Community Member Posts: 5 Master Anaplanner of the Year
in Planning & Modeling

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?

Tagged:

Comments

  • jonathan_fogden
    jonathan_fogden Member, ALL USERS, Community Member Posts: 5 Master Anaplanner of the Year

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

Categories