model Variable and calculated account formula

[daniel_weisblut86]

Hi

Need your help pls with model Varibale and calculated account formula

I have number of passenger's for 2023 and number of planes is calculate (no. passengers / seat on plane)

this is the simple part

for 2024 - 2032 i want in each year the number of planes to be 3% above last year

I created a Variables parameter Base year

Now in the Number of Planes Account i want to write a formula to calculate
If Year = Base Year then calculate the Number of Planes

and If Year is Larger then Base Year Take the number of planes in the base year and calculate for each year increase by 3%

Appreciate You help How is the best way to define that

Thank you

[N1kh1l · Accepted solution]

daniel_weisblut

daniel_weisblutThis does not look as simple but I have tried to work it out. I would still advise you to check if you can use Data Action for this.

Account members

Formulas:

[calc_plane]=[No_of_pax]/[Seat_on_plane]

[calc_plane_baseyr]=LOOKUP([calc_plane] ,[d/Date]=['BaseYear'] )

[calc_plane_future]=ITERATE(IF([d/Date].[p/YEAR]>['BaseYear'] ,PRIOR(),[calc_plane_baseyr]/1.03) * 1.03 ,[calc_plane_baseyr],[d/Date])

[No_of_plane]=IF([d/Date].[p/YEAR]  >['BaseYear'],[calc_plane_future] ,
IF([d/Date].[p/YEAR] =['BaseYear'],[calc_plane_baseyr],0))

Below I am using as you did not say what happens to year prior to Base Yr, so I am keeping them as calc_plane=[No_of_pax]/[Seat_on_plane]

[No_of_plane_1]=IF([No_of_plane]=0 ,[calc_plane] ,[No_of_plane] )

Output:

Base Yr= 2023, 159 in 2023 and then 3% increment over years

Base Yr= 2024,172 in 2024 and then 3% increment over years, 159 in 2023

Base Yr= 2025,185 in 2024 and then 3% increment over years, 159 in 2023 nd 172 in 2024

Hope this helps !!

Please accept/upvote if this helps you

Br

Nikhil