Showing results for 
Search instead for 
Did you mean: 
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




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

Start Year         


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)


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



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

Ashish Mathur

Helpful resources

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!


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