The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I am trying to figure out how to get the Compound Annual Growth Rate for some data but the measure not working.
I have Sales measures- Current year sales, first year sales and count of total years -1
The formula in Excel:
(B23/B2)^(1/C23)-1
b23- current year
b2 -first year
c23 - count of all years -1
measures :
Any help is greatly appreciated.
Thanks!!
Hi @amitchandak ,
I got the CAGR result for first year.
But how can i achieve the CAGR value for the all the remaining years?
Hi @vs_7 ,
Please try :
FirstYearSales =
VAR FirstYear = MINX (all('year_sort'), 'year_sort'[Year] )
RETURN
CALCULATE (
SUM ( 'Final data'[amt] ),
Filter(ALL('year_sort),'year_sort'[Year] = FirstYear)
)
CurrentYearSales =
VAR CurrentYear = MAX ( 'year_sort'[Year])
RETURN
CALCULATE (
SUM ( 'Final data'[Amount] ),
Filter(ALL('year_sort'),'year_sort'[Year] = CurrentYear)
)
CountYears = COUNTROWS(ALL(Year_Sort))-1
CAGR =
VAR CurrentYearSales = [CurrentYearSales]
VAR FirstYearSales = [FirstYearSales]
VAR CountYears = [CountYears]
RETURN
POWER (
DIVIDE ( CurrentYearSales,FirstYearSales),1 / CountYears) - 1
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @mwegener @amitchandak ,
please suggest
Issue not resolved from above dax
i'm getting values only for first year when uisng current year measure and current year sales measure not working
measures
Hi, @v-tangjie-msft @amitchandak @parry2k
Issue not resolved from above dax
i'm getting values only for first year when uisng current year measure and current year sales measure not working
measures
Hi @vs_7 ,
Please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
Hi @v-tangjie-msft @amitchandak @mwegener@@parry2k
Issue not resolved from above dax
i'm getting values but data is not showing properly
when im selecting start year and end year in slicer its showing same in both column
out put in table visual
year Startyear ENd year
2020 4796.24 4796.24
2022 6663.67 6663.67
Required Output
year Startyear ENd year
2020 4796.24
2022 6663.67
Measures
@vs_7 , I have made a few changes in the measure.
measures :
FirstYearSales =
VAR FirstYear = MINX (allselected('year_sort'), 'year_sort'[Year] )
RETURN
CALCULATE (
SUM ( 'Final data'[amt] ),
'year_sort'[Year] = FirstYear
)
CurrentYearSales =
VAR CurrentYear = MAXX (allselected('year_sort'), 'year_sort'[Year])
RETURN
CALCULATE (
SUM ( 'Final data'[Amount] ),
'year_sort'[Year] = CurrentYear
)
CountYears = COUNTROWS(Year_Sort)-1
or
CountYears = MAXX (allselected('year_sort'), 'year_sort'[Year]) - MINX (allselected('year_sort'), 'year_sort'[Year] )
CAGR =
VAR CurrentYearSales = [CurrentYearSales]
VAR FirstYearSales = [FirstYearSales]
VAR CountYears = [CountYears]
RETURN
POWER (
DIVIDE ( CurrentYearSales,FirstYearSales),1 / CountYears) - 1
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
100 | |
81 | |
62 | |
54 |
User | Count |
---|---|
250 | |
119 | |
115 | |
95 | |
70 |