Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
steph_io
Advocate II
Advocate II

How to use CAGR to show monthly future sales

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/

 

 

1 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]) )

View solution in original post

5 REPLIES 5
steph_io
Advocate II
Advocate II

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). 

 

Capture.PNG

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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
Super User
Super User

Hi,

 

Share your dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
vcastello
Resolver III
Resolver III

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors