Dynamic CAGR

[Himanshu]

Hi ,

I have created CAGR folmula as per the "https://powerbi.tips/2016/05/measures-calculate-cagr/".

NoofYrs = 2

FILTER(ComData,ComData[Year]=(MAX(ComData[Year])-2)

FILTER(ComData,ComData[Year]=MAX(ComData[Year])

I want to calculate CAGR as per the filter selection (Year), So what i have to use instead of Max for dynamic selection CAGR.

Formula :

DynamicCAGR = (CALCULATE(SUM(ComData[Amount]),FILTER(ComData,ComData[Year]=(MAX(ComData[Year])-2)))/ CALCULATE(SUM(ComData[Amount]),FILTER(ComData,ComData[Year]=MAX(ComData[Year]))))^(1/[NoofYrs])-1

Thanks,

[Zubair_Muhammad]

Hi @Himanshu

One solution to this problem is to make use of Parameter Table.

Go to Modelling Tab and create a new Table "YearsTable". This will be used for selecting Years.

YearsTable = summarize(ComData,ComData[YEAR])

Create YEAR slicer using above table and select any year other than most recent year

Now create following measures in your "ComData" Table

MostRecentYear = CALCULATE(max(ComData[YEAR]),all(ComData[YEAR]))

MostRecentYearValue = calculate(Sum([Amount]),filter(All(ComData[YEAR]),ComData[YEAR]=[MostRecentYear]))

SelectedYearValue = calculate(Sum(ComData[Amount]),filter(All(ComData[YEAR]),ComData[YEAR]=values(YearsTable[YEAR])))

Multiplier = [MostRecentYear]-values(YearsTable[YEAR])

Now your Dynamic CAGR measure would be

Dynamic_CAGR = ([MostRecentYearValue]/[SelectedYearValue])^(1/[Multiplier])-1

If you need the file let me know. I tried this and it works smoothly

[Anonymous]

@@Zubair_Muhamma 

Hi Zubair - I know this is 2 years old, but hoping you can help fairly quickly!

1.  I need to calculate CAGR by our Fiscal Year (which starts in November) for balances for several different accounts based on the open (beginning) date of the account.
2. For example account A opened November 25, 2015, Account B opened June 2, 2009, Account C opened February 15, 2019.
3.  The Fiscal year begins in November of each year
4. Not sure if this is relevant, but the balances are already cumulative; i.e. the balance for the most current month is the sum total of all previous months/years.
5. If an account doesn't have a full year's history, I'd like it to be able to pro-rate CAGr based on the month we're currently in. 
6. I have a table already set up for our fiscal years, but it's in a different table than my accounts and balances and open dates. 
7. Ultimately, I need to be able to select an account based on a slicer or some other filter (I already have set up); it should then calculate the CAGr in a card or table visual based on the selected account.  Behind the scenes, it should also know the beginning dates of each account (which is used to calculate CAGr).     
8. Unfortunately, I can't send a sample file due to the confidentiality of the data, but hoping this is fairly intutive the way I've explained it here. 

Thanks in advance for your assistance! 

---

@Zubair_Muhammad wrote:

Hi @Himanshu

One solution to this problem is to make use of Parameter Table.

Go to Modelling Tab and create a new Table "YearsTable". This will be used for selecting Years.

YearsTable = summarize(ComData,ComData[YEAR])

Create YEAR slicer using above table and select any year other than most recent year

Now create following measures in your "ComData" Table

MostRecentYear = CALCULATE(max(ComData[YEAR]),all(ComData[YEAR]))

MostRecentYearValue = calculate(Sum([Amount]),filter(All(ComData[YEAR]),ComData[YEAR]=[MostRecentYear]))

SelectedYearValue = calculate(Sum(ComData[Amount]),filter(All(ComData[YEAR]),ComData[YEAR]=values(YearsTable[YEAR])))

Multiplier = [MostRecentYear]-values(YearsTable[YEAR])

Now your Dynamic CAGR measure would be

Dynamic_CAGR = ([MostRecentYearValue]/[SelectedYearValue])^(1/[Multiplier])-1

If you need the file let me know. I tried this and it works smoothly

---

[tizard]

I am experiencing circular references when following your directions.  Any ideas?

[v-yuezhe-msft]

@Himanshu,

Do you get issues when creating dynamic CAGR? If so, please share more details about your scenario and post expected result here.

If you just share the DAX you use to calculate CAGR as per the filter selection, thanks for your sharing the formula.

Regards,
Lydia