Using a Dynamically Calculated CAGR to Project Future Value

[EricPeabody]

Okay, I am trying to use a dynamically calculated CAGR to project future value. I think I have all the parts and pieces:

1. Using historical sales data, I have a dynamic sales total called CAGR Sales End.
2. I also have a dynamic CAGR.
3. I have a measure that calculates the number of periods to project into the future called # Years.
4. Using the CAGR Sales End, CAGR, and # Years I have a measure that calculates the Projected Sales.

The Projected Sales measure correctly calculates the value in the column total. I am just having a hard time getting it to iterate across the date table. Any help would be greatly appreciated. Thanks!

Here is the Sales Projected measure and the pivot table with all the parts: 

[v-joesh-msft]

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/ERaJXNQrgP9AoIC4GC...

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]

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!!