Check if date is between multiple dates interval per client

[claus79]

Hello everyone! Kindly need your expert help to solve this problem in PBI. 
I have 2 tables from 2 separate sources, the link between them is ClientId column.
Table1: a list of clients with applications and date of application for each of them (each client can have more than 1 application).
Table2: a list of clients with contracts (each client can have more than 1 contract), for each contract we have a start date and an end date, between these dates the contract is active (if there is no EndDate then the contract is currently active), after it is closed the contract is inactive.
I need to add a column in Table1 named ClientType with values: new, active, inactive. I filled in the column with the expected results (based on the examples given in the 2 tables).
• new => client is not in Table2 or the date of application is before the earliest start date for that client in Table2
• active => client is in Table2 and at the date of application it has at least 1 active contract
• inactive => client is in Table2 and at the date of application it has no active contracts

How can I do this? Should I avoid the many to many relationships and how?

Thank you very much for your help! 

Table1
ClientId	AppId	AppdDate	CLIENT TYPE
100001	20000000	1/1/2006	new
100001	20000001	1/1/2012	inactive
100004	20000002	1/1/2017	new
100004	20000003	7/1/2018	active
100001	20000004	9/1/2018	active
100004	20000005	7/1/2019	inactive
100003	20000006	3/1/2020	active
100001	20000008	7/1/2020	active
100002	20000009	8/1/2020	inactive
100005	20000010	8/1/2020	new
100006	20000011	9/1/2020	new

Table2
ClientId	ContractId	StartDate	EndDate
100001	150050	9/3/2007	11/30/2011
100001	160060	4/10/2013	8/19/2015
100001	170070	10/17/2014	5/31/2017
100001	180080	7/2/2015	5/31/2016
100001	190090	9/30/2015	8/1/2018
100001	200100	9/2/2016	11/30/2017
100001	210110	11/14/2017	6/28/2019
100001	220120	6/25/2019
100002	230130	1/1/2020	6/1/2020
100003	240140	2/1/2019
100004	250150	5/1/2018	5/1/2019
100004	260160	7/1/2020
100007	270170	9/1/2020

[v-lionel-msft · Accepted solution]

Hi @claus79 ,

Please refer to my .pbix file.

Step1, Merge the two tables 'Edit Query'.

2. Create measure and calculated column.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[claus79]

Thank you for your help! I appreciate it!  

[amitchandak]

@claus79 , new column is Table 1

New column =
var _1 = countx(filter(Table2, table2[ClientId] = Table1[ClientID]),Table2[ClientID])
var _2 = countx(filter(Table2, table2[ClientId] = Table1[ClientID] && isblank(Table2[End Date])),Table2[ClientID])
return
if(isblank(_1), "New", if(isblank(_2),"inactive","active"))

[claus79]

Hello and thank you for your quick reply! Unfortunately, the problem with this formula is that it doesn't get the correct answer for clients with more than 1 contract in Table2. The split has to consider the date of application from Table1 meaning that this check is done at application level not client level : for each row we have to look at the AppDate as well and see if at that date for the same client there are active/ inactive contracts.

For ex ,first line from Table 1 (ClientId 100001, AppId 20000000, AppDate 1/1/2006) , ClientType is new because AppDate is before the earliest StartDate for the same client in Table 2 (1/1/2006 < 9/3/2007) 

But next line from Table 1 (ClientId 100001, AppId 20000001, AppDate 1/1/2012) , ClientType is inactive because at the date of application (1/1/2012) there is no active loan for that client in Table 2 (a contract ended in 11/30/2011 and the next one has started 4/10/2013) 

I would be very grateful if you can think of a solution for this. Thank you very much!