Cálculo de horas laborales con DAX

Estuve ayudando a un cliente la semana pasada a calcular el total de horas de trabajo entre una fecha / hora de inicio y una fecha / hora de finalización, teniendo en cuenta los días laborables, festivos y fines de semana no laborables, etc. Como suele ser el caso, Pensé que sería un gran artículo de blog que podría compartir con mis lectores. Este es un problema bastante complicado con muchas partes móviles, por lo que he decidido grabar un video que muestra cómo resolví el problema, 1 paso a la vez.

¿Cuántas horas hábiles ha estado abierto mi boleto?

Creé algunos datos de muestra (que se muestran a continuación) para explicar el problema y cómo lo resolví usando DAX.

Los datos de muestra muestran algunos tickets de soporte de la mesa de ayuda con una fecha / hora abierta y una fecha / hora de finalización para cada ticket.

Tenga en cuenta lo siguiente:

• La columna Horas transcurridas muestra la diferencia de tiempo entre las horas de apertura y finalización.
  • Incluye horas no laborables
  • Incluye fines de semana y festivos
• A menudo es necesario que el tiempo de respuesta de un ticket de soporte se calcule tomando solo el horario comercial entre las horas de inicio y finalización, ignorando las horas no laborables, los fines de semana y los días festivos.

La solución

Este problema se puede resolver con DAX o con Power Query. He elegido DAX para calcular el horario comercial en este caso. Explico mi razón de ser en el video. Como siempre, prefiero resolver problemas dividiéndolos en pedazos, probando los resultados en cada paso antes de seguir adelante. Usé variables en DAX para definir cada paso y probarlo antes de continuar. El uso de variables proporciona una buena legibilidad y comprensión de las fórmulas complejas de DAX.

Editar: asegúrese de consultar mi artículo de seguimiento aquí también, para una solución más elegante.

La fórmula DAX

Aquí está la fórmula final, pero la fórmula en sí no importa realmente: no aprenderá DAX leyendo esta fórmula. Tú APRENDERÁ DAX viendo mi video de cómo escribí esta fórmula.

Bus Hours Open =
   VAR BusHoursStart = CALCULATE(
                         SELECTEDVALUE(BusinessHours[Time]),
                         BusinessHours[Business Hours]="Start"
                       )
   VAR BusHoursEnd = CALCULATE(SELECTEDVALUE(BusinessHours[Time]),BusinessHours[Business Hours]="End")
   VAR BusHoursPerDay = VALUE(BusHoursEnd - BusHoursStart)*24
   VAR ThisStartTime = HelpDeskTickets[Start Time]
   VAR ThisEndTime = HelpDeskTickets[End Time]
   VAR StartDate = HelpDeskTickets[Start Date]
   VAR EndDate = HelpDeskTickets[End Date]
   VAR FirstDayElapsedTime = SWITCH(TRUE(),
                                RELATED('Calendar'[Working Day])=0,0,
                                ThisStartTime <= BusHoursStart,BusHoursPerDay,  //edited sequence to fix logic issue
                                ThisStartTime>=BusHoursEnd,0,
                                StartDate = EndDate && ThisEndTime < BusHoursEnd,
                                       round((ThisEndTime-ThisStartTime)*24,3),
                                round((BusHoursEnd-ThisStartTime)*24,3)
                              )
   VAR LastDayElapsedTime = SWITCH(TRUE(),
                                 LOOKUPVALUE('Calendar'[Working Day],'Calendar'[Date],EndDate)=0,0,
                                 ThisEndTime >= BusHoursEnd,BusHoursPerDay, //edited sequence to fix logic issue
                                 ThisEndTime<=BusHoursStart,0,
                                 StartDate = EndDate,0,
                                 round((ThisEndTime - BusHoursStart)*24,3)
                             ) 
   VAR FullWorkDays = CALCULATE(
                         SUM('Calendar'[Working Day]),
                         DATESBETWEEN('Calendar'[Date], StartDate+1,EndDate-1)
                      )
   VAR TotalHours = FirstDayElapsedTime + FullWorkDays*BusHoursPerDay + LastDayElapsedTime
   RETURN TotalHours

Aquí está el video

Aquí están el archivo de muestra y el workbook de Power BI

Datos de muestra del horario comercial
Calcular archivo PBIX de horario comercial

Asegúrese de leer mi artículo de seguimiento aquí https://exceleratorbi.com.au/calculating-elapses-business-hours-using-dax-part-2/

¿Quiere aprender DAX de mí?

Si le gusta este video y mi estilo de enseñanza, ¿por qué no considerar inscribirse en uno de mis cursos formales de capacitación donde le enseño cómo ser excelente en DAX? Tengo las siguientes opciones.

1. Cursos de introducción en vivo dirigidos por un instructor (diseñados por mí e impartidos por Jason).
   1. https://exceleratorbi.com.au/product-category/virtual-instructor-led-online-training/modules-1-2/
2. Un curso DAX intermedio / avanzado dirigido por un instructor en vivo diseñado e impartido por mí
   1. https://exceleratorbi.com.au/product-category/virtual-instructor-led-online-training/module-3/
3. Versiones de video a pedido de los cursos en vivo anteriores
   1. https://www.skillwave.training/courses/data-to-dashboard-using-power-bi/
   2. https://www.skillwave.training/courses/extracting-data-insights-with-power-bi-and-dax/
4. Un curso de DAX semestral en el que aprendes de mi libro y contenido de video en línea, y luego te unes a una sesión semanal de preguntas y respuestas en vivo conmigo para ayudarte a aprender.
   1. https://www.skillwave.training/courses/supercharge-power-bi-online/