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.
I have used the following sample to create the CAGR % and future value, but I aslo need a monthly breakdown of this calculation to be able to chart it per month over the next X future months.
https://powerbi.tips/2016/05/measures-calculate-cagr/
Solved! Go to Solution.
I think I have it working now.
To chart the FV for each month, each value used in the formula had to work for each monthly context.
FV Chart = (([Cummulative Ending Value]*(1+[CAGR UpTo CurrentMonth])^[# of Future Month counter]))
Cummulative Ending Value = CALCULATE([Ending Value], FILTER(ALLSELECTED(DateTable), DateTable[YearMonthNumber] <= max(DateTable[YearMonthNumber])))
CAGR UpTo CurrentMonth = CALCULATE([CAGR], FILTER(all(DateTable),DateTable[YearMonthNumber] <= MAX(DateTable[CurrentYearMonthNumber])))
The is just the # of months from the current month.
# of Future Month counter = IF([# of Months] <= max(DateTable[CurrentYearMonthNumber]),0,
CALCULATE(DISTINCTCOUNT(DateTable[YearMonthNumber]), FILTER(ALL(DateTable), DateTable[YearMonthNumber] <= MAX(DateTable[YearMonthNumber] )))- max(DateTable[CurrentYearMonthNumber]) )
Here is a sample dataset where the formula to apply the CAGR (renamed CMGR since it's calculated monthly), is working in excel. I am able to show per month the future compounded growth amount (in grey below).
In my scenario I have sales just to March 2017, and I want to show the calculated future amounts for each month. In power BI, my formulas work to generare a KPI card of a fixed future month amount, but how do I make it calculate for each month in the range of month's up to the [# of future months]:
CAGR FV = [Total Sales CM]*((1+[CAGR])^[# of Future Months])
CAGR = IFERROR(([Ending Value]/[Beginning Value])^(1/[# of Months])-1,0)
Beginning Value = CALCULATE([Cumulative Sales],FILTER(DateTable,DateTable[YearMonthNumber]<=MIN(DateTable[YearMonthNumber])))
Ending Value = CALCULATE([Cumulative Sales],FILTER(DateTable,DateTable[YearMonthNumber]<=MAX(DateTable[YearMonthNumber])))
# of Months = CALCULATE(DISTINCTCOUNT(DateTable[YearMonthNumber]), FILTER(ALLSELECTED(DateTable), DateTable[YearMonthNumber] <= max(DateTable[YearMonthNumber])) // have a slicer in place to select the range of months to use in the CAGR calculation
# of Future Months = MAX('Future #Months'[Future #Months]) //this is a numeric value passed via parameter
I need a DAX formula to itterate over each future month, and provide the CAGR FV.
Thank you kindly for your help
Hi,
Share the link from where i can download that Excel file.
I think I have it working now.
To chart the FV for each month, each value used in the formula had to work for each monthly context.
FV Chart = (([Cummulative Ending Value]*(1+[CAGR UpTo CurrentMonth])^[# of Future Month counter]))
Cummulative Ending Value = CALCULATE([Ending Value], FILTER(ALLSELECTED(DateTable), DateTable[YearMonthNumber] <= max(DateTable[YearMonthNumber])))
CAGR UpTo CurrentMonth = CALCULATE([CAGR], FILTER(all(DateTable),DateTable[YearMonthNumber] <= MAX(DateTable[CurrentYearMonthNumber])))
The is just the # of months from the current month.
# of Future Month counter = IF([# of Months] <= max(DateTable[CurrentYearMonthNumber]),0,
CALCULATE(DISTINCTCOUNT(DateTable[YearMonthNumber]), FILTER(ALL(DateTable), DateTable[YearMonthNumber] <= MAX(DateTable[YearMonthNumber] )))- max(DateTable[CurrentYearMonthNumber]) )
Hi,
Share your dataset and show the expected result.
Hi @steph_io
Why don't you create the same measure but using months instead of years.
In the example you have used ....
# of Months = DATEDIFF(MIN('World GDP'[MonthNumber]),MAX('World GDP'[MonthNumber]), MONTH)
Hope That Helps
Vicente
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 |
---|---|
115 | |
100 | |
89 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |