topic Re: Compound Growth Rate for Month wise in DAX Commands and Tips

Compound Growth Rate for Month wise

Anonymous — Tue, 09 May 2023 06:23:15 GMT

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

Re: Compound Growth Rate for Month wise

FreemanZ — Tue, 09 May 2023 06:55:38 GMT

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:

Re: Compound Growth Rate for Month wise

Anonymous — Tue, 09 May 2023 08:06:25 GMT

FreemanZ Thank you so much . its working