Calculate compound rate

[SariPari]

Hi - I have the issue where I need to calculate the expense Increase /Decrease based on the Rates and the year.

I have the solution using variables but that is not the ideal way of doing this for sure.This method is messing the performace as I have almost 3000 Address with different Construction years.I have the working app attached.

I have data as below and the required calculation is in the screenshot (Column named Calculation)

Ex:

2020 is $40
2019 = 2020's value - % change of 2019 = 40 - (40 * 5.5) = 37.80
2018 = 2019's value - % change of 2018 = 37.80 - (37.80 * 5.6) = 35.70
2017 = 2019's value - % change of 2017 = 35.70 - (35.70 * 5.0) = 33.92
so on until 2010

for year 2021 onwards it will add the % change

2021 = 2020's value + % change of 2021 = 40 - (40 * 1.9) = 40.76

2022 = 2021's value + % change of 2022 = 40.76 - (40.76)= 44.02

so on until 2023.

Any help is much appreciated.

@theoat  @henrikalmen @stevedark @marcus_sommer @sunny_talwar @swuehl @hic @Gysbert_Wassenaar @jagan 

Thank you so much!

[Anil_Babu_Samineni]

@SariPari I still don't understand the needs?

If I look above screenshot for both First and Second measure, It seems to be fine. When you do multiply both, what is the expected result? 

[SariPari]

Please see the below required calculation.

[SariPari]

@Anil_Babu_Samineni @Chanty4u - is this doable ? I have the calculation in the screenshot.Let me know if anything else is needed.Thank you.

[Anil_Babu_Samineni]

@SariPari Like we said, We will try our best once we understand the needs. Anyway, Can we know what are these numbers? Example, 27.27?

[SariPari]

@Anil_Babu_Samineni please see below.Than you!

Those numbers are the results from previous year.

Ex:

2020 is $40
2019 = 2020's value - % change of 2019 = 40 - (40 * 5.5) = 37.80
2018 = 2019's value - % change of 2018 = 37.80 - (37.80 * 5.6) = 35.70
2017 = 2019's value - % change of 2017 = 35.70 - (35.70 * 5.0) = 33.92
so on until 2010

for year 2021 onwards it will add the % change

2021 = 2020's value + % change of 2021 = 40 - (40 * 1.9) = 40.76

2022 = 2021's value + % change of 2022 = 40.76 - (40.76 * 😎 = 44.02

so on until 2023.

Thank you!

[Anil_Babu_Samineni]

@SariPari Perhaps this way?

If(Scenario_Year=2020, Sum(Sales),If(Scenario_Year<2020,Num(Below(Sum(Sales)-
(Sum(Sales)*(Rates)/100), 1,1) * (pow((1+(Rates)),ScenarioYear_CY_Delta))/100, '#.#0%'),
Num(Above(Sum(Sales)-(Sum(Sales)*(Rates)/100), 1,1) * (pow((1+(Rates)),ScenarioYear_CY_Delta))/100, '#.#0%')))

[SariPari]

@Anil_Babu_Samineni It is not giving the output

[Anil_Babu_Samineni]

@SariPari PFA.

[SariPari]

@Anil_Babu_Samineni  - Thank you so much for trying.However, this gave the same output as my old logic.

[SariPari]

@Anil_Babu_Samineni - could you please let me know if this is doable at all ?