% growth in monthly report

[Mniknejad]

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 ?! 

[v-xiaotang]

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.

[Jihwan_Kim]

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:] )

)

[VahidDM · Accepted solution]

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/