Cálculo de la duración de episodios del mismo tipo

[Syndicate_Admin]

Tengo una mesa de personas y una mesa de episodios. (1 a muchos)

La tabla de episodios tiene episodios consecutivos (fecha de finalización de la última fecha = fecha de inicio del siguiente episodio), pero puede variar según el tipo.

Identificación de la persona	Tipo	Fecha de inicio	Fecha final
1	a	01/01/2020	02/02/2020
1	b	02/02/2020	03/03/2020
1	a	03/03/2020	04/04/2020
1	a	04/04/2020
2	c	01/01/2020	02/02/2020
2	c	02/02/2020	03/03/2020

Me gustaría saber la duración de un último grupo de episodios del mismo tipo. Cuando la fecha de finalización es nula, debe ser a partir de hoy. También debe excluir la fecha más temprana en la que el tipo es el mismo que el tipo actual, pero ha habido algunos episodios de un tipo diferente en el medio.

Por ejemplo, la persona 1 necesitaría la duración desde el 03/03/2020 hasta hoy

La persona 2 necesitaría una duración del 01/01/2020 al 03/03/2020

Es probable que agregue este valor a una columna calculada en la tabla person

He intentado usar Power Query para hacer un índice agrupado, y creo que esta es la ruta correcta, pero no he podido hacer que funcione.

Espero que esta información sea suficiente, pero por favor hágamelo saber si se requiere más.

Toda ayuda es muy apreciada.

Gracias

[Syndicate_Admin]

Hola @hackcrr , gracias por la respuesta rápida, agregaré más.

Hola @Elscc ,

Los datos de la tabla se muestran a continuación:

Siga estos pasos:
1. Use la siguiente expresión DAX para crear una columna

Index = 
VAR _a = [Start Date]
VAR _b = [Person ID]
VAR _c = COUNTROWS(FILTER('Table','Table'[Person ID] = _b && 'Table'[Start Date] <= _a))
RETURN _c

2. Utilice la siguiente expresión DAX para crear una medida

MEASURE =
VAR _a =
    MAXX (
        SUMMARIZE ( 'Table', [Person ID], "MaxDate", MAX ( 'Table'[Start Date] ) ),
        [MaxDate]
    ) //Maximum start date after grouping by id
VAR _b =
    LOOKUPVALUE (
        'Table'[Type],
        'Table'[Start Date], _a,
        'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
    ) //Type corresponding to the maximum start date
VAR _c =
    SWITCH (
        TRUE (),
        _b = "a",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] = "b" && 'Table'[Start Date] < _a ),
                [Index]
            ),
        //Find the index corresponding to the maximum start date of another type
        _b = "b",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] = "a" && 'Table'[Start Date] < _a ),
                [Index]
            )
    )
VAR _d =
    LOOKUPVALUE (
        'Table'[Index],
        'Table'[Start Date], _a,
        'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
    ) //Index corresponding to the maximum start date
VAR _StartDate =
    IF (
        ISBLANK ( _c ),
        MINX (
            FILTER ( 'Table', 'Table'[Person ID] = SELECTEDVALUE ( 'Table'[Person ID] ) ),
            [Start Date]
        ),
        IF (
            _d - _c = 1,
            _a,
            LOOKUPVALUE (
                'Table'[Start Date],
                'Table'[Index], _c + 1,
                'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
            )
        )
    )
VAR _EndDate =
    IF (
        ISBLANK (
            LOOKUPVALUE (
                'Table'[End Date],
                'Table'[Start Date], _a,
                'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
            )
        ),
        TODAY (),
        LOOKUPVALUE (
            'Table'[End Date],
            'Table'[Start Date], _a,
            'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
        )
    )
RETURN
    DATEDIFF ( _StartDate, _EndDate, DAY )

3. Salida final

[Syndicate_Admin · Accepted solution]

Hola @v-zhouwen-msft

Tengo el código para trabajar haciendo un ajuste en el VAR _c como se muestra a continuación.

Una vez más, le agradezco mucho que se haya tomado el tiempo de ayudar.

