Dynamic CAGR formula?

ccccc4

I know how to do a basic CAGR formula between two static years:

power((sum(CASE WHEN Fiscal Year = 2024 THEN Amount else 0 end)
/ sum(case when Fiscal Year = 2020 then Amount end)), 1 / 4)
-1

Is there a way to construct a CAGR calculation that responds to user filtering or changing the timeframe? I am trying to think of a way to make it so a user can choose a different start and end year and have it calculate the CAGR correctly.

Manasi_Panov

Hello @ccccc4,

It's a bit complicated and I'll try to explain in detail how I approached this. First, I've created a Variable for the Start Period as follows:

I applied the same process for the CAGR end period, defining the start and end years for the calculation. Using these variables, I calculated the CAGR with the following formula:

Power(
(SUM(CASE WHEN Parts Kits Fiscal Year=SEG PKA CAGR End Period THEN Parts Kits Qty END)
/
SUM(CASE WHEN Parts Kits Fiscal Year=SEG PKA CAGR Start Period THEN Parts Kits Qty END))
,1/(SEG PKA CAGR End Period-SEG PKA CAGR Start Period+1))-1

On the dashboard, you can set the CAGR calculation period (I know, I also hate the decimal in the year).

This automatically calculates the CAGR for the 2019–2023 period in the following graph:

Data_Devon

VARIABLES!!!

Beast of a topic, so I'll defer to Domo's existing knowledge base - see link below.

Variables | Overview

It's kind of a Beastmode for Beastmodes (BeastModes^2???)

This should get you were you want to be - let me know if I misunderstood

https://domo-support.domo.com/s/article/7903767835031?language=en_US

Manasi_Panov

Hello @ccccc4,

It's a bit complicated and I'll try to explain in detail how I approached this. First, I've created a Variable for the Start Period as follows:

I applied the same process for the CAGR end period, defining the start and end years for the calculation. Using these variables, I calculated the CAGR with the following formula:

Power(
(SUM(CASE WHEN Parts Kits Fiscal Year=SEG PKA CAGR End Period THEN Parts Kits Qty END)
/
SUM(CASE WHEN Parts Kits Fiscal Year=SEG PKA CAGR Start Period THEN Parts Kits Qty END))
,1/(SEG PKA CAGR End Period-SEG PKA CAGR Start Period+1))-1

On the dashboard, you can set the CAGR calculation period (I know, I also hate the decimal in the year).

This automatically calculates the CAGR for the 2019–2023 period in the following graph:

ccccc4

https://community-forums.domo.com/main/discussion/comment/99743#Comment_99743

Thanks! I was able to replicate this and it works great. It would be nice if Variables were more dynamic (i.e point the variable to an existing field and it populates the values. My data already has the Fiscal Year field in it, so having to manually add each year to the Variable selection list is tedious.