Compruebe si la fecha está entre el intervalo de varias fechas por cliente

[claus79]

¡Hola a todos! Por favor, necesite su ayuda experta para resolver este problema en PBI.
Tengo 2 tablas de 2 orígenes independientes, el vínculo entre ellos es la columna ClientId.
Tabla 1: una lista de clientes con aplicaciones y fecha de aplicación para cada uno de ellos (cada cliente puede tener más de 1 aplicación).
Tabla 2: una lista de clientes con contratos (cada cliente puede tener más de 1 contrato), para cada contrato tenemos una fecha de inicio y una fecha de finalización, entre estas fechas el contrato está activo (si no hay EndDate entonces el contrato está actualmente activo), después de que se cierra el contrato está inactivo.
Necesito agregar una columna en Table1 denominada ClientType con valores: new, active, inactive. He rellenado la columna con los resultados esperados (basados en los ejemplos dados en las 2 tablas).
• el nuevo cliente no está en la Tabla 2 o la fecha de solicitud es anterior a la fecha de inicio más temprana para ese cliente en el Cuadro 2
• activo > cliente está en la Tabla2 y en la fecha de aplicación tiene al menos 1 contrato activo
• inactivo > cliente está en el Cuadro2 y en la fecha de aplicación no tiene contratos activos

¿Cómo puedo hacer esto? ¿Debo evitar las muchas a muchas relaciones y cómo?

¡Muchas gracias por su ayuda!

Tabla 1
ClientId	Appid	AppdDate	TIPO DE CLIENTE
100001	20000000	1/1/2006	Nuevo
100001	20000001	1/1/2012	Inactivo
100004	20000002	1/1/2017	Nuevo
100004	20000003	7/1/2018	Activo
100001	20000004	9/1/2018	Activo
100004	20000005	7/1/2019	Inactivo
100003	20000006	3/1/2020	Activo
100001	20000008	7/1/2020	Activo
100002	20000009	8/1/2020	Inactivo
100005	20000010	8/1/2020	Nuevo
100006	20000011	9/1/2020	Nuevo

Tabla2
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]

Hola @claus79 ,

Consulte mi archivo .pbix.

Paso 1, Combine las dos tablas 'Editar consulta'.

2. Cree la medida y la columna calculada.

Saludos
Lionel Chen

Si este post ayuda,entonces considere Aceptarlo como la solución para ayudar a los otros miembros a encontrarlo más rápidamente.

[claus79]

¡Gracias por tu ayuda! ¡Te lo agradezco!

[amitchandak]

@claus79 , la nueva columna es la Tabla 1

Nueva columna ?
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])
devolución
if(isblank(_1), "New", if(isblank(_2),"inactive","active"))

[claus79]

Hola y gracias por su respuesta rápida! Desafortunadamente, el problema con esta fórmula es que no obtiene la respuesta correcta para los clientes con más de 1 contrato en la Tabla2. La división tiene que tener en cuenta la fecha de aplicación de la Tabla1 lo que significa que esta comprobación se realiza a nivel de aplicación no nivel de cliente: para cada fila tenemos que mirar el AppDate también y ver si en esa fecha para el mismo cliente hay contratos activos / inactivos.

Para ex ,primera línea de la tabla 1 (ClientId 100001, AppId 20000000, AppDate 1/1/2006) , ClientType es nuevo porque AppDate está antes de la fecha de inicio más temprana para el mismo cliente en la Tabla 2 (1/1/2006 < 9/3/2007)

Pero la siguiente línea de la Tabla 1 (ClientId 100001, AppId 20000001, AppDate 1/1/2012) , ClientType está inactivo porque en la fecha de solicitud (1/1/2012) no hay ningún préstamo activo para ese cliente en el Cuadro 2 (un contrato terminado en 11/30/2011 y el siguiente ha comenzado 4/10/2013)

Le agradecería mucho que se le ocurra una solución para esto. ¡Muchas gracias!