topic Re: calculate active customers previous month and current month in DAX Commands and Tips

calculate active customers previous month and current month

Anonymous — Thu, 07 Oct 2021 22:03:06 GMT

I have 3 columns

and I want to get active customers for current month where status = "won"

and active customers for previous month where status = "won"

for current month im using

ActiveMembers = CALCULATE(DISTINCTCOUNT(Table[Customer]),PARALLELPERIOD(table[Date],0,MONTH),FILTER(

'Table','table'[Status] ="won" ))

and it's working fine.

but i have getting no value for previous month and I'm using:

ActiveMemberPreviousMonth = CALCULATE(DISTINCTCOUNT('table'[Customer]),PREVIOUSMONTH('table'[Date]), FILTER('table','table'[Status]="Won"))

Problem statement: I want help to get current active member and previous month active member where status = "Won"

expected solution:

eg: if today is 12 March 2021:

I want active users of the current month March 2021: 5364 users

and previous month i.e Feb 2021 :

active users 3265 users.

my example data for the table is shown below

customer	date	status
xyz	03-01-2018	won
abc	03-01-2018	lost
efd	03-01-2018	won
ghy	03-01-2018	lost
tgh	05-02-2018	won
fht	01-01-2019	won

Re: calculate active customers previous month and current month

colacan — Thu, 07 Oct 2021 20:39:05 GMT

Anonymous Hi, could you share mroe data? the data you've shared above does not have Feb & Apr data hence it is natural to show no value. When your date filter context is Jan, there is no data for last year Dec, and when date filter is March, there is no data for Feb, and so on.

Re: calculate active customers previous month and current month

smpa01 — Thu, 07 Oct 2021 20:59:39 GMT

Anonymous what do you mean by active customers?

Do you need cumulative distinct count of wins per yr-month

Measure2 = CALCULATE ( DISTINCTCOUNT ( 'Table'[customer] ), FILTER ( ALL ( 'Table'[ date] ), 'Table'[ date] <= MAX ( 'Table'[ date] ) ), 'Table'[ status] IN { "won" } )

Re: calculate active customers previous month and current month

Anonymous — Thu, 07 Oct 2021 21:09:46 GMT

the data is huge I just made a small snippet of it for understanding.

I have data from Jan 2018 to August 2021.

I get accurate active customers for the current month but I don't know how to get active users for the previous month.

my measure for the previous month just doesn't work.

Re: calculate active customers previous month and current month

Anonymous — Thu, 07 Oct 2021 21:11:46 GMT

yes, I want the cumulative distinct count of active users for the current month and the previous month.

Re: calculate active customers previous month and current month

smpa01 — Thu, 07 Oct 2021 21:12:50 GMT

Then try the measure i gave

Re: calculate active customers previous month and current month

Anonymous — Thu, 07 Oct 2021 21:14:19 GMT

how do I use it for finding previous month's active users

Re: calculate active customers previous month and current month

Anonymous — Thu, 07 Oct 2021 21:16:49 GMT

I have data from Jan 2018 to August 2021, I have just shown the above data as a sample snippet.

need help with previous months active users

Re: calculate active customers previous month and current month

v-yingjl — Tue, 12 Oct 2021 05:59:58 GMT

Hi Anonymous ,

You can try these measures:

CurrentMonthCount = CALCULATE ( DISTINCTCOUNT ( 'Table'[Customer] ), FILTER ( 'Table', YEAR ( 'Table'[Date] ) = YEAR ( SELECTEDVALUE ( 'Date'[Date] ) ) && MONTH ( 'Table'[Date] ) = MONTH ( SELECTEDVALUE ( 'Date'[Date] ) ) && 'Table'[Status] = "won" ) )

LastMonthCount = VAR lastmonth = EOMONTH ( SELECTEDVALUE ( 'Date'[Date] ), -2 ) + 1 RETURN CALCULATE ( DISTINCTCOUNT ( 'Table'[Customer] ), FILTER ( 'Table', YEAR ( 'Table'[Date] ) = YEAR ( lastmonth ) && MONTH ( 'Table'[Date] ) = MONTH ( lastmonth ) && 'Table'[Status] = "won" ) )

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: calculate active customers previous month and current month

Anonymous — Tue, 12 Oct 2021 12:10:41 GMT

Thank you