MEASURE =
VAR _a =
    MAXX (
        SUMMARIZE ( 'Table', [Person ID], "MaxDate", MAX ( 'Table'[Start Date] ) ),
        [MaxDate]
    ) //Maximum start date after grouping by id
VAR _b =
    LOOKUPVALUE (
        'Table'[Type],
        'Table'[Start Date], _a,
        'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
    ) //Type corresponding to the maximum start date
VAR _c =
    SWITCH (
        TRUE (),
        _b = "a",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] <> "a" && 'Table'[Start Date] < _a ),
                [Index]
            ),
        //Find the index corresponding to the maximum start date of another type
        _b = "b",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] <> "b" && 'Table'[Start Date] < _a ),
                [Index]
            ),
        _b = "c",
            MAXX (
                FILTER ( 'Table', 'Table'[Type] <> "c" && 'Table'[Start Date] < _a ),
                [Index]
            )
    )
VAR _d =
    LOOKUPVALUE (
        'Table'[Index],
        'Table'[Start Date], _a,
        'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
    ) //Index corresponding to the maximum start date
VAR _StartDate =
    IF (
        ISBLANK ( _c ),
        MINX (
            FILTER ( 'Table', 'Table'[Person ID] = SELECTEDVALUE ( 'Table'[Person ID] ) ),
            [Start Date]
        ),
        IF (
            _d - _c = 1,
            _a,
            LOOKUPVALUE (
                'Table'[Start Date],
                'Table'[Index], _c + 1,
                'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
            )
        )
    )
VAR _EndDate =
    IF (
        ISBLANK (
            LOOKUPVALUE (
                'Table'[End Date],
                'Table'[Start Date], _a,
                'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
            )
        ),
        TODAY (),
        LOOKUPVALUE (
            'Table'[End Date],
            'Table'[Start Date], _a,
            'Table'[Person ID], SELECTEDVALUE ( 'Table'[Person ID] )
        )
    )
RETURN
    DATEDIFF ( _StartDate, _EndDate, DAY )

[Syndicate_Admin]

Hola @v-zhouwen-msft Gracias por tomar el tipo para responder.

Creo que esto va a funcionar, pero en mi situación en realidad tengo 3 tipos de "Tipo". Disculpas por no dejarlo claro. ¿Cómo modificaría el VAR _c para tener en cuenta un tercer tipo, como la "c"?

Gracias

[Syndicate_Admin]

¿Alguien más tiene algún consejo?

Gracias de antemano

[Syndicate_Admin]

Hola, @Elscc

En primer lugar, usaremos Power Query para transformar los datos y, a continuación, usaremos DAX para crear una columna calculada para la duración del último conjunto de episodios del mismo género de cada persona. Pasos de Power Query.
1. Ordene por ID de persona y Fecha de inicio.
2. Agregue la columna Índice.
3.Agregue la columna Tipo de último episodio.
4. Agregar columna de grupo.
5. Rellene la columna Grupo.
A continuación se muestra un código M de referencia:

let
    Source = YourSourceTable,
    SortedTable = Table.Sort(Source,{{"Person ID", Order.Ascending}, {"Start Date", Order.Ascending}}),
    AddedIndex = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),
    PreviousType = Table.AddColumn(AddedIndex, "Previous Type", each if [Index] = 0 then null else try AddedIndex[Type]{[Index]-1} otherwise null),
    Group = Table.AddColumn(PreviousType, "Group", each if [Type] = [Previous Type] then null else [Index]),
    FillDownGroup = Table.FillDown(Group,{"Group"})
in
    FillDownGroup

Cree una columna de cálculo de duración, a continuación se muestra un DAX de referencia:

Latest Episode Duration = 
VAR LatestGroup = 
    CALCULATE(
        MAX('Episodes'[Group]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = EARLIER('Episodes'[Person ID])
        )
    )

VAR StartDate = 
    CALCULATE(
        MIN('Episodes'[Start Date]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = EARLIER('Episodes'[Person ID]) &&
            'Episodes'[Group] = LatestGroup
        )
    )

