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?