Find new (non-carryover) clients within date range

[ThaddeusB]

I have a calendar slicer for user input which does not have a relationship to my data table.  User will select a minimum and maximum date to form a range such a 1/1/2018 - 1/31/2018

I have table for client #s with start and end dates.  The same # can have multiple start and end dates.

I want to find "new" clients who started service within the date range.  The complication is that I want to count a client as long as they began service after the start date and did not have a service that was ongoing at the start of the period.  So service in previous periods are ignored.

Some sample data to illustrate:

ClientID	Start date	End date
1	12/30/2017	1/5/2018
1	1/10/2018	1/18/2018
1	1/28/2018	2/2/2018
2	12/1/2017	12/12/2017
2	1/5/2018	1/12/2018
3	1/12/2018	1/19/2018
3	1/27/2018	3/3/2018
4	11/5/2017	1/21/2018
4	2/3/2018	2/10/2018
5	12/20/2017	2/2/2018

With this data, the correct count would be 2 (clients #2 & 3).   

I can filter the table down to relevant services in the period via:

VAR minDate = MIN ( 'Calendar'[Date] )

VAR maxDate = MAX ( 'Calendar'[Date] )

FILTER ( data,  ( data[Start date] <= maxDate && data[End date] >= minDate ) )

but can't seem to figure out how to get that filter into an expression that gets the calculation I want.

[v-yuta-msft]

Hi ThaddeusB,

"I want to find "new" clients who started service within the date range. The complication is that I want to count a client as long as they began service after the start date and did not have a service that was ongoing at the start of the period. So service in previous periods are ignored." 

<--- Your requirement is not so clear. For example, could you please clarify why clientID 1 doesn't meet the condition. Could you give more details about the logic?

Regards,

Jimmy Tao

[ThaddeusB]

---

@v-yuta-msft wrote:

Hi ThaddeusB,

"I want to find "new" clients who started service within the date range. The complication is that I want to count a client as long as they began service after the start date and did not have a service that was ongoing at the start of the period. So service in previous periods are ignored." 

<--- Your requirement is not so clear. For example, could you please clarify why clientID 1 doesn't meet the condition. Could you give more details about the logic?

Regards,

Jimmy Tao

---

@v-yuta-msft  Thanks for your reply.  I would be happy to (try to) clarify.  A "new" client is one who started a service after the start date of the period and didn't also have a service ongoing at the start of the period.  Client 1 does not qualify as new in January because he had a service from 12/30/17-1/5/18.

One possible logic would be 

1) Filter down to services in the selected period (using filter I posted or something similar):

ClientID	Start date	End date
1	12/30/2017	1/5/2018
1	1/10/2018	1/18/2018
1	1/28/2018	2/2/2018
2	1/5/2018	1/12/2018
3	1/12/2018	1/19/2018
3	1/27/2018	3/3/2018
4	11/5/2017	1/21/2018
5	12/20/2017	2/2/2018

2) Find the minimum entry date by client in the filtered set

ClientID	Start date	End date
1	12/30/2017	1/5/2018
2	1/5/2018	1/12/2018
3	1/12/2018	1/19/2018
4	11/5/2017	1/21/2018
5	12/20/2017	2/2/2018

3) Count the number of minimum entry dates on or after the selected period start date.

ClientID	Start date	End date
2	1/5/2018	1/12/2018
3	1/12/2018	1/19/2018

[ThaddeusB]

So I came up with the following that appears to work:

VAR minDate = MIN('Calendar'[Date])

VAR maxDate = MAX('Calendar'[Date])

RETURN

CALCULATE(

COUNT(Data[ClientId]),

FILTER(

NATURALLEFTOUTERJOIN(Data,

SUMMARIZE(

FILTER(

Data,

Data[Start date] <= maxDate && Sheet1[End date] >= minDate

),

Data[Client Id],

"MinD",

MIN(Data[Start date])

)

), [minD]=[Start date] && [minD])>=minDate

)

)

Let me know if you know a more efficient way to achieve the goal.