topic Re: Help calculating compounding growth rates in DAX in DAX Commands and Tips

Help calculating compounding growth rates in DAX

ThomasSan — Tue, 01 Mar 2022 15:24:51 GMT

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!

Re: Help calculating compounding growth rates in DAX

Whitewater100 — Tue, 01 Mar 2022 15:37:00 GMT

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

Re: Help calculating compounding growth rates in DAX

ThomasSan — Wed, 02 Mar 2022 07:31:43 GMT

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?

Re: Help calculating compounding growth rates in DAX

ThomasSan — Wed, 02 Mar 2022 11:12:52 GMT

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.