Trazar turnos de trabajo programados (con solapamiento y diferentes franjas horarias) en un gráfico de 24 horas

[Syndicate_Admin]

Hola Comunidad,

Estoy tratando de trazar los turnos de trabajo programados (con solapamiento y diferentes franjas horarias) para los empleados ove el intervalo de tiempo de 24 horas, idealmente me gustaría mostrar en cada ranura de una hora cuántos empleados estarían disponibles:

Datos de muestra

EMP_ID	Shift_Start Hora	Shift_End Hora
EMP1	2	8
EMP2	5	12
EMP3	14	19
EMP4	19	23
EMP5	3	10
EMP6	8	10
EMP7	9	13
EMP8	3	16
EMP9	16	20
EMP10	22	24

Salida de muestra deseada:

[Syndicate_Admin]

Hola @smpa01 , A continuación se puede encontrar un ejemplo de datos con la salida deseada

arrived_at	completed_at
22/01/2021 00:00	22/01/2021 01:02
22/01/2021 00:03	22/01/2021 01:18
22/01/2021 00:42	22/01/2021 02:15
22/01/2021 00:44	22/01/2021 02:11
22/01/2021 00:52	22/01/2021 05:33
22/01/2021 00:59	22/01/2021 04:05
22/01/2021 01:07	22/01/2021 02:00
22/01/2021 01:22	22/01/2021 04:11
22/01/2021 01:26	22/01/2021 10:40
22/01/2021 01:37	22/01/2021 02:37
22/01/2021 02:15	22/01/2021 05:22
22/01/2021 02:27	22/01/2021 03:09
22/01/2021 02:50	22/01/2021 04:32
22/01/2021 03:02	22/01/2021 03:21
22/01/2021 03:08	22/01/2021 07:00
22/01/2021 03:15	22/01/2021 04:20
22/01/2021 03:40	22/01/2021 04:17
22/01/2021 03:44	22/01/2021 06:03
22/01/2021 03:45	22/01/2021 06:07
22/01/2021 03:58	22/01/2021 06:03
22/01/2021 04:06	22/01/2021 04:30
22/01/2021 04:06	22/01/2021 13:29
22/01/2021 04:18	22/01/2021 05:49
22/01/2021 04:43	22/01/2021 06:36
22/01/2021 04:53	22/01/2021 06:01
22/01/2021 05:09	22/01/2021 06:13
22/01/2021 05:12	22/01/2021 05:43
22/01/2021 05:21	22/01/2021 07:12
22/01/2021 05:27	22/01/2021 06:02
22/01/2021 05:49	22/01/2021 13:02
22/01/2021 05:53	22/01/2021 06:30
22/01/2021 06:02	22/01/2021 06:31
22/01/2021 06:03	22/01/2021 13:03
22/01/2021 06:04	22/01/2021 07:02
22/01/2021 06:12	22/01/2021 06:29
22/01/2021 06:30	22/01/2021 07:44
22/01/2021 06:30	22/01/2021 14:33
22/01/2021 06:34	22/01/2021 07:35
22/01/2021 06:34	22/01/2021 06:35
22/01/2021 06:35	22/01/2021 08:28
22/01/2021 06:43	22/01/2021 09:27
22/01/2021 06:45	22/01/2021 09:03
22/01/2021 06:45	22/01/2021 09:59
22/01/2021 06:50	22/01/2021 07:22
22/01/2021 06:51	22/01/2021 07:01
22/01/2021 06:52	22/01/2021 09:57
22/01/2021 06:53	22/01/2021 09:02
22/01/2021 06:53	22/01/2021 07:31
22/01/2021 07:08	22/01/2021 08:04
22/01/2021 07:12	22/01/2021 08:48
22/01/2021 07:15	22/01/2021 08:30
22/01/2021 07:24	22/01/2021 07:28
22/01/2021 07:28	22/01/2021 08:31
22/01/2021 07:37	22/01/2021 10:03
22/01/2021 07:41	22/01/2021 09:00
22/01/2021 07:42	22/01/2021 11:13
22/01/2021 07:49	22/01/2021 10:01
22/01/2021 07:52	22/01/2021 09:05
22/01/2021 08:00	22/01/2021 12:32
22/01/2021 08:01	22/01/2021 10:04
22/01/2021 08:05	22/01/2021 09:18
22/01/2021 08:05	22/01/2021 09:31
22/01/2021 08:07	22/01/2021 12:20
22/01/2021 08:12	22/01/2021 09:37
22/01/2021 08:12	22/01/2021 10:27
22/01/2021 08:18	22/01/2021 15:01
22/01/2021 08:21	22/01/2021 10:01
22/01/2021 08:24	22/01/2021 09:29
22/01/2021 08:38	22/01/2021 09:30
22/01/2021 08:47	22/01/2021 11:11
22/01/2021 08:47	22/01/2021 09:21
22/01/2021 08:53	22/01/2021 09:22
22/01/2021 09:03	22/01/2021 09:35
22/01/2021 09:06	22/01/2021 10:31
22/01/2021 09:11	22/01/2021 10:08
22/01/2021 09:13	22/01/2021 11:21
22/01/2021 09:24	22/01/2021 11:05
22/01/2021 09:32	22/01/2021 10:01
22/01/2021 09:33	22/01/2021 12:08
22/01/2021 09:39	22/01/2021 09:45
22/01/2021 09:51	22/01/2021 13:04
22/01/2021 10:00	22/01/2021 10:04
22/01/2021 10:03	22/01/2021 11:16
22/01/2021 10:26	22/01/2021 11:06
22/01/2021 10:38	22/01/2021 12:36
22/01/2021 10:44	22/01/2021 10:45
22/01/2021 10:55	22/01/2021 12:07
22/01/2021 10:57	22/01/2021 10:58
22/01/2021 11:07	22/01/2021 12:38
22/01/2021 12:24	22/01/2021 13:12
22/01/2021 12:25	22/01/2021 12:35
22/01/2021 12:27	22/01/2021 14:01
22/01/2021 12:29	22/01/2021 15:36
22/01/2021 12:31	22/01/2021 22:29
22/01/2021 12:39	22/01/2021 12:40
22/01/2021 13:01	22/01/2021 13:19
22/01/2021 13:04	22/01/2021 14:20
22/01/2021 13:05	22/01/2021 14:10
22/01/2021 13:07	22/01/2021 15:05
22/01/2021 13:24	22/01/2021 15:02
22/01/2021 13:26	22/01/2021 15:54
22/01/2021 13:48	22/01/2021 16:03
22/01/2021 13:49	22/01/2021 15:27
22/01/2021 14:01	22/01/2021 14:34
22/01/2021 14:07	22/01/2021 14:19
22/01/2021 14:44	22/01/2021 19:14
22/01/2021 14:51	22/01/2021 17:05
22/01/2021 14:59	22/01/2021 23:33
22/01/2021 15:06	22/01/2021 16:01
22/01/2021 15:23	22/01/2021 16:25
22/01/2021 15:24	22/01/2021 16:13
22/01/2021 15:30	22/01/2021 19:00
22/01/2021 15:36	22/01/2021 18:02
22/01/2021 15:56	22/01/2021 17:11
22/01/2021 15:59	22/01/2021 16:35
22/01/2021 16:00	22/01/2021 18:01
22/01/2021 16:25	22/01/2021 19:24
22/01/2021 16:42	22/01/2021 17:04
22/01/2021 16:43	22/01/2021 21:30
22/01/2021 17:04	22/01/2021 20:12
22/01/2021 17:13	22/01/2021 20:02
22/01/2021 17:30	22/01/2021 18:02
22/01/2021 17:35	22/01/2021 19:39
22/01/2021 17:37	22/01/2021 19:01
22/01/2021 17:57	22/01/2021 20:01
22/01/2021 18:00	22/01/2021 18:31
22/01/2021 18:05	22/01/2021 20:01
22/01/2021 18:06	22/01/2021 18:08
22/01/2021 18:11	22/01/2021 20:01
22/01/2021 18:17	22/01/2021 18:33
22/01/2021 18:28	22/01/2021 19:01
22/01/2021 18:33	22/01/2021 19:46
22/01/2021 18:58	22/01/2021 19:15
22/01/2021 18:59	22/01/2021 19:21
22/01/2021 18:59	22/01/2021 21:00
22/01/2021 19:00	22/01/2021 19:48
22/01/2021 19:12	22/01/2021 19:34
22/01/2021 19:15	22/01/2021 21:28
22/01/2021 19:23	22/01/2021 20:30
22/01/2021 19:56	22/01/2021 21:00
22/01/2021 20:01	22/01/2021 20:52
22/01/2021 20:06	22/01/2021 20:30
22/01/2021 20:22	22/01/2021 21:53
22/01/2021 20:30	22/01/2021 23:35
22/01/2021 20:31	22/01/2021 20:32
22/01/2021 20:36	22/01/2021 21:38
22/01/2021 20:59	22/01/2021 21:33
22/01/2021 21:00	22/01/2021 21:16
22/01/2021 21:04	22/01/2021 23:01
22/01/2021 21:04	22/01/2021 21:14
22/01/2021 21:06	22/01/2021 22:02
22/01/2021 21:10	22/01/2021 23:08
22/01/2021 21:27	22/01/2021 23:02
22/01/2021 21:51	22/01/2021 22:58
22/01/2021 21:52	23/01/2021 00:03
22/01/2021 21:53	23/01/2021 01:13
22/01/2021 22:21	23/01/2021 00:29
22/01/2021 22:25	22/01/2021 22:31
22/01/2021 22:28	23/01/2021 01:32
22/01/2021 22:30	22/01/2021 22:31
22/01/2021 22:53	23/01/2021 00:01
22/01/2021 22:53	23/01/2021 00:00
22/01/2021 23:16	22/01/2021 23:24
22/01/2021 23:33	23/01/2021 00:38
23/01/2021 00:06	23/01/2021 02:02
23/01/2021 00:47	23/01/2021 01:53
23/01/2021 00:48	23/01/2021 02:17
23/01/2021 00:53	23/01/2021 02:10
23/01/2021 00:59	23/01/2021 02:01
23/01/2021 01:01	23/01/2021 02:55
23/01/2021 01:06	23/01/2021 01:38
23/01/2021 01:18	23/01/2021 03:10
23/01/2021 01:37	23/01/2021 02:34
23/01/2021 01:58	23/01/2021 02:18
23/01/2021 02:21	23/01/2021 03:01
23/01/2021 02:44	23/01/2021 02:45
23/01/2021 02:55	23/01/2021 04:01
23/01/2021 03:01	23/01/2021 04:11
23/01/2021 03:02	23/01/2021 03:29
23/01/2021 03:29	23/01/2021 03:49
23/01/2021 03:34	23/01/2021 04:00
23/01/2021 03:46	23/01/2021 04:18
23/01/2021 04:22	23/01/2021 04:23

