cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Counting Starts (how to ignore start dates when a client relationship already exists)

I have a data table with clients, and products. A client can own many different products. Each product has a start and an end date. 

 

As long as the client owns at least one product, the client is considered to be a client. If the client ends all their products, they are no longer a client.  A person might end all products and then purchase new products 2 or 3 years later - becoming a client again.

 

I want to count how many new clients we get in each time period.

 

I can count all the Active clients by using:

Active Client = CALCULATE(DISTINCTCOUNT('Products'[ClientNumber]),
FILTER('Products', ('Products'[StartDate] <= LASTDATE('DIMDate'[Date])
&& 'Products'[EndDate]>= FIRSTDATE('DIMDate'[Date]))))

 

but this formula only tells me how many active clients I have, not how many NEW clients have joined (or how many clients have terminated).

 

I tried making a client table with one record per client and counting on start dates:

New Clients= CALCULATE(COUNTROWS('ClientTable'),
FILTER('ClientTable', ('ClientTable'[FirstClientStartDate] <= LASTDATE('DIMDate'[Date])
&& 'ClientTable'[FirstClientStartDate]>= FIRSTDATE('DIMDate'[Date]))))

 

But this gives me the wrong number because it assumes that a Client can only become a client ONE TIME - and it neglects to show when clients REJOIN

 

I think what I need is a table that shows me EVERY TIME a Client starts or ends a relationship - but I can't figure out how to build this. Since clients can have many products and the products overlap in time, I am not sure how to identify when all products have been stopped within a time frame.

 

I have attached a link to a sample PBIX file with some records for testing.

sample pbix file

 

Or here is a table with sample records

ClientNumberStartDateEndDateProduct

12003-02-022004-02-02A
22005-02-022010-02-02B
32002-02-022005-02-02C
42002-02-022003-02-02B
52003-02-022007-02-02B
62001-02-022006-02-02A
72005-02-022006-02-02C
82002-02-022006-02-02B
12009-02-022010-02-02C
22001-02-022002-02-02A
42003-02-022006-02-02C
52005-02-022010-02-02C
62003-02-022007-02-02C
82004-02-022009-02-02A
22001-02-022002-02-02C
32002-02-022005-02-02B
52005-02-022007-02-02A
72001-02-022004-02-02B
32003-02-022008-02-02A
72001-02-022005-02-02A

 

For the desired results I would expect the results to show as follows:

Start Year         
2001200220032004200520062007200820092010
3320100010

 

Note: 2005 shows 1 because client#2 started again as a new client in 2005 (after having ended all previous products in 2002)

and 2009 shows 1 because client#1 started again as a new client in 2009 (after having ended all previous products in 2004)

11 REPLIES 11
Highlighted

Hi Ashish,

Thanks. Your solution works - but I can't use it because of my computing power. I have 200,000 records and each record represents a product that is owned for many years (decades) so the Data file becomes many millions of records.

 

I did replicate the solution exactly and I am sure it would have worked if my data set were smaller. It did work perfectly on a smaller test set that I tried.

 

What I just posted a few minutes ago with GenerateSeries was my adapted version where I was trying to relate the info by Years rather than days in order to save space (I had already made a smaller dataset which just listed products by year rather than day). Since I was breaking the data down by year long time intervals and matchng it by year, I had switched from Datesbetween to GenerateSeries. The text OMA that you saw was a typo I made in this forum but my actual measure has the correct column name. 

 

Tomorrow I will mark your original solution as the solution - since it answers the question. The problem in my case is that my computer just can't handle the memory demands that the solution requires and I just wanted to play with it a bit longer to see if I can come up with something that will work for my situation.

 

Thanks again

 

Highlighted

You are welcone.  Disheartened to hear that the solution did not work on 200,000 rows.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors