Simulation based on simulated base-Value with %Growth Rate on Parameter

[Chanleakna123]

Dear All , 

I have a Parameter to play on %Growth from 2023 to 2026 and this will link to the Total All UC measure I have built down below. 

I have done the formula as Forecast 12 but the result is not shown as expected. It's still using the base value from Total All UC in the calculation, while I don't need this. 

Forecast 12 =

VAR __yr = MAX('445 Calendar'[Year])

RETURN

PRODUCTX(

CALCULATETABLE('445 Calendar', '445 Calendar'[Year] <= __yr ),

CALCULATE([Selection 2 Simulation] )

) * [Total All UC]+[Total All UC]

My expectation is to see the base value will be calculated based on the %Growth Simulation we had. 

For example :

2022 : 38.6M 

2023 : (38.6*13%)+38.6=43.6

2024 :(43.6*0%)+43.6=43.6

2025 :(43.6*19%)+43.6=51.9

2026 :(51.9*-18%)+51.9=42.5

[Anonymous]

Hi @Chanleakna123 ,

I have tried my best to calculate the result. And I modify my answer. Please check my pbix file to see if it helps you.

Best Regards

Community Support Team _ Polly

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

[Anonymous]

Hi @Chanleakna123 ,

power bi does not support dax formulas to do recursive calculation based formula its previous calculation result with current values.

Perhaps you can take a look at the following blog to use EXP and LN functions to calculate out current rate to achieve your requirement:

Recursive Calculations in PowerPivot using DAX

Best Regards

Community Support Team _ Polly

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

[Chanleakna123]

Hi @Anonymous  , is that possible that u can provide the sample ? 
because I went through it and not sure where to start. 
Sorry .

might need ur sample working on this so that I can work. 
thx for ur help

[Anonymous · Accepted solution]

Hi @Chanleakna123 ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _rs =
    SUMMARIZE (
        FILTER ( ALL ( 'Table' ), 'Table'[year] <= MAX ( 'Table'[year] ) ),
        'Table'[year],
        "bilv", [selection] + 1
    )
VAR _minyear =
    MINX ( ALL ( 'Table' ), 'Table'[year] )
VAR _minvalue =
    CALCULATE (
        'Table'[total],
        FILTER ( ALL ( 'Table' ), 'Table'[year] = _minyear )
    )
RETURN
    PRODUCTX ( _rs, [bilv] ) * _minvalue

Best Regards

Community Support Team _ Polly

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