[Syndicate_Admin]

Hola @smpa01 ,

Gracias por la pronta respuesta, la solución que proporcionó funcionó bien hasta que me enteré de que tenemos turnos que abarcan más de dos días (es decir, el turno comienza a las 10:00 PM y termina al día siguiente 03:00 AM) el cálculo se rompe cuando tengo tales entradas. ¿tienes alguna idea de cómo ir alrededor de este.

Gracias, Ali

[Syndicate_Admin]

@AliZreik se puede lograr de esta manera

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUUrNLQBRhvrGhvpGBoaGCoZGVgYGQKTg6AsSN8IqEasTrWQE0Q2iDIHaDYCqjAwUsOo2MkQWj40FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [index = _t, emp = _t, Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type datetime}, {"Column2", type datetime}}),
    #"Inserted Time Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each [Column2] - [Column1], type duration),
    #"Added Custom" = Table.AddColumn(#"Inserted Time Subtraction", "Custom", each Duration.TotalHours([Subtraction])),
    #"Inserted Year" = Table.AddColumn(#"Added Custom", "Year", each Date.Year([Column1]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Column1]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Month", "Day", each Date.Day([Column1]), Int64.Type),
    #"Inserted Hour" = Table.AddColumn(#"Inserted Day", "Hour", each Time.Hour([Column1]), Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Inserted Hour", "Custom.1", each List.DateTimes(#datetime([Year],[Month],[Day],[Hour],0,0),[Custom],#duration(0,1,0,0))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Subtraction", "Custom", "Year", "Month", "Day", "Hour"}),
    #"Expanded Custom.1" = Table.ExpandListColumn(#"Removed Columns", "Custom.1")
in
    #"Expanded Custom.1"

[Syndicate_Admin]

Hola @smpa01 ,

Apreciado volver a mi respuesta, a continuación se muestra una instantánea de los datos de muestra (1st snapchot), estoy empezando con dos columnas datetime, se puede ver a continuación un snaphot (2o) del resultado después de aplicar el código que ha enviado, no estoy seguro de por qué las columnas originales se establecieron en valores mew con 1 minuto de diferencia (casi se ven como todas las filas tomaron valores de la primera fila en datos originales)

En cuanto a la columna resultante, a diferencia de la primera vez, realmente no entendí cómo la nueva columna generada me va a trazar los turnos programados (especialmente los que abarcan 2 días) de la misma manera que usted indicó en la primera respuesta.

Tal vez sea obvio, pero realmente no puedo evitarlo. ¡Apreciamos el apoyo!

Saludos, Ali

[Syndicate_Admin]

No @AliZreik ,

Primero vaya a Power query>create a custom column as as abajo:

Custom=let 
st = [arrived_at],
end = [completed_at],
l = List.Generate(
    ()=>st,
    each _<=end,
    each _+#duration(0,0,1,0)
),
res=List.Select(l,(x)=>Time.Minute(x)=0 and Time.Second(x)=0)
in 
res

A continuación, vuelva a la vista de datos, cree una columna calculada como se indica a continuación:

Column = HOUR('Table'[Custom])

Y una medida como se indica a continuación:

Measure = CALCULATE(COUNT('Table'[Column]),FILTER(ALL('Table'),'Table'[Column]=MAX('Table'[Column])&&'Table'[Column]<>BLANK()))

Finalmente verá:

Para el archivo .pbix relacionado, pls ver adjunto.

Saludos
Kelly

¿He respondido a tu pregunta? ¡Marca mi puesto como una solución!

[Syndicate_Admin]

Estimado @v-kelly-msft ,

He probado el código de Power Query que ha proporcionado, pero la nueva columna personalizada creada es en su mayoría valores nulos, no estoy seguro de lo que he hecho mal. Incluso puedo ver que en su archivo .pbix, que cada vez que el turno comienza y termina en la misma hora, el resultado es nulo, mientras que tiene que ser exactamente la misma hora (es decir, el turno comienza 22/01/2021 03:02:00 y termina el 22/01/2021 03:21:00) sho returnud 3.

Estoy adjuntando instantáneas de los resultados que obtuve después de aplicar el mismo código que tiene.

Gracias, Ali

[Syndicate_Admin]

No @AliZreik ,

He encontrado que ha agregado un paso de "Ordenar filas", intente quitar el paso y compruebe si los valores siguen siendo nulos.

Saludos
Kelly

¿He respondido a tu pregunta? ¡Marca mi puesto como una solución!

[Syndicate_Admin]

Estimado @v-kelly-msft

No creo que este sea el problema, he eliminado la clasificación y todavía tiene el mismo problema (comprobar adjunto)

