How to use CAGR to show monthly future sales

[steph_io]

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/

[steph_io]

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

[Ashish_Mathur]

Hi,

Share the link from where i can download that Excel file.

[steph_io · Accepted 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]) )

[Ashish_Mathur]

Hi,

Share your dataset and show the expected result.

[vcastello]

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