Dynamic Calculation (Need Help!!)

[thekimberlytan]

Is this possible to be done in powerbi? 

Example in forecasting:

In 2020 I have 50,  dashboard filter 5% to the number for the following forecast years. 

Start of forcasting and the expected result: 

in

2020: 50
2021 : 50-(50*0.05) = 48

2022: 48 - (48*0.05) = 46

2023: 46 - (46*0.05) = 44

.

.

2025

[v-yalanwu-msft · Accepted solution]

Hi @thekimberlytan  ,

According to your description, you could create what-if parameter, then create measure (based on the what-if parameter); the following formula to create a measure :
Step 1: Add what-if parameter(Percentage)

Step2: Add measure(Value)

Value =
VAR _a =
    MAX ( 'Table'[Year] ) - 2020
RETURN
    CALCULATE (
        MAX ( 'Table'[Sales] ) * ( 1 - [Percentage Value] ) ^ _a,
        ALL ( 'Table' )
    )

The final output is shown below:

Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft · Accepted solution]

Hi @thekimberlytan  ,

According to your description, you could create what-if parameter, then create measure (based on the what-if parameter); the following formula to create a measure :
Step 1: Add what-if parameter(Percentage)

Step2: Add measure(Value)

Value =
VAR _a =
    MAX ( 'Table'[Year] ) - 2020
RETURN
    CALCULATE (
        MAX ( 'Table'[Sales] ) * ( 1 - [Percentage Value] ) ^ _a,
        ALL ( 'Table' )
    )

The final output is shown below:

Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[thekimberlytan]

Hi @v-yalanwu-msft, thanks for your solution!!

what if the scenario is like - i have 2 what-if parameters, attrition 5% and recruitment 3%

2020:52
2021: 52- (52*5%)+(52*3%) = 51
2022: 51-(51*5%)+(51*3%) = N
20233:N-(N*5%)+(51*3%)=N
.
.

[v-yalanwu-msft · Accepted solution]

You could modify the measure by the following：

value =
VAR _a =
    MAX ( 'Table'[Year] ) - 2020
RETURN
    CALCULATE (
        MAX ( 'Table'[Sales] ) * ( 1 - [Percentage Value] + [scenario Value] ) ^ _a,
        ALL ( 'Table' )
    )

The final output is shown below:

Best Regards,
Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[thekimberlytan]

Hi @CNENFRNL, thanks for your input. 

My Question - if the 5% is based on the what-if parameter, can it still be populate using the script you share? 

[CNENFRNL]

@thekimberlytan , simply an algbra question,

f(n)=f(n-1)*(1-0.05)=f(n-2)*(1-0.05)^2= ... =f(0)*0.95^n

let
    Source = Table.FromList({2020..2025}, Splitter.SplitByNothing(), {"Yr"}),
    Yr2020 = 50,
    #"Added Custom" = Table.AddColumn(Source, "Forecast_PQ", each Number.RoundUp(Yr2020 * Number.Power(.95, [Yr]-2020)))
in
    #"Added Custom"