Diferencia entre las columnas DateTime (al minuto) para los días laborables y el tiempo

[samhunter]

Hola, he estado luchando con este problema por un tiempo ahora y no han sido capaces de resolverlo o encontrar una solución publicada aquí (disculpas si me he perdido). No puedo creer que sea el primero en intentar hacer esto.

Me gustaría contar los minutos entre las 2 columnas DateTime, pero solo durante los días y horas laborables (por ejemplo, 08:30:00 a 05:30:00).

Tengo una tabla [Datos] con 2 columnas de fecha [Open DateTime] y [Closed DateTime]. Tengo un calendario [Fecha] con días laborables [IsWorkingDay] (indicado por 1 & 0) y una tabla de tiempo [Time] con [IsWorkingTime] (indicado por 1 & 0) - tabla de tiempo es al minuto.

Mi enfoque ha sido calcular el número de días en 1 columna (sumando IsWorkingDay) y luego el número de minutos (sumando IsWorkingTime) y luego agregar las 2 columnas juntas. Esto funciona excepto que el cálculo de tiempo falla cuando OpenTime es posterior a ClosedTime.

La solución de cierre que he encontrado es esta (https://community.powerbi.com/t5/Desktop/Calculate-Hours-between-dates-for-only-business-hours/td-p/...) pero la volumn de datos para obtener esto hasta el minuto es masiva por lo que me gustaría tratar de encontrar una solución utilizando las tablas de búsqueda.

¿Alguien tiene alguna idea?

[samhunter]

Tablas de ejemplo

Fecha de apertura	Fecha de cierre
24/02/2019 20:26	01/04/2020 17:33
15/08/2019 09:59	28/08/2019 17:03
15/08/2019 10:21	27/08/2019 15:58
19/08/2019 15:47	20/08/2019 10:16
19/08/2019 16:41	10/10/2019 12:28

hora	IsWorkingTime
00:00	0
00:01	0
00:02	0

Fecha	IsWorkingDay
23/02/2020	0
24/02/2020	1
25/02/2020	1
26/02/2020	1
27/02/2020	1

[v-kelly-msft]

Hola @samhunter ,

Primero divida las columnas de fecha y hora en 2 columnas como fecha y hora;

A continuación, cree una columna calculada para obtener las diferencias de fecha entre abrir y cerrar:

DATEDIFF = 
DATEDIFF('Table'[Date Open.1],'Table'[Date Closed.1],DAY)

Después de eso, cree una medida como se muestra a continuación:

Total minutes = 
var _open=HOUR(MAX('Table'[Date Open.2]))*60+MINUTE(MAX('Table'[Date Open.2]))
var _close=HOUR(MAX('Table'[Date Closed.2]))*60+MINUTE(MAX('Table'[Date Closed.2]))
var _notworkingtime=CALCULATE(COUNTROWS('Table (2)'),FILTER('Table (2)',('Table (2)'[Time]>=MAX('Table'[Date Open.2])&&'Table (2)'[Time]<=MAX('Table'[Date Closed.2])||MAX('Table (2)'[Time])<=MAX('Table'[Date Closed.2]))&&'Table (2)'[IsWorkingTime]=0))
var _notworkingday=CALCULATE(COUNTROWS('Table 2'),FILTER('Table 2','Table 2'[Date]>=MAX('Table'[Date Open.1])&&'Table 2'[Date]<=MAX('Table'[Date Closed.1])&&'Table 2'[Column]=0))+0
Return
IF(MAX('Table'[DATEDIFF])<=1,24*60-_open+_close,(MAX('Table'[DATEDIFF])-1)*60-_open+_close-_notworkingtime-_notworkingday*60
)

Finalmente verá:

Para el archivo .pbix relacionado, haga clic aquí.

Saludos
Kelly

¿Respondí a tu pregunta? ¡Marca mi puesto como solución!

[samhunter]

Hola Kelly

Gracias por su ayuda. Esto se siente muy cerca (sin duda el más cerca i've tiene tan lejos). Creo que he malentendido algo.

Para la segunda fila en su tabla (No.12674 abajo), esperaba 4152 Minutos Totales, pero cuando configuro esto obtengo 904 así que algo ha salido mal en algún lugar. Creé medidas de cada una de las variables en la medida Total de Minutos y las dejé caer en una tabla dándome los números que esperaba para cada una de ellas.

No puedo poner mi cabeza alrededor de la declaración IF. Lo rastreé y encontré que si agrego *24 en un par de lugares para obtener los días completos minutos obtengo 11944 (esencialmente lo que tienes)

Pero todavía no es el 4152 que esperaba.

¿Alguna idea?

[v-kelly-msft]

Hola @samhunter ,

¿Cómo se obtiene el resultado de 4152, como lo que ha dicho en el post original, desea calcular el total de minutos durante un cierto período menos los días notworkingy y notworkingtime.

Por ejemplo, durante 15/8/2019 9:59 a 28/8/2019 17:03, el total de minutos es igual a 17704,días laborables es igual a 4, por lo que el resultado debe ser 17704-4* 24 * 60 -11944, ¿cómo se obtiene el resultado de 4152??

Saludos
Kelly

¿Respondí a tu pregunta? ¡Marca mi puesto como solución!

[samhunter]

@v-kelly-msft

Lo siento, mi cálculo estaba un poco apagado. La lógica que estoy usando es que hay 541 minutos entre las 08:30 y las 17:30, 10 días laborables por lo que, con un ajuste para los tiempos de inicio/fin, creo que el total de minutos debe ser 5294.

Parece que la variable notworkingtime horas sólo está contando desde el principio hasta las 08:30 y de 17:30 para terminar en el primer/último día que creo que explica esta diferencia. No pude averiguar cómo cambiar esto, pero creo que he sido capaz de tomar lo que has hecho y terminarlo usando un método ligeramente diferente. No es bonito, pero parece funcionar.

Hay 541 minutos de trabajo en un día. Múltiples que compran el número de días laborables en el intervalo de fechas y luego realizan un ajuste para la hora abierta y cerrada si alguno de ellos está dentro del día laborable.

Dividir columnas de fecha y hora en columnas de fecha y hora

Medidas:

NotWorkingDay ? CALCULATE(COUNTROWS('Date'),FILTER('Date','Date'[Date]>'MAX('IT Incidents'[OpenedDate])&&'Date'[Date]<'MAX('IT Incidents'[ClosedDate])&&'Date'[IsWorkingday]-0))+0

OpenTimeAdjustment - IF([abrir] > 511, [abierto]-511,0) - 08:30 es el minuto 511 del día

ClosedTimeAdjustment á if([close]< 1051, 1051-[close],0) - 17:30 es el minuto 1051 del día

Abrir : HORA(MAX('IT Incidents'[OpenedTime]))*60+MINUTE(MAX('IT Incidents'[OpenedTime]))

Cerrar s HORA(MAX('IT Incidents'[ClosedTime]))*60+MINUTE(MAX('IT Incidents'[ClosedTime]))

DateDiff - DATEDIFF('IT Incidents'[OpenedDate],'IT Incidents'[ClosedDate],day)

Columna calculada:

Minutos totales ([DateDiff]-[notworkingday]+1)*541 - [OpenedTimeAdjustment] - [ClosedTimeAdjustment