Create measure based on another measure | How to calculate CAGR based on a measure?

[MSC]

Hi everyone

I am working with a simple dataset for educational purposes which consists of rows with financial statement data (think of inventories, total assets, etc.) for different years and companies. I have already created some measures, e.g. Return on Equity. In my simple report, I am using two slicers - one to select a company and another one to select a range of years (since I like to show the development of different KPIs in my visuals).

Now, I would like to calculate the compound annual growth rate (CAGR) for a selected company and time frame.

For this, I have experimented with some DAX formulas from ChatGPT. Even though I am convinced that I described my "problem" thoroughly, the formulas do not return the correct values.

My dataset contains, among others, the following tables:

-Date_Table with column Date contains all the years -> Used for slicer

-Data_Company with column Company contains all the companies -> Used for slicer

-Calculations_Financials contains all the measures (such as Return on Equity for which I would like to calculate the CAGR)

All tables are linked by connectors either directly or indirectly (via other tables).

I used the following DAX code:

CAGR_ROE =
VAR StartYear = YEAR(MIN('Data_Financials'[Date]))
VAR EndYear = YEAR(MAX('Data_Financials'[Date]))
VAR StartValue =
CALCULATE(
[Return on Equity],
FILTER(
ALL('Data_Financials'),
'Data_Financials'[Date] = DATE(StartYear, 12, 31) &&
'Data_Company'[Company] = SELECTEDVALUE('Data_Company'[Company])
)
)
VAR EndValue =
CALCULATE(
[Return on Equity],
FILTER(
ALL('Data_Financials'),
'Data_Financials'[Date] = DATE(EndYear, 12, 31) &&
'Data_Company'[Company] = SELECTEDVALUE('Data_Company'[Company])
)
)
VAR NumYears = EndYear - StartYear

RETURN
IF(
NumYears > 0 && NOT(ISBLANK(StartValue)) && NOT(ISBLANK(EndValue)),
(EndValue / StartValue) ^ (1 / NumYears) - 1,
BLANK()
)

However, this does not work and it seems that the formula is not selecting the "earliest" and "latest" years in my date filter. Furthermore, the VAR return wrong values for the respective years if I compare it with a simple line chart showing the RoE for the time frame selected.

Can anyone help with the calculation of the CAGR based on another measure?

Thank you very much in advance for any assistance and tips!

[Wilson_]

Hi MSC,

Can you please share a sample pbix file? (If you don't know how, please check the pinned thread in the forum.) It would make debugging your issue easier. 🙂

[MSC]

Dear Wilson_, please find attached a demo file of my dashbord with the same structure/data model.

Dashboard_Power-BI-Community.pbix 

[Wilson_]

Hi MSC,

I've re-attached your pbix file with the my CAGR_ROE measure. I've added my own simplified date table that fixes the first two issues I note below.

A few notes:

• Date tables need to contain full years without gaps, especially if you use standard time intelligence functions. Additionally, it's a good idea to mark the table that contains your calendar/dates as a date table once you make sure there are no gaps. (You can do this by right clicking the table and clicking "Mark as date table".)
• You should not have a relationship between your Calculations_Financials measures table and any of your tables. It's only supposed to house your calculations.
• I also believe your CAGR on equity calculation is incorrect.

That being said, to also answer your underlying question on the measure not selecting the "earliest" and "latest" years in my date filter, that's because in your table or line chart, the year is also part of what is called the filter context.

On the left, you're filtering for year between 1996 and 2020. However, on the highlighted row on the right, that row is filtering for year = 2001. Therefore, when it's doing the calculation for that row, the earliest year is 2001 and the latest year is also 2001. This video on filter context from SQLBI might help the concept. They also have a whole playlist explaining fundamental concepts of Power BI you might find helpful.

The way I typically get around that if I need to reference the slicer and ignore the visual I'm in is to use CALCULATE ( ALLSELECTED() ). You will see this in the CAGR_ROE measure in my attachment.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[MSC]

Hey Wilson_, thanks a lot for your updated file and especially the useful explanation. Very appreciated! I`ve just taken a look at the video and your pbix file incl. your calculations. You calculate the CAGR for the Total Equity but it also works out for Return on Equity if one changes it in the formula, obviously. Thanks!

Now, what I would like to do is to not only look at the CAGR of a single company but also compare it with that of the industry to make interpreting the value more valuable. Is it possible to calculate the CAGR for a group of company which all match the industry of my focal company, let`s say ABB in this example? So I want to calculate CAGR for ABB from 2016 to 2020 and I also want to calculate CAGR for all other companies in my dataset which are also part of the Electrical Equipment industry.

How would I do that in DAX? I can imagine the calculation will look similar to the formula you already provided but how do I include the additional "condition" for matching industry?

Again, thank you very much for any assistance!

[Wilson_ · Accepted solution]

MSC,

An update like the below should work for getting the whole industry.

The logic is exactly the same. My only note is whether you remove the filters from the whole table or just from the Company field you are slicing on (or any other configuration) depends on the nuance of what exactly you want to calculate.

[MSC]

Thank you very much for your help and the solution provided, Wilson_!

[Wilson_]

You're welcome! 😄