VAR EndDate = 
    CALCULATE(
        MAX('Episodes'[End Date]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = EARLIER('Episodes'[Person ID]) &&
            'Episodes'[Group] = LatestGroup
        )
    )

RETURN
    IF(
        ISBLANK(EndDate),
        DATEDIFF(StartDate, TODAY(), DAY),
        DATEDIFF(StartDate, EndDate, DAY)
    )

Los resultados se muestran a continuación:

Si desea agregarlo como una columna calculada a la tabla Person, el DAX es el siguiente:

Latest Episode Duration = 
VAR PersonID = 'Person'[Person ID]

VAR LatestGroup = 
    CALCULATE(
        MAX('Episodes'[Group]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = PersonID
        )
    )

VAR StartDate = 
    CALCULATE(
        MIN('Episodes'[Start Date]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = PersonID &&
            'Episodes'[Group] = LatestGroup
        )
    )

VAR EndDate = 
    CALCULATE(
        MAX('Episodes'[End Date]),
        FILTER(
            'Episodes',
            'Episodes'[Person ID] = PersonID &&
            'Episodes'[Group] = LatestGroup
        )
    )

RETURN
    IF(
        ISBLANK(EndDate),
        DATEDIFF(StartDate, TODAY(), DAY),
        DATEDIFF(StartDate, EndDate, DAY)
    )

Saludos

hackcrr

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

[Syndicate_Admin]

Hola @hackcrr ,

Gracias por tomarse el tiempo de responder, pero desafortunadamente los pasos de Power Query agregados hacen que la consulta tarde demasiado en cargarse. Se carga a unas 8 filas por segundo, y con el número de filas que tengo tardaría una hora en cargar la consulta. Por lo tanto, no he podido probar las columnas dax para verificar si funciona como necesito.

No sé qué causa los problemas de rendimiento (se carga en unos segundos antes de los cambios) o si hay soluciones alternativas, pero actualmente no puedo usar esta solución.

Gracias

[Syndicate_Admin]

Hola, @Elscc

Puede probar el siguiente código sin crear métricas. Puedes sacar una parte de tu conjunto de datos y probar el enfoque en mis dos respuestas. Vea si puede obtener los resultados esperados.

let
    Source = YourEpisodesTable, // Replace YourEpisodesTable with the name of your Episodes table
    Grouped = Table.Group(Source, {"Person ID", "Type"}, {{"Grouped", each _, type table}}),
    AddedCustom = Table.AddColumn(Grouped, "Duration", each 
        let
            Type = [Type],
            Episodes = [Grouped],
            LastEpisode = List.Last(Episodes),
            LastEndDate = if LastEpisode[End Date] = null then Date.From(DateTime.LocalNow()) else LastEpisode[End Date],
            PreviousEpisodes = List.RemoveLastN(Episodes, 1),
            EarliestDifferentTypeDate = List.First(List.Select(PreviousEpisodes, each _[Type] <> Type), each true),
            Duration = Duration.Days(Duration.From(LastEpisode[Start Date] - (if EarliestDifferentTypeDate <> null then EarliestDifferentTypeDate[End Date] else Episodes{0}[Start Date])))
        in
            Duration
    ),
    Expanded = Table.ExpandTableColumn(AddedCustom, "Grouped", {"Person ID", "Type", "Start Date", "End Date"}, {"Person ID", "Type", "Start Date", "End Date"}),
    RemovedColumns = Table.RemoveColumns(Expanded, {"Grouped"})
in
    RemovedColumns

Saludos

hackcrr

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

[Syndicate_Admin]

Hola @hackcrr ,

Gracias de nuevo por su tiempo. He copiado su código en el editor avanzado, pero obtengo algunos errores.
El primer error es que el ID de persona y el tipo son nombres duplicados, esto ocurre dentro del paso "Expandido", pero no es un problema, ya que puedo cambiarlos.

Después de esto, obtengo un error:

Esto parece estar en el paso "RemovedColumns", pero si elimino esto, obtengo errores para todos los valores de "Duración":

Espero que esto ayude a solucionar problemas. Una vez más, le agradezco que se haya tomado el tiempo de ayudarme.

Gracias