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.