Retención: Los retornados menos los completores calculados

[Mitig]

Estoy luchando con una medida de retención donde necesito excluir "completores" del resultado. Estos completadores están dentro de un intervalo de tiempo específico (08-20-2018 a 08-19-2019). Hasta ahora, soy capaz de calcular una cantidad de retención básica, pero quitar el grupo "Completado" de la mezcla me está desechando.

La medida que necesito llevar a cabo es básicamente la siguiente:

Recuento de repatriados en Quarter_Code 20 2019 desde el mismo trimestre de 2018 por número de identificación excluyendo a aquellos que"completado"dentro de 08.20.2018 a 08.19.2019. (Consulte la codificación de color a continuación de la tabla.)

A continuación se muestra una tabla de datos de ejemplo con codificación de color y resultados de muestra por debajo de eso.

TABLA1

ID_NUM	año	Quarter_Code	Completed_Date
0001	2018	20
0002	2018	20
0003	2018	20	05-25-2019
0004	2018	30
0006	2018	20	12-31-2018
0007	2018	20
0008	2018	20	05-25-2019
0002	2019	20
0003	2019	20	05-25-2019
0005	2019	20
0001	2019	30
0004	2019	30
0007	2019	20
0008	2018	30	5-25-2019

Codificación de color:

Rojo o Negro - No cuenta como "retenido" ya que id no está presente en el trimestre 20 de 2018 y el trimestre 20 de 2019. (NOTA: 0001 NO se cuenta debido a la devolución en el código de trimestre 30 de 2019 (pero no 20).

Verde - Cuenta estrictamente como "retenido"

Púrpura: cuenta como "Completado" debido a graduarse dentro del plazo de 8.20.2018 a 8.19.2019 . (NOTA: Aunque 0003 regresó en 20 de 2019, este ID solo cuenta como un completor en esta dinámica: NO retenido o ambos retenidos y completados.)

Resultado esperado:

Quarter_Code 20 en 2018 5 (0001, 0002, 0003, 0006, 0007)

Retenido en Quarter_Code 20 en 2019 (pero no completado) 2 (0002 y 0007)

Completado 3 (0003, 0006 y 0008)

Teniendo en cuenta esto, la medida sólo debe devolver 2.

El código DAX que tengo hasta ahora para la medida es el siguiente:

Retained_Excluding_Completed = 
    VAR
        Present_20_2018 = 
            SELECTCOLUMNS(Filter(
                TABLE1, TABLE1[Year] = 2018 && TABLE1[Quarter_Code] = 20),"2018 Rows",TABLE1[ID_NUM])

RETURN

    VAR
        Present_20_2019 = 
            SELECTCOLUMNS(FILTER(
                TABLE1, TABLE1[Year] = 2019 && TABLE1[Quarter_Code] = 20),"2019 Rows",TABLE1[ID_NUM])

RETURN

    VAR
        Completed = 
            SELECTCOLUMNS(FILTER(
                TABLE1, TABLE1[Completed_Date] >= DATE(2018,08,20) && TABLE1[Completed_Date] <= DATE(2019,08,19)),"Completed 2018",TABLE1[ID_NUM])

RETURN

CALCULATE(DISTINCTCOUNT(
    TABLE1[ID_NUM]),
        FILTER(
            TABLE1, STABLE1[ID_NUM]
                IN
                    Present_20_2018),
        FILTER(
            TABLE1, TABLE1[ID_NUM]
                IN
                    Present_20_2019),
        FILTER(TABLE1, [[[ADDITIONAL CODE NEEDED HERE TO EXCLUDE COMPLETERS???]]]...

Verás que yo (con la ayuda de esta comunidad en pasos anteriores) he creado variables para 1) las presentes en Quarter_Code 20 de 2018, 2) las presentes en Quarter_Code 20 de 2019, 3) las que completaron dentro del período de tiempo especificado, y 4) el inicio de una expresión que solo incluye los documentos de identificación retenidos que no se completaron. Claramente, me falta la expresión que EXCLUYE a los completadores de esta medida.

Cualquier ayuda en absoluto es muy apreciado! Además, si estoy yendo sobre esto de una manera completamente incorrecta, cualquier orientación también es apreciada. ¡Salud!

[v-yiruan-msft]

Hola @Mitig ,

El ID devuelto se calculará grupo por año y cuarto de código al igual que la siguiente captura de pantalla?

Saludos

Rena

[Mitig]

@yingyinr - Los documentos de identificación devueltos serían sólo los colores codificados en verde arriba (0002 y 0007). Así que sólo dos identificaciones de la tabla anterior se devolverían como "retenidas".

El púrpura se filtraría independientemente de si estuvieran presentes en ambos códigos de cuarto "20" en 2018 y 2019 porque se completaron. Dado el estado más completo, 0003 sería excluido, a pesar de que estaban presentes para ambos códigos "20" en ambos años.

Los rojos y los negros tampoco serían devueltos, ya que no estaban presentes para ambos códigos de cuartos "20" en ambos años.

¡Disculpas por cualquier confusión, y espero que eso ayude!

[v-yiruan-msft · Accepted solution]

Hola @Mitig ,

Acabo de actualizar su medida como se muestra a continuación:

1. Obtener ID Num para el próximo año y el mismo trimestre

2. Encuentre el número de identificación que existen en el año actual y el próximo año con el mismo trimestre, y excluya ID NUM que ya haya completado

Retained in Quarter_Code = 
var y=2018 
var q=20
var _ID=CALCULATETABLE(VALUES('TABLE1'[ID_NUM]),FILTER(ALL('TABLE1'),'TABLE1'[Year]=y+1&&'TABLE1'[Quarter_Code]=q))
return CALCULATE(DISTINCTCOUNT('TABLE1'[ID_NUM]), FILTER('TABLE1','TABLE1'[ID_NUM] in _ID &&'TABLE1'[Year]=y&&'TABLE1'[Quarter_Code]=q&&ISBLANK('TABLE1'[Completed_Date])))

Saludos

Rena

[Mitig]

@yingyinr - Muchas gracias por esto! Aunque estoy viendo algunos problemas con mi conjunto de datos conectado a SQL, estoy marcando esto como resuelto aquí. Todavía no estoy completamente ahí para mi modelo de datos, pero esto me ha dado algunas noticias sobre cómo solucionar el problema; si no puedo resolver, merecería un nuevo puesto. En cualquier caso, gracias de nuevo!