New,Lost,Existing Customer Count -- Dax Help

[Anonymous]

Hello,
I just want to count new,lost,existing customer. Below are my data and scenario

Emp Name	EMP iD	Date	Month
A	101	4/30/2020	Apr
B	102	4/30/2020	Apr
C	103	4/30/2020	Apr
D	104	4/30/2020	Apr
B	102	5/31/2020	May
C	103	5/31/2020	May
D	104	5/31/2020	May
E	105	5/31/2020	May
C	103	6/30/2020	Jun
D	104	6/30/2020	Jun
F	106	6/30/2020	Jun
C	103	7/31/2020	Jul
D	104	7/31/2020	Jul
E	105	7/31/2020	Jul
B	102	7/31/2020	Jul
A	101	7/31/2020	Jul
A	101	8/31/2020	Aug
E	105	8/31/2020	Aug
B	102	8/31/2020	Aug

on the first month April ,Every body will be my new customer (i.e A,B,C,D) so count will be 4.

on the next month May,B,C,D continues so they are my existing customer (count is 3).A discontinues so A is lost customer (Count is 1) and a new Customer E is joining,so count is also 1.

on june, C and D my existing Customer (Count 2), B and E my Lost customer ( count 2) and A is my non-existing customer (beacuse on th previous month A was my lost customer). and a new customer "F" is joining (Count 1).

on july,C and D my existing Customer (Count 2) .again A,B,E are joining on this month so they are my new customer (Count 3) and F is my lost customer (Count 1).
on last month August, A, E, B continues,so they are my existing customer (count 3).C & D my Lost customer (Count 2) because they are missing and F is my non-existing customer.

New customer Logic : 1) If any one not into the previous month, but appears into the current month so they are my New.2)if anyone lost in previous month but again they are joining in current month so they are my new.

Lost Customer : if any one appears into previous month. but does not continue into current month then they are my lost customer.

Non-existing Customer : If anyone lost in previous month and again not continue in current month then they are my non-existing customer.
Existing customer : if any one appears in previous month and aalso apeears on current month thn they are my existing customer.

Please see the below image.

Desired Count:

Month          New customer          Existing Customer      Lost Customer              Non-existing Customer
April :                 4  (A,B,C,D)                          0                                       0                              0

May :                  1 (E)                                   3(B,C,D)                  1(A)                                     0

June :                   1(F)                                   2(C,D)                     2 (B,E)                                 1 (A)

July  :                   3 (A,B,E)                            2 (C,D)                   1(F)                                       0

August :               0                                       3(A,B,E)                  2 (C,D)                                 1(F)

I have to build this logic into Dax rather than PQ.
Can anyone help me into this?? how to achieve that..

@ Zubair_Muhammad, @Greg_Deckler

[Fowmy · Accepted solution]

@Anonymous 

I have worked out a solution that you can find in the attached PBIX file. Hope it answers your questions.
I added a calendar table as well. Modify your table and field names as necessary.



Download:  https://1drv.ms/u/s!AmoScH5srsIYgYIRa-ntfQ0YKA50IQ?e=Pmq6KZ

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Anonymous]

@Fowmy 
Thanks for help fowmy..
I was calculating Existing customer and received your answer..
Below are my approach.

Existing : 

VAR _1 = CALCULATETABLE(VALUES(Source[Emp Name]))

VAR _2 = CALCULATETABLE(VALUES(Source[Emp Name]),FILTER(ALL(Source),Source[Month Number] = MAX(Source[Month Number])-1 ) )

VAR _common=INTERSECT(_1,_2)

return

COUNTROWS(_common)

Lost:

VAR _1 = CALCULATETABLE(VALUES(Source[Emp Name]))

VAR _2 = CALCULATETABLE(VALUES(Source[Emp Name]),FILTER(ALL(Source),Source[Month Number] = MAX(Source[Month Number])-1 ) )

VAR _common=EXCEPT(_2,_1)

return

COUNTROWS(_common)

New:

VAR _1 = CALCULATETABLE(VALUES(Source[Emp Name]))

VAR _2 = CALCULATETABLE(VALUES(Source[Emp Name]),FILTER(ALL(Source),Source[Month Number] = MAX(Source[Month Number])-1 ) )

VAR _common=EXCEPT(_1,_2)

return

COUNTROWS(_common)

I have not executed your solution yet.. Let me execute first..If i have any question..i will let you know obvious..
thanks

[amitchandak]

@Anonymous , refer if this solution can help

https://community.powerbi.com/t5/Desktop/Churn-Rate-lost-Customer/m-p/1173754#M529196

There is pbix , which was also shared

[Anonymous]

@amitchandak 
Thanks for sharing..Let me see this post if it meets my requirement or not.. I will let you know..