Compound Growth Rate for Month wise

[Anonymous]

I have the few years Data from 2021 to 2025 .  Every year have QTY month wise . i  want find  compund growth for month wise . i used below formula for Years in Excel . i want to convert to Month.

Syntax:

((currentyear)/(previousyear))^(1-n))-1

LIke : 

((Currentmonth)/(PreviousMonth))^(1-n))-1

QTY   Month   year

10       Jan       2021

20       Feb      2021

30       Mar      2021

40       Apr       2021

-----

---

21        Dec      2025

Looking for support .. thanks in advance 

[Anonymous]

@FreemanZ  Thank you so much . its working 

[FreemanZ · Accepted solution]

hi @Anonymous 

supposing you have a table like:

Year	Month	Qty
2022	9	1
2022	10	2
2022	11	3
2022	12	4
2023	1	5
2023	2	6
2023	3	7
2023	4	8
2023	5	9
2023	6	10
2023	7	11
2023	8	12
2023	9	13
2023	10	14
2023	11	15
2023	12	16
2024	1	17

1) add a calculated column like:

MonthSN = ([Year]*12+[Month])

2) plot a slicer with a calculated table like:

n = 
SELECTCOLUMNS(
    GENERATESERIES(1,20),
    "n", [Value]
)

3) plot a table visual with year and month column and a measure like:

CMGA% = 
VAR _currentmonthSN = MAX(data[MonthSN])
VAR _n = SELECTEDVALUE(n[n])
VAR rate =
    DIVIDE(
        SUM(data[value]),
        CALCULATE(
            SUM(data[value]),
            ALL(data),
            data[MonthSN]=_currentmonthSN-_n
        )
    )^(1/_n)
    -1
RETURN
    IF(rate<>-1, rate, "")

it worked like: