How to create a loop formula
Hi everyone,
I am stuck on a formula.
I have demands for 2021 and I need to calculate the demand forecast until 2030 by using the formula below
Demand 2022= Demand 2021*Growth rate 2022
Demand 2023= Demand 2022*Growth rate 2023
.
.
.
So how can I do that?
Thank you!
Best Answers

Hi @TahaU
You seem to be asking really good questions. I enjoyed the discussion on the last question as well.
While there is no default way to create a Loop function, the ask here seems to be simpler. I will not get into the details of dimensionality, I know you have a good grasp of that already. What you essentially need to do is bring previous year's value to current year (or year in consideration) and multiple it with the growth rate of current year (or year in consideration).
 If there is a time dimension, a simple PREVIOUS should fetch previous year's demand to current
 A bad way (just for discussion) is to create a system module that formulaically maps previous year to current, and then do a lookup basis this
What would be helpful is to look at functions that move values across time  LAG/OFFSET/LEAD/POST etc.
Let me know if this helps
1 
Hi @TahaU ,
Ah, I see  sorry for oversimplifying it. In that case, I would echo @ankit_cheeni's suggestion of using a different function. I think LAG would do the trick here. In fact, it may even help simplify the formula. In this case, my example formula would instead read: LAG(Demand Calc,12,Demand) * Growth Rate
That should return the same month previous year's demand. In the case of your first year, it will instead just take that month's demand * that month's growth rate. Let me know if that solution works!
Thanks,Timmy
2
Comments

Hi @TahaU ,
There are likely quite a few ways to accomplish this. Here's how I would do it:
 If you don't already have a Time Settings module set up, create one (dimensionalized by time only). Create a simple formula to find your First Period which returns a boolean: CURRENTPERIODSTART() = START(). This will be set up as a Year time scale.
 Create a new line item in your module called something like "Demand Calc." The formula would be: IF NOT Time Settings.First Period? THEN PREVIOUS(Demand Calc) * Growth Rate ELSE Demand
Hope that helps  let me know if additional help is needed!
Thanks,Timmy
1 
Hi @TahaU ,
For, Creating a Loop Formula  an If/Else loop is a pretty straightforward approach provided your conditions are well defined and not too many as this may make the model slow.
Over here in this particular case, you can 
 Create a System module to separate time between Actual/Current & Forecast using a formula, refer <
Time & Date Functions  it will give you better clarity> to use it as a condition for formula application.
 Make a Calculation Module keeping Time (Model Calendar) in Column & 3 Line Items
 Previous Period Growth – for the first period in the calendar (actual) – it’d be zero / for the second period onwards (Current & Forecast) grab it from “Forecast Growth” of the previous period. (IFELSE with OFFSET can be used)
 Expected Growth Rate – for the first period in the calendar (actual) – it’d be zero / for the second period onwards (Current & Forecast) update through a UX /formula or Import, depending on your Use Case.
 Forecast Growth – for the first period in the calendar (actual) – it’d be the actual value you received/ for the second period onwards (Current & Forecast) grab drive it through the formula “Previous Period Growth” * “Expected Growth Rate”.
Hope this helps,
Kind Regards,
Adwait
1  Create a System module to separate time between Actual/Current & Forecast using a formula, refer <

Hi @TimothyThomas,
Thank you for your reply. It is a good idea.
I have already tried something similar to your answer. My problem is that I need to calculate the monthly demand forecast and when I use the PREVIOUS function it refers previous month, not the same month last year.
I couldn't figure out how to deal with it.
Demand 2023 Jan = Demand 2022 Jan * Annual Growth Rate 2023
Taha
1 
Hi @ankit_cheeni,
Thank you for your great answers each time I ask a question!
I was able to do it with the OFFSET function.
IF NOT 'SYS00 Time Settings bu Month'.Current Year? THEN OFFSET('Demand Forecast', 12, 0) * (1 + Demand Growth Rate) ELSE Demand 2021
In this solution, I had to use 2 line items. Demand 2021 and Demand Forecast. Is there a better solution?
2 
I think having two line items is fine. Honestly in my experience, breaking calculations in multiple line items is never a problem until it affects your space usage.1