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

1 ACCEPTED SOLUTION

Hi,

 

I think i have solved the problem.  Please download the file from here.

 

Untitled.png


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

View solution in original post

11 REPLIES 11

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors