Cálculo de horas de red / horas de trabajo

Desktop
Un usuario Pregunto ✅

juliusj

Hola,

He estado intentando calcular las horas de la red (es decir, el tiempo de trabajo excluyendo las horas no laborables, los fines de semana y los días festivos). He probado otras soluciones (enumeradas a continuación). Sin embargo, mi conjunto de datos parece ser más complejo.

Dentro de los datos tenemos las siguientes columnas:

Fecha / hora de apertura Fecha / hora de cierre Número de caso Rendimiento esperado
11/2/19 15:24 11/2/19 15:24 1 0
12/02/19 14:21 2 nulo
27/02/19 15:17 27/02/19 15:17 3 0
14/3/19 10:10 a. M. 4 nulo
16/02/19 7:04 p.m. 18/02/19 9:42 a. M. 5 1,7
15/02/19 4:32 p.m. 16/02/19 11:06 a. M. 6 0.966667

Contexto:
Estoy tratando de calcular el tiempo para resolver un caso de atención al cliente. He intentado seguir las siguientes soluciones.

  • https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/mp/651779#M312607
  • https: //community.powerbi.com/t5/Desktop/Calculate-Date-and-Time-difference-considering-the-weekends …

Sin embargo, dado que los casos pueden estar abiertos después del horario comercial, cerrarse después del horario comercial o no estar cerrados todavía, he tenido muchos problemas.

Los requisitos:

  • Día laborable: 8 a. M. – 5 p. M.
  • Fines de semana: sábado y domingo
  • También me gustaría incluir un calendario de vacaciones.
  • Idealmente también funcionaría con una consulta directa[optional]

Cualquier ayuda o dirección será muy apreciada.

Anónimo

@juliusj –

Aquí está la tabla de fechas a la que se hace referencia en la columna calculada. Tenga en cuenta que tiene un atributo de fin de semana para indicar si es un fin de semana. Puede agregar OffDay, que considera tanto los fines de semana como los feriados.

Date = ADDCOLUMNS(CALENDAR(DATE(2018,1,1),DATE(2020,12,31)),"Weekend",IF(WEEKDAY([Date]) IN {1,7}, TRUE(),FALSE()))

Aquí está un Columna calculada. Funciona los fines de semana. La misma lógica podría usarse con Is OffDay en lugar de Weekend. Reemplazaría los siguientes bits:

var start_date_is_weekend = LOOKUPVALUE (‘Fecha'[Weekend],’Fecha'[Date],EN T([Date/Time Opened]))

var end_date_is_weekend = LOOKUPVALUE (‘Fecha'[Weekend],’Fecha'[Date],EN T([Date/Time Closed]))

‘Fecha'[Weekend] = FALSO ()

Hours Difference = 
//Define hours of workday, in seconds
var work_day_begins = 3600 * 9 //9AM
var work_day_ends = 3600 * 17  //5PM
var seconds_in_workday = work_day_ends - work_day_begins

//Check whether start/end dates occurred on a weekend.
var start_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Opened]))
var end_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Closed]))

//Get the seconds from midnight. 
//If it's a weekend, always use the end of the workday value.
//If outside working hours, snap to the start or end of day.
var start_time = TIMEVALUE(FORMAT([Date/Time Opened],"HH:mm:ss"))*86400
var end_time = TIMEVALUE(FORMAT([Date/Time Closed],"HH:mm:ss"))*86400
var start_time_adj = IF(start_date_is_weekend,work_day_ends,MIN(MAX(start_time,work_day_begins),work_day_ends))
var end_time_adj = SWITCH(
    TRUE(),
    ISBLANK(end_date_is_weekend),BLANK(),
    end_date_is_weekend,work_day_ends,
    MIN(MAX(end_time,work_day_begins),work_day_ends)
)

//Find the number of workdays
var day_diff = COUNTROWS(
    FILTER(
        'Date',
        [Date] > INT([Date/Time Opened]) 
        && [Date] <= INT([Date/Time Closed]) 
        && 'Date'[Weekend] = FALSE()
    )
)

//Final calculation:
var time_diff = end_time_adj - start_time_adj
var working_seconds = (day_diff * seconds_in_workday) + time_diff
var working_hours = working_seconds / 3600.00
return IF(ISBLANK([Date/Time Closed]),BLANK(),working_hours)

Anónimo

@juliusj –

Aquí está la tabla de fechas a la que se hace referencia en la columna calculada. Tenga en cuenta que tiene un atributo de fin de semana para indicar si es un fin de semana. Puede agregar OffDay, que considera tanto los fines de semana como los feriados.

Date = ADDCOLUMNS(CALENDAR(DATE(2018,1,1),DATE(2020,12,31)),"Weekend",IF(WEEKDAY([Date]) IN {1,7}, TRUE(),FALSE()))

Aquí está un Columna calculada. Funciona los fines de semana. La misma lógica podría usarse con Is OffDay en lugar de Weekend. Reemplazaría los siguientes bits:

var start_date_is_weekend = LOOKUPVALUE (‘Fecha'[Weekend],’Fecha'[Date],EN T([Date/Time Opened]))

var end_date_is_weekend = LOOKUPVALUE (‘Fecha'[Weekend],’Fecha'[Date],EN T([Date/Time Closed]))

‘Fecha'[Weekend] = FALSO ()

Hours Difference = 
//Define hours of workday, in seconds
var work_day_begins = 3600 * 9 //9AM
var work_day_ends = 3600 * 17  //5PM
var seconds_in_workday = work_day_ends - work_day_begins

//Check whether start/end dates occurred on a weekend.
var start_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Opened]))
var end_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT([Date/Time Closed]))

//Get the seconds from midnight. 
//If it's a weekend, always use the end of the workday value.
//If outside working hours, snap to the start or end of day.
var start_time = TIMEVALUE(FORMAT([Date/Time Opened],"HH:mm:ss"))*86400
var end_time = TIMEVALUE(FORMAT([Date/Time Closed],"HH:mm:ss"))*86400
var start_time_adj = IF(start_date_is_weekend,work_day_ends,MIN(MAX(start_time,work_day_begins),work_day_ends))
var end_time_adj = SWITCH(
    TRUE(),
    ISBLANK(end_date_is_weekend),BLANK(),
    end_date_is_weekend,work_day_ends,
    MIN(MAX(end_time,work_day_begins),work_day_ends)
)

//Find the number of workdays
var day_diff = COUNTROWS(
    FILTER(
        'Date',
        [Date] > INT([Date/Time Opened]) 
        && [Date] <= INT([Date/Time Closed]) 
        && 'Date'[Weekend] = FALSE()
    )
)

//Final calculation:
var time_diff = end_time_adj - start_time_adj
var working_seconds = (day_diff * seconds_in_workday) + time_diff
var working_hours = working_seconds / 3600.00
return IF(ISBLANK([Date/Time Closed]),BLANK(),working_hours)

juliusj

En respuesta a Anónimo

@Anónimo

Muchas gracias por tu ayuda. Conseguí que esta consulta funcionara con un conjunto de datos estáticos.

Sin embargo, en el modo de consulta directa, el siguiente código no funciona:

//Find the number of workdays
var day_diff = COUNTROWS(
    FILTER(
        'Date',
        [Date] > INT([Date/Time Opened]) 
        && [Date] <= INT([Date/Time Closed]) 
        && 'Date'[Weekend] = FALSE()
    )
)

Mensaje de error: la función ‘COUNTROWS’ no está permitida como parte de las expresiones DAX de columna calculadas en modelos DirectQuery.

¿Hay alguna solución para esto o simplemente no tengo suerte?

Anónimo

En respuesta a juliusj

@juliusj: podemos cambiarlo a una medida o volver a trabajar la lógica. Cambiar a medida tiene sentido para DirectQuery.

Hours Difference Measure = 
//Define hours of workday, in seconds
var work_day_begins = 3600 * 9 //9AM
var work_day_ends = 3600 * 17  //5PM
var seconds_in_workday = work_day_ends - work_day_begins

//Put Opened, Closed into variables.
var opened = MAX([Date/Time Opened])
var closed = MAX([Date/Time Closed])

//Check whether start/end dates occurred on a weekend.
var start_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT(opened))
var end_date_is_weekend = LOOKUPVALUE('Date'[Weekend],'Date'[Date],INT(closed))

//Get the seconds from midnight. 
//If it's a weekend, always use the end of the workday value.
//If outside working hours, snap to the start or end of day.
var start_time = TIMEVALUE(FORMAT(opened,"HH:mm:ss"))*86400
var end_time = TIMEVALUE(FORMAT(closed,"HH:mm:ss"))*86400
var start_time_adj = IF(start_date_is_weekend,work_day_ends,MIN(MAX(start_time,work_day_begins),work_day_ends))
var end_time_adj = SWITCH(
    TRUE(),
    ISBLANK(end_date_is_weekend),BLANK(),
    end_date_is_weekend,work_day_ends,
    MIN(MAX(end_time,work_day_begins),work_day_ends)
)

//Find the number of workdays
var day_diff = COUNTROWS(
    FILTER(
        'Date',
        [Date] > INT(opened) 
        && [Date] <= INT(closed) 
        && 'Date'[Weekend] = FALSE()
    )
)

