Need Help: Count Customers based on filters & Identify New/ Lost Customers

[Anonymous]

Hi There,

I am looking for help to create few measures. My attempts to seek guidance was not fruitful and hence posting here. I have put the datatable below along with expected answer for ease of answering.

Below are the 3 questions (more detailed with expected answer in "What measures are needed" tab in attached excel:

1. How to calculate the number of customers for whom a certain number of services were rendered?

2. How to identify new customers?

3. How to identify Lost customers?

I used this measure but was not able to identify whether it was "Consultancy" or "Implementation" through filters - 

One Service Only = SUMX(FILTER(SUMMARIZE(FILTER(DataTable,[Revenue]<>0),DataTable[Customer],"Count of Service",DISTINCTCOUNT(DataTable[Service Rendered])),[Count of Service]=1),1)

YearMonthCountryCustomer NameService RenderedDocument noRevenueCash Collected

2015	Jan	USA	Customer 1	Consultancy	892	$           100	$                     100
2015	Jan	USA	Customer 1	Implementation	892	$             80	$                       40
2015	Jan	USA	Customer 1	Consultancy	981	$           240	$                     150
2015	Jan	USA	Customer 2	Consultancy	989	$           110	$                     100
2015	Jan	USA	Customer 2	Implementation	1003		$                     100
2015	Jan	USA	Customer 3	Implementation	1021	$           300	$                     250
2015	Jan	USA	Customer 3	Consultancy	1021	$           750	$                     500
2015	Jan	USA	Customer 4	Consultancy	1022	$           180	$                     150
2015	Feb	USA	Customer 1	Implementation	1055	$           400	$                     340
2015	Feb	USA	Customer 2	Implementation	1057	$           200	$                     200
2015	Feb	USA	Customer 3	Implementation	1060	$           600	$                     600
2015	Feb	USA	Customer 5	Consultancy	1077	$           550	$                     750
2015	Feb	USA	Customer 6	Consultancy	1087	$             50	$                       75
2015	Feb	USA	Customer 7	Consultancy	1088		$                       75
2015	Jan	India	Customer 1	Consultancy	892	80	80
2015	Jan	India	Customer 1	Implementation	892	64	32
2015	Jan	India	Customer 1	Consultancy	981	192	120
2015	Jan	India	Customer 2	Consultancy	989	88	64
2015	Jan	India	Customer 2	Implementation	1003		80
2015	Jan	India	Customer 3	Implementation	1021	240	200
2015	Jan	India	Customer 3	Consultancy	1021	600	400
2015	Jan	India	Customer 4	Consultancy	1022	144	120
2015	Jan	India	Customer 4	Implementation	1022	320	320
2015	Feb	India	Customer 2	Implementation	1057		160
2015	Feb	India	Customer 3	Implementation	1060		480
2015	Feb	India	Customer 5	Consultancy	1077	440	600
2015	Feb	India	Customer 6	Consultancy	1087	40	60
2015	Feb	India	Customer 7	Consultancy	1088		60

		Question:
	Qn 1	In a selected period & Country, how many number of customers in USA & India were provided:
		# 1	Both types of services (i.e., Consultancy & Implementation)
		Expected Ans:	USA in Jan = 2 customer (Customer 1 & 3). Customer 2 had zero revenue on implementation and hence excluded
		# 2	Only one type of service (either Consultancy or Implementation) … sum of 3 & 4 below
		Expected Ans:	USA in Feb = 5 customers (Customer 1, 2, 3, 5 & 6). Customer 7 has no revenue and hence excluded
		# 3	Only Consultancy Services
		Expected Ans:	USA in Jan = 2 customers (Customer 2 & 4). Customer 2 has zero revenue on Implementation and counted for one type of service count
		# 4	Implementation Services
		Expected Ans:	India in Feb = 2 customers (Customer 2 & 3)
		All calculations need to exclude customers who had zero Revenue but had cash collections
	Qn 2	In a selected period & country, how many new* customers were made
		Expected Ans:	USA in Feb = 2 customers (Customer 5 & 6). Customer 7 had zero Revenue in Feb and hence excluded
		If a customer was not in the database in any prior month, the customer is identified as new
		To clarify, a customer for 'consultany' in a prior month is provided 'implementation' services for the first time in the current month is NOT a new customer
	Qn 3	In a selected period & country, how many old customers were lost
		Expected Ans:	India in Feb = 3 customers(Customer 1, 2 & 4). Customer 2 is considered as lost customer on account of zero revenue in Feb
		If a customer ceases to have revenue in any month, the customer is identified as Lost in that month

Regards

Minosh

[Ashish_Mathur · Accepted solution]

Hi,

I'd like to solve this question in steps.  You may refer to my solution to your first question in this PBI file.  For question 4 (Only Implementation services), why should the answer be 2? - there is no billing for Customers 2 and 3 in India in Feb 2015.  In the matrix, i have also shown Customer names for your additional names.  You should be able to drag any field in/out of the filter/matrix.

Let me know how this works.  If there are no mistakes, we will proceed to the next questions.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

I'd like to solve this question in steps.  You may refer to my solution to your first question in this PBI file.  For question 4 (Only Implementation services), why should the answer be 2? - there is no billing for Customers 2 and 3 in India in Feb 2015.  In the matrix, i have also shown Customer names for your additional names.  You should be able to drag any field in/out of the filter/matrix.

Let me know how this works.  If there are no mistakes, we will proceed to the next questions.

Hope this helps.

[Anonymous]

Let me start by saying Thank you Very much! 

Yes, you are right that expected answer to Qn 4 is not 2 as they have zero revenue in the period. I should have put it as USA for Feb should be 2 (Customer 5 & 6).

Let me look through your PBI file and see if that works.

Thanks again

[Ashish_Mathur]

You are welcome.  Look forward to your feedback.

[Anonymous]

Dear Ashish - This works perfectly! Great work! Thank you!

Posting this as Accepted Solution.

Would you mind helping on the new clients and lost clients measure?

[Ashish_Mathur]

Hi,

I made a minor chage to the Measure "Lost Customers".  Please download the file again.  The link shared earlier should work.

[Ashish_Mathur]

Hi,

For new and lost customers, you may downlod the PBI file from here.  Customer 3 is also a lost customer in India in Feb (there was 0 revenue from Customer 3).

Hope this helps.

[Anonymous]

The new client count DAX formula is not giving desired result. It's calculating the total number of client for a selected period.

[Anonymous]

Just to add on ... "Date when revenue was first booked" is showing the first date of the period in the data and not the first date of the user selected period. In the sample data that you created, there is only one year and hence not showing the error. Can you please relook at your formula with multi-years.

[Ashish_Mathur · Accepted solution]

Hi,

Thank you for the feedback.  Could you kinldy help me with data for a few years so i can correct for the error.  If i add fictitious data, i may still not be able to catch the error.

[Anonymous]

Hi Ashish - Please find data for 2 years

YearMonthCountryCustomer NameService RenderedDocument noRevenueCash Collected

2015	Jan	USA	Customer 1	Consultancy	892	$           100	$                     100
2015	Jan	USA	Customer 1	Implementation	892	$             80	$                       40
2015	Jan	USA	Customer 1	Consultancy	981	$           240	$                     150
2015	Jan	USA	Customer 2	Consultancy	989	$           110	$                     100
2015	Jan	USA	Customer 2	Implementation	1003		$                     100
2015	Jan	USA	Customer 3	Implementation	1021	$           300	$                     250
2015	Jan	USA	Customer 3	Consultancy	1021	$           750	$                     500
2015	Jan	USA	Customer 4	Consultancy	1022	$           180	$                     150
2015	Feb	USA	Customer 1	Implementation	1055	$           400	$                     340
2015	Feb	USA	Customer 2	Implementation	1057	$           200	$                     200
2015	Feb	USA	Customer 3	Implementation	1060	$           600	$                     600
2015	Feb	USA	Customer 5	Consultancy	1077	$           550	$                     750
2015	Feb	USA	Customer 6	Consultancy	1087	$             50	$                       75
2015	Feb	USA	Customer 7	Consultancy	1088		$                       75
2015	Jan	India	Customer 1	Consultancy	892	80	80
2015	Jan	India	Customer 1	Implementation	892	64	32
2015	Jan	India	Customer 1	Consultancy	981	192	120
2015	Jan	India	Customer 2	Consultancy	989	88	64
2015	Jan	India	Customer 2	Implementation	1003		80
2015	Jan	India	Customer 3	Implementation	1021	240	200
2015	Jan	India	Customer 3	Consultancy	1021	600	400
2015	Jan	India	Customer 4	Consultancy	1022	144	120
2015	Jan	India	Customer 4	Implementation	1022	320	320
2015	Feb	India	Customer 2	Implementation	1057		160
2015	Feb	India	Customer 3	Implementation	1060		480
2015	Feb	India	Customer 5	Consultancy	1077	440	600
2015	Feb	India	Customer 6	Consultancy	1087	40	60
2015	Feb	India	Customer 7	Consultancy	1088		60
2014	Dec	USA	Customer 1	Consultancy	742	100	100
2014	Dec	USA	Customer 1	Implementation	742	80	40
2014	Dec	USA	Customer 1	Consultancy	755	240	150
2014	Dec	USA	Customer 2	Consultancy	765	110	100
2014	Dec	USA	Customer 2	Implementation	801		100
2014	Dec	USA	Customer 8	Implementation	802	300	250
2014	Dec	USA	Customer 8	Consultancy	802	750	500
2014	Dec	USA	Customer 9	Consultancy	803	180	150
2014	Dec	India	Customer 1	Consultancy	805	80	80
2014	Dec	India	Customer 1	Implementation	806	64	32
2014	Dec	India	Customer 1	Consultancy	809	192	120
2014	Dec	India	Customer 2	Consultancy	911	88	64
2014	Dec	India	Customer 2	Implementation	911		80
2014	Dec	India	Customer 3	Implementation	913	240	200
2014	Dec	India	Customer 3	Consultancy	913	600	400
2014	Dec	India	Customer 10	Consultancy	920	144	120
2014	Dec	India	Customer 11	Implementation	921	320	320
2014	Dec	India	Customer 12	Consultancy	925	320	320

[Anonymous]

Hi Ashish - Were you able to have a look? Please help

[Ashish_Mathur]

Hi,

Thank you for sharing the dataset.  Please also let me know in which specific selection scenaio are you getting an incorrect result and what the should be the correct result.

[Ashish_Mathur]

Hi,

Not yet.  i will see it on my weekend.  Please also let me know in which specific selection scenaio are you getting an incorrect result and what the should be the correct result.

[Anonymous]

Thanks much Ashish! I have a problem with this one - it is not the problem with the formula what you did but rather something at my end. I have a report level filter that looks only FY19 Q1 for few other things to work and because of that I am getting "Blank" as count of new clients. So essentially, I am looking to see new clients in FY19 Q1. Would you know how best to fix this?

[Ashish_Mathur]

Hi,

I have blogged about my solution to this problem here - Customer analysis by Country and time period.

Hope this helps.

[Ashish_Mathur]

Sorry, i would not know.

[Ashish_Mathur]

You are welcome.

[Anonymous]

Just to add, when "Services Rendered" is added as a filter and a particular Service is selected, the answers should still be correct

[CheenuSing]

Hi @Anonymous,

Have you tried changing the group by order to  DataTable[Service Rendered] and use the count of customer as a measure ?

Cheers

CheenuSing

[Anonymous]

Thanks but that doesn't work.