Forum Discussion
New,Lost,Existing Customer Count -- Dax Help
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
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 🙂
4 Replies
- Fowmy
Super User
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 🙂
- AnonymousNot applicable
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)returnCOUNTROWS(_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)returnCOUNTROWS(_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)returnCOUNTROWS(_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
Super User
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
- AnonymousNot applicable
amitchandak
Thanks for sharing..Let me see this post if it meets my requirement or not.. I will let you know..