Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
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
@@Zubair_Muhamma
Hi Zubair - I know this is 2 years old, but hoping you can help fairly quickly!
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" TableMostRecentYear = 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 beDynamic_CAGR = ([MostRecentYearValue]/[SelectedYearValue])^(1/[Multiplier])-1
If you need the file let me know. I tried this and it works smoothly
I am experiencing circular references when following your directions. Any ideas?
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |