topic Re: % growth in monthly report in DAX Commands and Tips

% growth in monthly report

Mniknejad — Wed, 01 Dec 2021 02:44:00 GMT

Hi Folks

I have table like below

Consid	consdate	state	city
qqjh	2021-08-01	ON	toronto
skjdf	2021-07-01	QC	Montreal
dksdlksm	2021-07-10	QC	Montreal
wodmslm	2021-08-01	QC	Montreal
djnhm	2021-07-10	ON	toronto
lklj	2021-08-10	ON	toronto
sclzx	2021-07-10	ON	toronto
efrd	2021-06-10	ON	toronto
aaa	2021-06-01	QC	Montreal
gfg	2021-06-22	QC	Montreal

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


Count of Consid	Column Labels
Row Labels	Jun	Jul	Aug	Grand Total
ON	235	137	324	696	JUL - Jun /JUN = = -98 /235	aug -Jul /JUL == 187 /137
toronto	235	137	324	696
QC	122	132	100	354
Montreal	122	132	100	354
Grand Total	357	269	424	1050

what is your sugesstion ?!

Re: % growth in monthly report

VahidDM — Wed, 01 Dec 2021 03:55:25 GMT

Hi Mniknejad

Try this measure:

%Growth = VAR _LastM = CALCULATE ( COUNTA ( 'Table'[Consid] ), ALLEXCEPT ( 'Table', 'Table'[consdate], 'Table'[state], 'Table'[city ] ), PREVIOUSMONTH ( 'Table'[consdate] ) ) VAR _ThisM = COUNTA ( 'Table'[Consid] ) RETURN IF ( ISBLANK ( _LastM ), BLANK (), ( _ThisM - _LastM ) / _LastM )

Output:

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

Re: % growth in monthly report

Jihwan_Kim — Wed, 01 Dec 2021 04:02:10 GMT

Hi,

Please check the below picture and the attached sample pbix file.

I suggest having a Dim-Calendar table.

ID Count: =

COUNTROWS ( Data )

ID count Previous Month: =

CALCULATE ( [ID Count:], DATEADD ( 'Calendar'[Date], -1, MONTH ) )

ID Count Growth: =

IF (

ISBLANK ( [ID Count:] ),

BLANK (),

DIVIDE ( [ID Count:] - [ID count Previous Month:], [ID count Previous Month:] )

)

Re: % growth in monthly report

v-xiaotang — Wed, 22 Dec 2021 02:37:37 GMT

Hi Mniknejad

Based on the sample you attached, you can try this,

create the measure

growth = var _premonth=MAX('calendar'[Month])-1 var _thiscount=CALCULATE(COUNT('Table'[Consid]),'Table') var _precount=CALCULATE(COUNT('Table'[Consid]),FILTER(ALLEXCEPT('Table','Table'[state],'Table'[city]),MONTH('Table'[consdate])=_premonth)) return DIVIDE(_thiscount-_precount,_precount,BLANK())

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.