Forum Discussion

EricPeabody's avatar
EricPeabody
Frequent Visitor
6 years ago

Using a Dynamically Calculated CAGR to Project Future Value

Okay, I am trying to use a dynamically calculated CAGR to project future value. I think I have all the parts and pieces: Using historical sales data, I have a dynamic sales total called CAGR Sales ...
Need Help
v-joesh-msft's avatar
v-joesh-msft
Solution Sage
6 years ago

Hi  EricPeabody ,

I think it's because of your fields “CAGR”, “CAGR Sales End” have no value in the matrix, so the formula in Sales Project cannot refer to the corresponding value. I'm not quite sure what formula you use, but I believe that when you change filter ("table") in the formula to filter (All ("table")) or filter (Allselected ("table")), the formula should work:

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/ERaJXNQrgP9AoIC4GCVHf3oBE8Xx1t3SNwkeBReAft3YZw?e=ctbzPu

Best Regards,
Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

EricPeabody's avatar
EricPeabody
Frequent Visitor
6 years ago

Thanks Joey,

 

I really appreciate your help on this. I face variations on this dynamic CAGR/projected value all the time and I always end up with a really clunky solution with some manual calculations in excel.

 

I think I failed to communicate the "dynamic" nature of the sales data and CAGRs. The sales data has several categories to it like market, client, and actual/target. Each of the clients and markets grows at a different rate and thus has a different CAGR.


Also, the start year and end year for the CAGR calculation varies client to client. I like your <>BLANK() approach to sorting out the start and end values. I was just using an iferror to filter out the values that dropped off. I also had to resort to a parameter to define the end year for the CAGR. 

 

I plugged in your measures but had trouble adapting them to work with the way the data is setup. Here is a screenshot of the two approaches side by side. 

 

 

To make things easier, here is a link to the pbix file so you can see the data structure better. I created two measure tables so we can compare the two approaches.

 

https://app.box.com/s/457g0nqylg9sjwrr5h4x6qo6zbk5eexj

 

Thanks so much!!