topic Re: How to calculate monthly grow rate % in DAX Commands and Tips

How to calculate monthly grow rate %

lyly — Thu, 19 Jan 2023 06:39:16 GMT

Hello, I still beginner in power bi,

I want to make the monthly growth rate % for this data can someone help me how to do this using the DAX measure? Here is my table;

Re: How to calculate monthly grow rate %

XIB1 — Thu, 19 Jan 2023 07:20:11 GMT

Hi, it could be calculated like this:

Growth % = var _donor = SUM('Table'[Donor]) var _prevdonor = CALCULATE(SUM('Table'[Donor]), PREVIOUSMONTH('Table'[Month])) return _donor / _prevdonor - 1

Re: How to calculate monthly grow rate %

tamerj1 — Thu, 19 Jan 2023 07:49:35 GMT

Hi lyly

It depends. Is "Month" a month hierarchy of a date column in the same table or is it an independent column in the same table or is it a column from a connected date table?

Re: How to calculate monthly grow rate %

lyly — Thu, 19 Jan 2023 08:41:37 GMT

Hello,

I have table like below

DonorID	Joined Date
1111111	15/01/2021
2222222	17/02/2021
333333	23/01/2021
444444	2/02/2021
555555	1/03/2021
6666666	3/03/2021
7777777	11/03/2021
8888888	14/03/2021

I need to show a table that show month to month count (DonorID) and %Growth of each month like this

Month	Count of Donor ID	%growth rate
Jan	844	%growth rate
Feb	760	%growth rate
Mar	953	%growth rate

what is your sugeestion?

Re: How to calculate monthly grow rate %

FreemanZ — Thu, 19 Jan 2023 09:18:31 GMT

hi lyly

try to add a column like this:

MONTH = FORMAT(TableName[Joined Date], "MMM")

plot a table visual with Month column, DonorID column (choose count) and a measure like this:

GrowthRate = VAR _month = MAX(TableName[Month]) VAR _countcurrent = COUNTROWS(TableName) VAR _countpre = COUNTROWS( FILTER( ALL(TableName), FORMAT(EDATE(TableName[Joined Date], 1), "MMM") = _month ) ) RETURN IF( _countpre<>0, DIVIDE(_countcurrent, _countpre) -1 )

it works like this:

Re: How to calculate monthly grow rate %

tamerj1 — Thu, 19 Jan 2023 10:03:52 GMT

FreemanZ

That should work but you need to pay attention to the possibility of having multiple years. I would prefer using a YearMonth column preferably from a legitimate date table. The dax would be simpler using time intelligence functions.