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.
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.
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.