//Final calculation:
var time_diff = end_time_adj - start_time_adj
var working_seconds = (day_diff * seconds_in_workday) + time_diff
var working_hours = working_seconds / 3600.00
return IF(ISBLANK(closed),BLANK(),working_hours)

Sin embargo, esto solo funciona para filas individuales, el total no funciona. Podemos obtener una medida aditiva envolviendo un SUMX:

Hours Difference Measure With Total = SUMX('DateTime Test', [Hours Difference Measure])

Desafortunadamente, es posible que estos no funcionen bien para grandes conjuntos de datos. ¡Me encantaría saber qué tipo de actuación experimentas!

Saludos,

Nathan

juliusj

En respuesta a Anónimo

@Anónimo

La solución es bastante lenta ~ tiempos de carga de 6-7 minutos. Estoy trabajando con un conjunto de datos de 27K filas que crece cada día. Tener totales es muy importante para mi caso de uso. Esperamos medir el desempeño individual y en equipo.

Anónimo

En respuesta a juliusj

@juliusj – Algunas consideraciones de rendimiento:

Tenga 2 tablas de fechas, cada una con una relación con su tabla. Esto requeriría agregar columnas con el tipo de datos Fecha en lugar de Fecha / Hora. La tabla Fecha también podría tener una columna «días laborables relativos» que podría usarse para derivar la diferencia entre ellos, en lugar de contar filas.

También podría tener una tabla de tiempo para cada inicio y fin. Podría tener una columna que mapee todas las horas antes de las horas de trabajo hasta el comienzo de la jornada laboral y todas las horas después de las horas laborales hasta el final de la jornada laboral, y también contener el segundo del día. Nuevamente, cada tabla estaría relacionada con su tabla de hechos. El rendimiento podría mejorarse redondeando al minuto más cercano y teniendo solo 1440 filas en su tabla de tiempos en lugar de 86400.

Las 4 de estas tablas deben construirse en su fuente de datos. La buena noticia es que se pueden construir y pueden ser estáticos.

Este enlace describe las consideraciones de rendimiento.

¡Salud!

Nathan

juliusj

En respuesta a Anónimo

@Anónimo

Solo quiero asegurarme de que te entiendo correctamente. Realizar lo anterior crearía las tablas necesarias para imitar la lógica siguiente:

//Find the number of workdays
var day_diff = COUNTROWS(
    FILTER(
        'Date',
        [Date] > INT([Date/Time Opened]) 
        && [Date] <= INT([Date/Time Closed]) 
        && 'Date'[Weekend] = FALSE()
    )
)

@juliusj – Algunas consideraciones de rendimiento:

Tenga 2 tablas de fechas, cada una con una relación con su tabla. Esto requeriría agregar columnas con el tipo de datos Fecha en lugar de Fecha / Hora. La tabla Fecha también podría tener una columna «días laborables relativos» que podría usarse para derivar la diferencia entre ellos, en lugar de contar filas.

También podría tener una tabla de tiempo para cada inicio y fin. Podría tener una columna que mapee todas las horas antes del horario laboral al comienzo del día laboral y todas las horas posteriores al horario laboral al final del día laboral, y también contener el segundo del día. Nuevamente, cada tabla estaría relacionada con su tabla de hechos. El rendimiento podría mejorarse redondeando al minuto más cercano y teniendo solo 1440 filas en su tabla de tiempos en lugar de 86400.

Anónimo

En respuesta a juliusj

@juliusj – Eso es correcto – En lugar de contar filas, usaría RELATED para recuperar cada número de «día de trabajo relativo» y luego restar uno del otro.

Anónimo

En respuesta a Anónimo

De hecho, TODO el procesamiento podría potencialmente realizarse en la fuente, ya que es un cálculo fila por fila.

@juliusj,

Me gustaría sugerirle que consulte los videos y blogs a continuación para crear sus medidas:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/td-p/367362

Equipo de apoyo de la comunidad _ Jimmy Tao

Si esta publicación ayuda, considere Aceptarla como la solución para ayudar a los demás miembros a encontrarla más rápidamente.

Entradas relacionadas

Conversión de UTC a EST

Desktop

Un usuario Pregunto ✅ Daven Hola, Estoy buscando convertir la columna de fecha y hora UTC al horario de verano EST. ¿Cómo lo convierto en…

Informes de estilo Kanban

Desktop

Un usuario Pregunto ✅ Sachinhitachi Hola a todos, Tengo un requisito en el que el cliente necesita informes de estilo de panel Kanban. Intenté lograrlo…

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *