Help calculating compounding growth rates in DAX

[ThomasSan]

Hi everyone,

I am looking for a DAX expression that allows me to calculate compound growth rates. More specifically, I would like to use the average per capita consumption of the last three years and extrapolate this consumption figure according to the population growth rate and a assumed annual growth rate of 4 percent. 

Currently, I have the following expression:

Sales Projection = 
var baseline =
CALCULATE(
    [Sales per Capita],
    DATESINPERIOD(
        'Date'[Date],
        2022,
        -3,
        YEAR
    )
)
/3

var extrapolation =
baseline * (1.04)

return extrapolation  

As you can see, I am able to calculate the average of the last three years and also include a one time growth rate of 4 percent. Now, what I am missing here is that with each additional year another 4 percent growth. Mathematically speaking, I am having problems getting my current formula "baseline * (1.04)" to "baseline * (1.04)^x" where x is the amount of years after my starting year 2022. I am looking for something akin to "year of the column here" minus 2022 in order to get the difference.

Can anyone therefore please help me with this formula? You are also welcome posting another solution in order to get this compounding growth rate included in the consumption per capita per future year.

Thanks!

[ThomasSan · Accepted solution]

After some intense googleing, I finally found the right code. I applied the FV function for it. 

Projected Sales per Capita = 

var yearssince = 
MIN('Date'[Date].[Year]) - 2022

var totalsalesinperiod=
CALCULATE(
    SUM(TotalSales[Sales]),
    ALL('Date'[Date]),'Date'[Date].[Year] >=2020, 'Date'[Date].[Year] <2023
)

var totalpopulationinperiod =
CALCULATE(
    SUM('Population Data'[Population]),
    ALL('Date'[Date]),'Date'[Date].[Year] >=2020, 'Date'[Date].[Year] <2023
)

var baseline =
DIVIDE(
    totalsalesinperiod,
    totalpopulationinperiod,
    BLANK()
)

var runningvalue =
FV(0.08, Yearssince, 0, -baseline)

return runningvalue       

My values are based on the current year (i.e. 2022). Modify the function according to your needs as necessary.

[Whitewater100]

Hello:

I found this article on this topic. First you would make a table of years and growth rates. It is all explained here by Matt Allington.

Compound Growth using DAX - Excelerator BI

[ThomasSan]

Hi @Whitewater100 ,

thank you for your reply. However, I am unable to replicate your solution

As you can see, I am using a parameter slider in order to determine the growth rate. But I am unable to replicate step 4 for the manual. What could be the reason for it?