Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Fowmy
Thanks for help fowmy..
I was calculating Existing customer and received your answer..
Below are my approach.
Existing :
@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
@amitchandak
Thanks for sharing..Let me see this post if it meets my requirement or not.. I will let you know..
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |