Dynamic CAGR formula?

ccccc4
ccccc4 Member
in Beast Mode

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.

Tagged:

Best Answer

  • Manasi_Panov
    Manasi_Panov Coach
    Answer ✓

    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:

    image.png

    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:

    image.png

    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).

    image.png

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

    Capture2.JPG

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

Answers

  • Data_Devon
    Data_Devon Contributor

    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

    ✅Did this solve your problem? Accept it as a solution!

    ❤️Did you love this answer? Mark it as "Awesome"!

    👍Do you agree with this process? Click "Agree"!

  • Manasi_Panov
    Manasi_Panov Coach
    Answer ✓

    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:

    image.png

    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:

    image.png

    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).

    image.png

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

    Capture2.JPG

    If you found this post helpful, please use 💡/💖/👍/😊 below! If it solved your problem, don't forget to accept the answer.

  • ccccc4
    ccccc4 Member
    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.

Categories