Forum Discussion

Chanleakna123's avatar
Chanleakna123
Icon for Post Prodigy rankPost Prodigy
4 years ago
Solved

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

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

Show and Tell
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Chanleakna123
      Icon for Post Prodigy rankPost Prodigy
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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.

         

         

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.