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.
This is fantastic! So much cleaner and smarter. Thank you. It works very well on my small test file,
I am currently trying to make it work on my big file (200,000 records of clients with products that span decades). I have already tried limiting the data table to just pull dates for the years that I want to chart (2014 to 2018), and it is still "applying the changes" to my query after 20 minutes. I will edit this post to let you know if it works on that much data in the end. Otherwise, maybe I will try to adapt the technique to make it work on a year by year basis rather than date to date.
Sadly the solution doesn't work for me with my big data set because of not enough memory.I tried to adapt the idea to follow the same logic but reduce the data set to years rather than days, however, although size is no longer a problem, I don't get the results I was expecting (perhaps I adapted something wrong)
I made a table with one record for each clientproductyear combination (called ClientProductYear),then adapted the measures as follows and linked the ClientProductYear table to a simple year table called years. Then adapted the measures as follows - without success
Last year of interaction with client prior to the beginning of the active year = CALCULATE(MAX(ClientProductYear[Years]),GENERATESERIES(MINX(ALL('years'),'years'[Year]),MIN('years'[Year])-1))
New Client = if(ISBLANK(COUNTROWS(FILTER(VALUES(ClientProductYear[Client Number]),[Last date of interaction with client prior to the beginning of the active year]<MIN('years'[Year])-1))),0,COUNTROWS(FILTER(VALUES(ClientProductYear[Client Number]),[Last date of interaction with client prior to the beginning of the active year]<MIN('years'[Year])-1)))
I think I have to give up and tell the management team that due to the data size, we cannot count new clients starting if they have already started and ended a client relationship with us in the past (but that despite this we had really wonderful help from the online community). Thanks anyway.
I had not used the GENRATESERIES() function in my formula. I used DATESBETWEEN(). Try and use that. Also, in the Else part of the IF() function, you are grouping by OMA. You should group by Client Number.