Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Segundo intento (el primer post dio un error)
Necesito calcular el tiempo en minutos entre dos fechas. Tengo una tabla de mensajes como esta:
Cliente A | 02/01/2020 11:00 | 02/01/2020 13:00 |
Cliente B | 02/01/2020 12:00 | 03/01/2020 16:00 |
Cliente C | 02/01/2020 16:00 | 06/01/2020 11:35 |
Pero aquí está el desafío. Todos los clientes tienen contratos diferentes en los que se describen las horas de trabajo. Necesito el tiempo efectivo entre la fecha creada y la fecha resuelta. El horario comercial se almacena en una tabla separada (pero vinculada) como:
Cliente | MonOpen | MonClosed | TueOpen | TueClosed | WedOpen | WedClose | ThuOpen | ThuClose | FriOpen | FriClose | SatOpen | SatClose | SunOpen | SunClose |
A | 0800 | 1700 | 0800 | 1700 | 0800 | 1700 | 0800 | 1700 | 0800 | 1700 | 1000 | 1500 | 1000 | 1500 |
B | 0900 | 1700 | 0900 | 1700 | 0900 | 1700 | 0900 | 1700 | 0900 | 1700 | ||||
C | 0000 | 2359 | 0000 | 2359 | 0000 | 2359 | 0000 | 2359 | 0000 | 2359 | 0600 | 1800 | 0600 | 1800 |
La tabla de clientes se puede cambiar a un formato de mejor ajuste. Es un archivo csv, pero puedo cambiarlo con shell o algo así.
Esperando los minutos en la tabla de mensajes, preferiblemente en Dax
Esto podría ayudar a: https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration-Working-Hours/m-p/481543#M...
Hola Greg,
Gracias por la respuesta. Pero fui por otro camino. Me acordé de un post que usé hace un tiempo para calcular los días activos entre una fecha de creación de ticket y la fecha de cierre:
https://community.powerbi.com/t5/Desktop/Calculate-Hours-between-dates-for-only-business-hours/td-p/...
Así que usé eso para crear una tabla con todas las fechas en las que un ticket estaba activo. Se ha añadido una columna para el número del día de la semana y escribió un estado de dax gigante para comprobar si el boleto estaba activo y durante cuánto tiempo:
Time Active =
VAR _BusinessOpen =
SWITCH(
TRUE(),
'Active'[DayOfWeek] = 0, RELATED(Customer[MONDAY OPEN]),
'Active'[DayOfWeek] = 1, RELATED(Customer[TUESDAY OPEN]),
'Active'[DayOfWeek] = 2, RELATED(Customer[WEDNESDAY OPEN]),
'Active'[DayOfWeek] = 3, RELATED(Customer[THURSDAY OPEN]),
'Active'[DayOfWeek] = 4, RELATED(Customer[FRIDAY OPEN]),
'Active'[DayOfWeek] = 5, RELATED(Customer[SATURDAY OPEN]),
'Active'[DayOfWeek] = 6, RELATED(Customer[SUNDAY OPEN])
)
VAR _BusinessClose =
SWITCH(
TRUE(),
'Active'[DayOfWeek] = 0, RELATED(Customer[MONDAY CLOSE]),
'Active'[DayOfWeek] = 1, RELATED(Customer[TUESDAY CLOSE]),
'Active'[DayOfWeek] = 2, RELATED(Customer[WEDNESDAY CLOSE]),
'Active'[DayOfWeek] = 3, RELATED(Customer[THURSDAY CLOSE]),
'Active'[DayOfWeek] = 4, RELATED(Customer[FRIDAY CLOSE]),
'Active'[DayOfWeek] = 5, RELATED(Customer[SATURDAY CLOSE]),
'Active'[DayOfWeek] = 6, RELATED(Customer[SUNDAY CLOSE])
)
VAR _created = RELATED(Messages[Start])
VAR _closed = IF(RELATED(Messages[Close])=BLANK(), NOW(), RELATED(Messages[Close]))
VAR _closeddate = IF(RELATED(Messages[Close].[Date])=BLANK(), DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY())), RELATED(Messages[Close].[Date]))
VAR _OpenDateHour =
IF(
_BusinessOpen = BLANK(),
BLANK(),
CONVERT( 'Active'[DateRange] & " " & _BusinessOpen, DATETIME)
)
VAR _CloseDateHour =
IF(
_BusinessClose = BLANK(),
BLANK(),
CONVERT( 'Active'[DateRange] & " " & _BusinessClose, DATETIME)
)
return
SWITCH(
TRUE(),
//Closed? 0 minutes active
_businessOpen = BLANk(), 0,
//same day close
RELATED(Messages[Start].[Date]) = RELATED(Messages[Close].[Date]), DATEDIFF(_created, _closed, MINUTE),
//First day open. After start businesshour
'Active'[DateRange].[Date] = RELATED(Messages[Start].[Date]) && RELATED(Messages[Start]) >= _OpenDateHour, DATEDIFF(RELATED(Messages[Start]), _CloseDateHour, MINUTE),
//First day open. before start businesshour
'Active'[DateRange].[Date] = RELATED(Messages[Start].[Date]) && RELATED(Messages[Start]) <= _OpenDateHour, DATEDIFF(_OpenDateHour, _CloseDateHour, MINUTE),
//If day not create or close day
'Active'[DateRange].[Date] <> RELATED(Messages[Start].[Date]) && 'Active'[DateRange].[Date] <> _closeddate, DATEDIFF(_OpenDateHour,_CloseDateHour, MINUTE),
//Close day
'Active'[DateRange].[Date] = _closeddate && _closed <= _CloseDateHour, DATEDIFF(_OpenDateHour, _closed, MINUTE),
'Active'[DateRange].[Date] = _closeddate && _closed > _CloseDateHour, DATEDIFF(_OpenDateHour, _CloseDateHour, MINUTE),
'Active'[DateRange].[Date] = _closeddate && _closed = blank(), DATEDIFF(_OpenDateHour, now(), MINUTE),
BLANK()
)
Es un pedazo largo de dax, pero funciona rápido y perfectamente. Y me permitirá trabajar rápidamente en diferentes zonas horarias y días de ahorro de luz cuando sea necesario.