Power BI DAX - Calculate CAGR and Projection values

Question

Hi there,Greetings!I need to calculate Calculate CAGR, Projection and Target values for next few years using DAXI have below data with me.FY Year Baseline Target Growth Rate Target by 2025&nbsp; #Year&nbsp; &nbsp;Year End (Actual)2021&nbsp; &nbsp; &nbsp;6.60%&nbsp; &nbsp; 20.00%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7.92%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6.60%2022&nbsp; &nbsp; &nbsp;6.60%&nbsp; &nbsp; 20.00%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7.92%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7.13%2023&nbsp; &nbsp; &nbsp;6.60%&nbsp; &nbsp; 20.00%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7.92%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7.45%2024&nbsp; &nbsp; &nbsp;6.60%&nbsp; &nbsp; 20.00%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7.92%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7.90%2025&nbsp; &nbsp; &nbsp;6.60%&nbsp; &nbsp; 20.00%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7.92%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1in result (calculated in Excel) I have below table along with CAGR, Projection and Target.CAGR = (Target by 2025 / Year End (Actual)) ^ (1 / #Year) - 1Projection = Projection Base Value * (1 + CAGR ) ................................ (Projection base value from the previous year 7.13%)Target = Year End (Actual) * (1 + CAGR)&nbsp;FY Year Baseline Target Growth Rate Target by 2025 #Year Year End (Actual) CAGR&nbsp; Projection Target CY2021&nbsp; &nbsp; &nbsp;6.60%&nbsp; &nbsp; 20.00%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7.92%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; 6.60%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2022&nbsp; &nbsp; &nbsp;6.60%&nbsp; &nbsp; 20.00%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7.92%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; 7.13%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1.37%&nbsp; 7.13%&nbsp; &nbsp; &nbsp; &nbsp;7.13%2023&nbsp; &nbsp; &nbsp;6.60%&nbsp; &nbsp; 20.00%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7.92%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; 7.45%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3.56%&nbsp; 7.38%&nbsp; &nbsp; &nbsp; &nbsp;7.38%2024&nbsp; &nbsp; &nbsp;6.60%&nbsp; &nbsp; 20.00%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7.92%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; 7.90%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3.11%&nbsp; 7.65%&nbsp; &nbsp; &nbsp; &nbsp;7.68%2025&nbsp; &nbsp; &nbsp;6.60%&nbsp; &nbsp; 20.00%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7.92%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; 0.25%&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7.92%&nbsp; 7.92%&nbsp;Here the requirement is ifNeed to achieve(a) I'm selecting the year e.g. 2023 from slicer it should consider CAGR 3.56% ,projection for 2023 and subsequent year should get calculated considering CAGR 3.56% itself.(b) If I'm selecting the year e.g. 2024 from slicer it should consider CAGR 3.11% ,projection for 2024 and subsequent year should get calculated considering CAGR 3.11% itself. But for year 2023 it should remain same as per (a) and so on.(c) To calculate Target considering Year End (Actual) instead previous year projection Value. And no need to calculate the values for subsequent year only respective year's CAGR need to consider.I need to see these values on line chart for a selected year.Please guide me to create a DAX measure.Thanks!

anonymous · Accepted Answer

Hi&nbsp;PNA_123&nbsp;,
Please try to create measure with below dax formula:
CAGR =
VAR SelectedYear =
    MAX ( 'Table'[FY Year] )
VAR EndValue =
    CALCULATE ( SUM ( 'Table'[Target by 2025] ), 'Table'[FY Year] = SelectedYear )
VAR StartValue =
    CALCULATE (
        SUM ( 'Table'[Year End (Actual)] ),
        'Table'[FY Year] = SelectedYear
    )
VAR NumYears =
    CALCULATE ( SUM ( 'Table'[#Year] ), 'Table'[FY Year] = SelectedYear )
RETURN
    IF ( NumYears &lt;&gt; 0, ( EndValue / StartValue ) ^ ( 1 / NumYears ) - 1, BLANK () )
Projection =
VAR SelectedYear =
    MAX ( 'Table'[FY Year] )
VAR PreviousYear = SelectedYear - 1
VAR BaseValue =
    CALCULATE (
        SUM ( 'Table'[Year End (Actual)] ),
        'Table'[FY Year] = PreviousYear
    )
VAR CAGR = [CAGR]
RETURN
    IF ( NOT ISBLANK ( BaseValue ), BaseValue * ( 1 + CAGR ), BLANK () )
Target =
VAR SelectedYear =
    MAX ( 'Table'[FY Year] )
VAR StartValue =
    CALCULATE (
        SUM ( 'Table'[Year End (Actual)] ),
        'Table'[FY Year] = SelectedYear
    )
VAR CAGR = [CAGR]
RETURN
    IF ( NOT ISBLANK ( StartValue ), StartValue * ( 1 + CAGR ), BLANK () )

Please note that these measures assume that your data is structured in a way that the 'Table' refers to your data table and the columns are named as indicated in your example.
&nbsp;
To visualize these values on a line chart for a selected year, you can:
- Add a slicer to your report and connect it to the 'FY Year' field.- Create a line chart and add the 'FY Year' field to the Axis.- Add the measures for CAGR, Projection, and Target to the Values.
&nbsp;
Best regards,Community Support Team_Binbin YuIf this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

Forum Discussion

Power BI DAX - Calculate CAGR and Projection values

1 Reply

Related content

Calculate Projection

Projected Available Balance Dax Calculation Help

DAX for calculating retroactive values

Calculate blank projection values

Missing Project Name