cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Post Partisan

## Compound Growth Rate for Month wise

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

1 ACCEPTED SOLUTION
Super User

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:

2 REPLIES 2
Post Partisan

@FreemanZ  Thank you so much . its working

Super User

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: