Get projected trend line based on dynamic CAGR calculated with end goal value and current value

[Anonymous]

I have a data coming for each month customer. Based on the current months' value as current value and target value for year end as end value, I need to get CAGR and the esimate the monthly projection for each month till the year end and create a line chart for the projected trend line. I have done similar in Excel and attached it here. Link to Excel Sheet 

In the following image, The 2020 Customer data are actual data with December 2020 customer value being the latest value. With the Goal of attaining 5000 customer for December 2021 and current value being December 2020 value that is 1500, the CAGR is calculated with : [(End value/current value)^(1/12)]-1.

WIth the CAGR recieved, I Have projected the customer value for Jan throughout Dec 2021 and the displayed them in chart. I want to be able to achieve this in PowerBI. 

Your help is greatly appreciated!!!

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated column.

Projected Customer =
 var _CAGR=
 POWER(DIVIDE(5000,1500),(1/12))
 var _index=
 RANKX(FILTER('Table',[Customer]=BLANK()),[Date],,ASC,Dense)
 var _result=
 POWER(_CAGR,_index)*1500
 return
 IF([Customer]=BLANK(),_result,BLANK())

2. Result.

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous 

How do I Dynamically populate the Projected Customer data?

All I have is the following data, and it varies for different product.

[Ashish_Mathur]

Hi,

Please share complete datasets.  You said that you have this data for multiple products.  So there should be an additional column in each table shown in your second post called product.

[Anonymous]

Hi Ashish Mathur, 

I have attached the link to an excel file. please have a look.

Link to the excel file 

[Ashish_Mathur]

I get an access denied message.

[Anonymous]

Please check this Link Thanks!!!

[Ashish_Mathur]

Hi,

Someone else will help you with this one.

[Anonymous]

Hi  @Anonymous  ,

Here are the steps you can follow：

1. Create calculated column.

CAGR =
var _1=DIVIDE([ Projected Customer],1500)
return
IF([ Projected Customer]=BLANK(),BLANK(),
POWER(_1,(1/12))-1)

2. Result.

Use line chart to put date into axis and CAGR into values

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.