Gracias, Ali

[Syndicate_Admin]

No @AliZreik ,

Volvamos al paso de "tipo cambiado", compruebe si devuelve valores en blanco.

Si es así, quite el paso e inserte los pasos a continuación:

Saludos
Kelly

¿He respondido a tu pregunta? ¡Marca mi puesto como una solución!

[Syndicate_Admin]

Estimado @v-kelly-msft ,

He aplicado el tipo de cambio usando la configuración regional como usted ha indicado, todavía estoy recibiendo valores nulos en la columna personalizada recién agregada (comprobar adjunta)

Gracias, Ali

[Syndicate_Admin]

No @AliZreik ,

A continuación, compruebe todos los pasos del campo de consultas correctos para ver si hay un paso (antes del último) que devuelve valores nulos o de error.

Saludos
Kelly

¿He respondido a tu pregunta? ¡Marca mi puesto como una solución!

[Syndicate_Admin · Accepted solution]

Hola @v-kelly-msft @smpa01 ,

He llegado a una solución potenial de cuando el intervalo de cambio de un día a otro, el código va así:

#"Added Custom6" = Table.AddColumn(#"Removed Columns", "scheduled_arrived_time_h", cada Time.Hour ([scheduled_arrive_time])),
#"Added Custom1" = Table.AddColumn(#"Added Custom6", "scheduled_completed_time_h", cada Time.Hour ([scheduled_completed_time])),
#"Added Custom7" = Table.AddColumn(#"Added Custom1", "Hours_Scheduled", cada uno si [scheduled_arrived_time_h] > [scheduled_completed_time_h] luego List.Combine ({List.Numbers ([scheduled_arrived_time_h],24 - [scheduled_arrived_time_h]),List.Numbers (0,[scheduled_completed_time_h]+1)}) else List.Numbers ([scheduled_arrived_time_h],[scheduled_completed_time_h] - [scheduled_arrived_time_h] + 1)),
#"Hours_Scheduled expandido" = Table.ExpandListColumn(#"Added Custom7", "Hours_Scheduled"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Hours_Scheduled",{{"Hours_Scheduled", Int64.Type}})

y el resultado sería así:

Gracias, Ali

[Syndicate_Admin]

Estimado @v-kelly-msft , @smpa01

He retrocedido un paso a la vez, nada devuelve error o null, hasta que aplico los pasos que ha proporcionado. Señalé que incluso en su archivo, cuando el turno comienza y termina en la misma hora, está devolviendo null en su archivo (¿es eso previsto?)

Estoy adjuntando de nuevo una instantánea de los datos en mi mano, no puedo evitar por qué a veces devuelve null y a veces devuelve un valor.

Gracias, Ali

[Syndicate_Admin]

¿Puede darme un representante de datos de muestra con capacidad de copiar y pegar de su problema con una columna de salida final claramente definida.

[Syndicate_Admin]

@AliZreik Se puede hacer fácilmente alterando la tabla en PQWRY

let
    Source = Web.BrowserContents("https://community.powerbi.com/t5/Desktop/Plot-scheduled-work-shifts-with-overlap-and-different-time-slot/m-p/1617671#M652662"),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE:nth-child(7) > * > TR > :nth-child(1)"}, {"Column2", "TABLE:nth-child(7) > * > TR > :nth-child(2)"}, {"Column3", "TABLE:nth-child(7) > * > TR > :nth-child(3)"}}, [RowSelector="TABLE:nth-child(7) > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"EMP_ID", type text}, {"Shift_Start Hour", Int64.Type}, {"Shift_EndHour", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Hours", each {[Shift_Start Hour]..[Shift_EndHour]}),
    #"Expanded Hours" = Table.ExpandListColumn(#"Added Custom", "Hours"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Hours",{{"Hours", Int64.Type}})
in
    #"Changed Type1"

y luego usa Horas en el Eje X.

Sin embargo, no estoy seguro de DAX.