CAGR computation

[sgupta22]

Hi everyone,

I am trying to compute the CAGR growth for "organic revenue".  However, the CAGR computation is wrong as it is simply giving me the CAGR of "Total reveneu" and not "Organic revenue".  Would be great if someone can help me figure what's going wrong with the formulas.  Below is the link to the power bi file as well for ease.

https://www.dropbox.com/s/4taoyigtytv84bc/Organic%20revenue%20CAGR%20calcualtion.pbix?dl=0

Also, here are some of the formulas that are being used to compute organic revenue:

Organic revenue = 
    VAR Revenue = CALCULATE(SUM('Table'[Total Revenue]))
    VAR Acquired_revenue = [Cumulative acquired revenue]
RETURN
    (Revenue - Acquired_revenue)

Cumulative acquired revenue = 
    VAR CurrentDate = MAX('Calendar'[Year])
    VAR StartDate = MINX(ALLSELECTED('Calendar'), [Year])
    VAR DateSelection = FILTER(ALLSELECTED('Calendar'), 'Calendar'[Year] > StartDate && 'Calendar'[Year] <= CurrentDate)
RETURN
    SUMX(DateSelection, [Total acquired revenue])

CAGR: Organic Revenue = 
    VAR BeginningDate = CALCULATE(MIN('Calendar'[Year]), ALLSELECTED('Calendar'[Year]))
    VAR EndingDate = CALCULATE(MAX('Calendar'[Year]), ALLSELECTED('Calendar'[Year]))
    VAR BeginningValue = CALCULATE([Organic revenue], FILTER('Calendar', 'Calendar'[Year] = BeginningDate))
    VAR EndingValue = CALCULATE([Organic revenue], FILTER('Calendar', 'Calendar'[Year] = EndingDate))
    VAR Years = (EndingDate - BeginningDate)
RETURN
    IF(BeginningValue <= 0, BLANK(), (EndingValue / BeginningValue)^(1/Years) - 1)

Thanks a ton

Shashank

@Ashish_Mathur 

[VijayP]

@sgupta22 

If you can share the sample data , because I want to understand  "Organic" , so that I can give a better solution!

[sgupta22]

Hi Vijay

Here is the link to the file:

https://www.dropbox.com/s/4taoyigtytv84bc/Organic%20revenue%20CAGR%20calcualtion.pbix?dl=0

Regards

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[sgupta22]

Hi Ashish,

Thank you for the response.  Unfortunately, this isn't exactly what I was looking for.  For example, the new column 'Org revenue' is the difference between Total revenue and Acquired revenue each year.  In my case, however, Organic revenue is the difference between Total revenue and the running total of Acquired revenue.  The data for Organic revenue would look something like this: 

And thus, if the slicer has years between 2018 and 2022, the CAGR for organic revenue would pick the beginning value of 133 and ending value of 193.  Instead, it takes the value for total revenue to compute the CAGR.  I believe this might be something to do with Organic revenue being dependent on running totals. 

I hope I haven't confused you more.

Regards

Shashank 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[sgupta22]

Great!  This worked perfectly thank you 🙂

If I may ask, I noticed that you changed the computation of Cumulative acquired revenue. Why was the previous calculation causing the issue? If the answer's complicated and would take up too much of your time to explain, please ignore this question 🙂

Thanks a ton

Shashank

[Ashish_Mathur]

You are welcome.  I do not know whether the previous calculation was an issue or not.  Since you had a Calendar table, i just chose to simplify/shorten your long measure.

[VijayP]

@sgupta22 

Try this video https://www.youtube.com/watch?v=p_t-l78iMQI&t=303s&pp=ygUQY2FnciBpbiBwb3dlciBiaQ%3D%3D

I hope you will achieve what exactly you are looking for!

[sgupta22]

Hi Vijay,

Thanks for your reply. Unfortunately, this didn't solve my issue. I think my issue is arising from the fact that Organic revenue in my case is based on running totals because of which Power Bi is not picking the right beginning and ending values.

Regards

Shashank