Projected Growth based on previous two years & Displayed on Chart

Hi Creative_tree88 ,

Great question and thanks for providing detailed requirements and sample data. Let’s break down a robust solution that projects growth using the midpoint of the growth rates from the previous two years, then displays both the historical and projected results in a chart and a table in Power BI.

Step-by-step Solution:

1. Calculate Yearly Growth Rates:

   • For each year (from Year 3 onward), calculate the yearly growth rate based on the previous year’s values.

2. Calculate the Midpoint %:

   • For projection, take the average (midpoint) of the previous two years’ growth rates.

3. Project Future Years:

   • Start from your latest actual year.
   • For each projection year, apply the midpoint % calculated from the last two actual/projected years’ growth rates to generate the new year’s value.
   • Repeat this for as many years as you want to project.

4. DAX Implementation in Power BI:

   • Assume your table is called CustomerNumbers with columns [Year], [Value].
   • Create a calculated column for growth rate:
     DAX
      

     Growth Rate = 
     VAR PrevYear = CALCULATE(MAX([Value]), FILTER(CustomerNumbers, [Year] = EARLIER([Year])-1))
     RETURN IF(ISBLANK(PrevYear), BLANK(), ([Value] - PrevYear) / PrevYear)

   • Calculate the midpoint for projection:
     DAX
      

     Midpoint % = 
     VAR PrevGrowth = CALCULATE([Growth Rate], FILTER(CustomerNumbers, [Year] = EARLIER([Year])-1))
     VAR PrevPrevGrowth = CALCULATE([Growth Rate], FILTER(CustomerNumbers, [Year] = EARLIER([Year])-2))
     RETURN IF(ISBLANK(PrevGrowth) || ISBLANK(PrevPrevGrowth), BLANK(), (PrevGrowth + PrevPrevGrowth) / 2)

   • To project future years, you can use a calculated table that appends projected years and uses the last two actual/projected growth rates to calculate each new year recursively.

5. Visualization:

   • Combine historical and projected data into a single table.
   • Use a Line and Clustered Column Chart to display actuals and projections.
   • Use a table visual to display the year-by-year numbers and growth rates.

Sample Calculated Table for Projection:

ProjectedData =
VAR LastActualYear = MAX(CustomerNumbers[Year])
VAR LastActualValue = CALCULATE(MAX(CustomerNumbers[Value]), CustomerNumbers[Year] = LastActualYear)
VAR Growth1 = CALCULATE([Growth Rate], CustomerNumbers[Year] = LastActualYear)
VAR Growth2 = CALCULATE([Growth Rate], CustomerNumbers[Year] = LastActualYear - 1)
VAR Midpoint = (Growth1 + Growth2) / 2
VAR NumProjYears = 5 // set how many years to project

RETURN
UNION(
    SELECTCOLUMNS(CustomerNumbers, "Year", [Year], "Value", [Value], "Type", "Actual"),
    ADDCOLUMNS(
        GENERATESERIES(LastActualYear+1, LastActualYear+NumProjYears, 1),
        "Value", 
            VAR PrevValue = IF([Value] = BLANK(), LastActualValue, [Value])
            RETURN PrevValue * (1 + Midpoint),
        "Type", "Projected"
    )
)

You may need to adjust the recursion for more than one projected year, but this gives a foundation.

If you want, I can help with a Power Query version or a more advanced DAX approach for multi-year projections. Just let me know your preferred method or if you want a .pbix example!

Let me know if this solves your problem or if you need further customization!
translation and formatting supported by AI