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
| 1 | 2003-02-02 | 2004-02-02 | A |
| 2 | 2005-02-02 | 2010-02-02 | B |
| 3 | 2002-02-02 | 2005-02-02 | C |
| 4 | 2002-02-02 | 2003-02-02 | B |
| 5 | 2003-02-02 | 2007-02-02 | B |
| 6 | 2001-02-02 | 2006-02-02 | A |
| 7 | 2005-02-02 | 2006-02-02 | C |
| 8 | 2002-02-02 | 2006-02-02 | B |
| 1 | 2009-02-02 | 2010-02-02 | C |
| 2 | 2001-02-02 | 2002-02-02 | A |
| 4 | 2003-02-02 | 2006-02-02 | C |
| 5 | 2005-02-02 | 2010-02-02 | C |
| 6 | 2003-02-02 | 2007-02-02 | C |
| 8 | 2004-02-02 | 2009-02-02 | A |
| 2 | 2001-02-02 | 2002-02-02 | C |
| 3 | 2002-02-02 | 2005-02-02 | B |
| 5 | 2005-02-02 | 2007-02-02 | A |
| 7 | 2001-02-02 | 2004-02-02 | B |
| 3 | 2003-02-02 | 2008-02-02 | A |
| 7 | 2001-02-02 | 2005-02-02 | A |
For the desired results I would expect the results to show as follows:
| Start Year | | | | | | | | | |
| 2001 | 2002 | 2003 | 2004 | 2005 | 2006 | 2007 | 2008 | 2009 | 2010 |
| 3 | 3 | 2 | 0 | 1 | 0 | 0 | 0 | 1 | 0 |
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)
