Cálculo de las horas de trabajo

Dale nueva vida a un viejo hilo.

Estaba mirando este problema sin tener idea de por dónde empezar, y luego me topé con este hilo que me dio esperanza.

Desafortunadamente, los ejemplos en este hilo dieron cifras tremendamente incorrectas y se perdieron un par de casos extremos con los que muchos datos estaban plagados.

Entonces, con la inspiración masiva de este hilo, he reunido lo siguiente que, por lo que puedo decir, es perfecto para mi conjunto de datos de más de 300,000:

Business Hours = 

VAR BusinessStart = 8
VAR BusinessEnd = 18
VAR StartDate = DATEVALUE ( [Start] )
VAR StartHour = HOUR ( [Start] )
VAR EndDate = DATEVALUE ( [End] )
VAR EndHour = HOUR ( [End] )

//Generate a table of Dates and Hours based on the Start and End of item
VAR BusinessCalendar =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( StartDate, EndDate ),
                SELECTCOLUMNS ( GENERATESERIES ( BusinessStart, BusinessEnd ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
    )


//Calculate the number of hours on the first day
//Also check if the start and end dates are the same and account for that, will drop lastDayHours in final calc
VAR firstDayHours = 
	IF ( StartDate <> EndDate,

		COUNTROWS (
			FILTER (
				BusinessCalendar,
				([Date] = StartDate && [HOUR] > StartHour )
				)
			),
			
		COUNTROWS (
			FILTER (
				BusinessCalendar,
				([Date] = StartDate && [HOUR] > StartHour && [HOUR] < EndHour )
				)
			)
			
		)

//Calculate hours in last day
VAR lastDayHours = 
	COUNTROWS (
		FILTER (
			BusinessCalendar,
			([Date] = EndDate && [HOUR] < EndHour )
			)
		)
		
//Add first and last hours together unless both same day
VAR combinedHours = 
	IF ( StartDate = EndDate,
		firstDayHours,
		firstDayHours + lastDayHours
	)

//Calculate the hours from the full days
VAR fullDays = 
	COUNTROWS (
		FILTER (
			BusinessCalendar,
			([Date] > StartDate && [Date] < EndDate)
		)
	)


//Calculate the minutes in the hours at each end of the date/time period
//Check if start and end hours are the same and account for that
VAR remainingMinutes = 
	IF ( StartDate <> EndDate,
		(60 - MINUTE ( [Start] )) + MINUTE ( [End] ),
		IF ( StartHour <> EndHour,
			(60 - MINUTE ( [Start] )) + MINUTE ( [End] ),
			MINUTE ( [End] ) - MINUTE ( [Start] )
		)
	)
			
	
RETURN
	( ( (combinedHours + fullDays ) * 60 ) + remainingMinutes ) / 60

NOTA: esto no tiene en cuenta las fechas de finalización que son posteriores a la fecha de inicio, se calcularán como valores negativos, esto no fue un problema para mi conjunto de datos, pero esto podría manejarse con la función MIN para fijar a 0

Hola

Por favor, ¿pueden aconsejarme? He intentado usar los scripts para calcular el horario comercial… ni siquiera está cerca… Estoy bastante seguro de que lo estoy haciendo correctamente, pero claramente no 🙂

por favor puedes ayudar

Gracias

Bagazo

Hola, trabajé en esta nueva solución, espero que pueda ser útil para usted.

Cualquier duda puedes contactarme.

¡Saludos!

Tiempo Minutes = 
VAR WrongDate = IF([Fecha y hora de correo]>[Fecha y hora Primera llamada],TRUE(),FALSE())
VAR SDate_1 = MIN([Fecha y hora de correo],[Fecha y hora Primera llamada])
VAR FDate_1 = MAX([Fecha y hora de correo],[Fecha y hora Primera llamada])
VAR SDate_2 = IF(TIMEVALUE(SDate_1) < TIME(8,30,0),DATEVALUE(SDate_1)+TIME(8,30,0)
                    ,IF(TIMEVALUE(SDate_1) > TIME(17,30,0),DATEVALUE(SDate_1)+TIME(17,30,0)
                        ,SDate_1)) - (30/60/24)
VAR FDate_2 = IF(TIMEVALUE(FDate_1) < TIME(8,30,0),DATEVALUE(FDate_1)+TIME(8,30,0)
                    ,IF(TIMEVALUE(FDate_1) > TIME(17,30,0),DATEVALUE(FDate_1)+TIME(17,30,0)
                        ,FDate_1)) - (30/60/24)

VAR SDate_3 = IF(WEEKDAY(SDate_2,2)=6,DATEVALUE(SDate_2)-(WEEKDAY(SDate_2,2)-5)+(17/24),SDate_2)
VAR FDate_3 = IF(WEEKDAY(FDate_2,2)=6,DATEVALUE(FDate_2)-(WEEKDAY(FDate_2,2)-5)+(17/24),FDate_2)

VAR Calendar_1 = FILTER(ADDCOLUMNS(CROSSJOIN(CALENDAR(SDate_3,FDate_3),SELECTCOLUMNS(GENERATESERIES(8, 17),"Hour",[Value]))
                                  ,"DOW",WEEKDAY([Date],2),"Date_Time",[Date]+[Hour]/24),[DOW] < 6)
VAR Calendar_2_1 = FILTER(ADDCOLUMNS(Calendar_1,"Val_Inicio",IF([Date_Time]>=SDate_3 && [Date_Time]<=FDate_3,1,0)),[Val_Inicio] = 1)
VAR Calendar_2_2 = SELECTCOLUMNS(Calendar_2_1,"Fecha",[Date],"FechaTiempo",[Date_Time])

VAR Calendar_3 = SUMMARIZE(Calendar_2_1,[Date],"Min_DateTime",MINX(FILTER(Calendar_2_2,[Fecha] = [Date]),[FechaTiempo])
                                            ,"Max_DateTime",MAXX(FILTER(Calendar_2_2,[Fecha] = [Date]),[FechaTiempo]))
VAR Calendar_4 = ADDCOLUMNS(Calendar_3,"Date_Start",IF(DATEVALUE([Min_DateTime]) = DATEVALUE(SDate_3),MIN([Min_DateTime],SDate_3),[Min_DateTime])
                                      ,"Date_End",IF(DATEVALUE([Max_DateTime]) = DATEVALUE(FDate_3),MAX([Max_DateTime],FDate_3),[Max_DateTime]))
VAR Time = SUMX(ADDCOLUMNS(Calendar_4,"Minutes",DATEDIFF([Date_Start],[Date_End],MINUTE)),[Minutes])
VAR Minutes = IF(ISBLANK(Time),DATEDIFF(SDate_1,FDate_1,MINUTE),Time)
RETURN
IF(WrongDate,Minutes*-1,Minutes)

Sé que este es un hilo un poco antiguo, pero necesitaba implementarlo y descubrí que había algunos errores/inconsistencias en la solución aceptada, así que pensé en publicar mi código modificado en caso de que alguien más lo necesite en el futuro:

FR_WorkHour = 

VAR Start_Date="SQL: Tickets"[created_at] //set the start date/time variable
VAR End_Date = if('SQL: Tickets'[first_responded_at] <> BLANK(),'SQL: Tickets'[first_responded_at],if('SQL: Tickets'[resolved_at] <> BLANK(), 'SQL: Tickets'[resolved_at],NOw())) // set the end date/time variable


/* Create a dynamic calendar from the start and end dates, and create a cross join table with the available working hours */
VAR workcal =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( DATEVALUE(Start_Date), DATEVALUE(End_Date) ),
                SELECTCOLUMNS ( GENERATESERIES ( 8, 17 ), "Hour", [Value] ) // set the work hours here. If you need to include a break, change the generate series to an array of the start hours e.g. [9,10,11,13,14,15...]
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
    )

/* Count the number of whole hours between the datetimes */
VAR hourcount = 
    COUNTROWS(
        FILTER (workcal,
            (
            ([Date] = DATEVALUE( Start_Date )
                && 
                [Hour] > HOUR( Start_Date ))
            && 
            ([Date] = DATEVALUE( End_Date ) 
                && 
                [Hour] <= HOUR( End_Date ))
            )
            ||
            ([Date] > Start_Date  && [Date] < End_Date )
        )
    )

/* Determine how much extra time (in minutes) is outside of the whole hours (hourcount) */
VAR remained = 
    if(
        DATEVALUE(Start_Date) = DATEVALUE(End_Date) && HOUR(Start_Date) = HOUR(End_Date), 
            DATEDIFF(Start_Date,End_Date,MINUTE),
            DATEDIFF (
                TIMEVALUE ( Start_Date ),
                TIME ( HOUR ( Start_Date ) + 1, 0, 0 ),
                MINUTE
            )
            + 
            DATEDIFF (
                TIME ( HOUR ( End_Date ) - 1, 0, 0 ),
                TIMEVALUE ( End_Date ),
                MINUTE
            )
    )

VAR worktime = (hourcount*60 + remained)/60

RETURN
    worktime

Hola @MarcelWoodman,

Hay un mensaje de error «Un argumento de la función ‘TIME’ tiene el tipo de datos incorrecto o el resultado es demasiado grande o demasiado pequeño».

He cambiado el tipo de datos para [Start_Date] y [End_Date] a Fecha/Hora.

Por favor ayuda. Gracias.

@PBI_newuser, si tuviera que adivinar, creo que tendría que ser en la última función donde le restamos 1 a la hora. Si la hora en que se cierra el ticket es la medianoche (0:00), entonces podría devolver -1, que es una hora no válida.

Intente lanzar una declaración if para decir si hora = 0, luego 23, de lo contrario, -1. Algo como esto:

DATEDIFF (
                TIME ( IF ( HOUR ( End_Date ) = 0, 23, HOUR ( End_Date ) - 1), 0, 0 ),
                TIMEVALUE ( End_Date ),
                MINUTE
            )

¡Gracias @MarcelWoodman! Funciona ahora, pero el horario de trabajo es incorrecto.

A continuación se muestra la columna que creé. ¿Hay algún error? Por favor ayuda. Gracias.

Work Hour = 
VAR workcal =
 FILTER (
 ADDCOLUMNS (
 CROSSJOIN (
 CALENDAR ( DATEVALUE([Start Date]), DATEVALUE([End Date]) ),
 SELECTCOLUMNS ( GENERATESERIES ( 8, 17 ), "Hour", [Value] )
 ),
 "Day of week", WEEKDAY ( [Date], 2 )
 ),
 [Day of week] < 6
 )
VAR hourcount = 
 COUNTROWS(
 FILTER (workcal,
 (
 ([Date] = DATEVALUE( [Start Date] )
 && 
 [Hour] > HOUR( [Start Date] ))
 && 
 ([Date] = DATEVALUE( [End Date] ) 
 && 
 [Hour] <= HOUR( [End Date] ))
 )
 ||
 ([Date] > [Start Date] && [Date] < [End Date] )
 )
 )
VAR remained = 
 if(
 DATEVALUE([Start Date]) = DATEVALUE([End Date]) && HOUR([Start Date]) = HOUR([End Date]), 
 DATEDIFF([Start Date],[End Date],MINUTE),
 DATEDIFF (
 TIMEVALUE ( [Start Date] ),
 TIME ( HOUR ( [Start Date] ) + 1, 0, 0 ),
 MINUTE
 )
 + 
 DATEDIFF (
 TIME ( IF ( HOUR ([End Date] ) = 0, 23, HOUR ([End Date] ) - 1), 0, 0 ),
 TIMEVALUE ( [End Date] ),
 MINUTE
 )
 )
VAR worktime = (hourcount*60 + remained)/60
RETURN
 worktime

@PBI_newuser, entonces mi código anterior era terrible 😁

No estoy seguro de si esto funciona completamente, pero parece al menos *más* preciso:

BusinessHours = 
VAR Start_Date = [DateTime]
VAR End_Date = [DateTime]
VAR Start_Hour = 8
VAR End_Hour = 17
VAR workcal =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( DATEVALUE(Start_Date), DATEVALUE(End_Date) ),
                SELECTCOLUMNS ( GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
    )
VAR starthourcount = 
    COUNTROWS(
        FILTER (workcal,
            (
            [Date] = DATEVALUE(Start_Date)
                && 
                [Hour] > HOUR(Start_Date)
            )
        )
    )
VAR endhourcount = 
    COUNTROWS(
        FILTER (workcal,
            (
            [Date] = DATEVALUE(End_Date) 
                && 
                [Hour] < HOUR(End_Date)
            )
        )
    )
VAR daycount = 
    COUNTROWS(
        FILTER (workcal,
            ([Date] > DATEVALUE(Start_Date) && [Date] < DATEVALUE(End_Date) )
        )
    )
VAR remained = 
    if(
        DATEVALUE(Start_Date) = DATEVALUE(End_Date) && HOUR(Start_Date) = HOUR(End_Date), 
            DATEDIFF(Start_Date,End_Date,MINUTE),
            IF(
				HOUR (Start_Date) < Start_Hour || HOUR (Start_Date) >= End_Hour, 0,
				DATEDIFF (
					TIMEVALUE ( Start_Date ),
					TIME ( HOUR ( Start_Date ) + 1, 0, 0 ),
					MINUTE
				)
			)
            +
			IF(
				HOUR (End_Date) < Start_Hour || HOUR (End_Date) >= End_Hour, 0,
				DATEDIFF (
					TIME ( HOUR ( End_Date ), 0, 0 ),
					TIMEVALUE ( End_Date ),
					MINUTE
				)
			)
    )
VAR worktime = ((daycount + starthourcount + endhourcount)*60 + remained)/60
RETURN
    worktime

Hola @MarcelWoodman

¿Tiene una fórmula para eliminar los casos en los que fecha de inicio>fecha de finalización?

Me gustaría tener estas fechas fuera del cálculo.

¡Muchas gracias por adelantado!
alejandra

Gracias @MarcelWoodman, su solución me dio un gran comienzo para mi problema particular de cálculo de horas de trabajo para dos columnas que son una fecha/hora de apertura del ticket y una fecha/hora de respuesta del ticket.

Un problema notable a superar fue el efecto del cálculo cuando se abre y la respuesta ocurre el mismo día en comparación con cuando la respuesta es al día siguiente o más tarde. Para resolver esto, tuve que crear 3 columnas nuevas en total, una adecuada para una respuesta del mismo día, otra adecuada para el día siguiente o una respuesta posterior y otra para el resultado requerido de la duración de la respuesta comercial (medida en minutos en mi caso ) :

sameday_response_minutes = 
/* This calculates the number of busines minutes between ticket open and being accepted when both occur on the same day */

VAR Start_Date="Data Table"[opened]
VAR End_Date="Data Table"[responded]
VAR Start_Hour = 8
VAR End_Hour = 17

/* calculate the number of full business hours on the day */

VAR first_day_hours_cal = 
	FILTER (
		ADDCOLUMNS (
			GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value]), ((hour(End_Date)- [Hour])>0 && hour(End_Date)>hour(Start_Date) && hour(Start_Date)<[Hour]  ))
			

VAR first_day_hours = COUNTROWS(first_day_hours_cal)

/* calculate the number of full business minutes of the first hour */

VAR first_day_minutes = 60 - MINUTE(Start_Date)

/* calculate the number of full business seconds of the first hour */
VAR first_day_seconds = (60 - SECOND(Start_Date))/60

/* calculate the number of full business minutes of the last hour */
VAR last_day_minutes = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,MINUTE(End_Date),0)
/* calculate the number of full business seconds of the last hour */
VAR last_day_seconds = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,SECOND(End_Date),0)

/* add them all up to get the response time in minutes under different conditions 1. when the response is at the same moment as the ticket is opened, 2. when the ticket is opened and responded out of business hours, 3. at the weekend, 4. when the start and response are both during business hours, 5. when ticket is opened or responded outside of business hours */

VAR response_time=if(Start_Date=End_Date,0,if(HOUR(Start_Date)>End_Hour && HOUR(End_Date)>End_Hour,0,(if(WEEKDAY(Start_date,2)>5,0,if((hour(Start_Date)>Start_Hour && hour(End_Date)<End_Hour),((time(hour(End_Date),MINUTE(End_Date),SECOND(End_Date))-TIME(HOUR(Start_Date),MINUTE(Start_Date),second(Start_Date)))*(24*60)),( (first_day_hours*60) + first_day_minutes - first_day_seconds + last_day_minutes + last_day_seconds))))))

RETURN
    response_time

slow_response_minutes = 
/* this calculates the business minutes between ticket opening and ticket being accepted when the response happens on the next day or later */

VAR Start_Date="Data Table"[opened]
VAR End_Date="Data Table"[responded]
VAR Start_Hour = 8
VAR End_Hour = 17

/* calculate the number of full business days between open and response */
VAR full_days_table =
    FILTER (
        ADDCOLUMNS (

                CALENDAR ( Start_Date, End_Date), "Day of week", WEEKDAY ( [Date], 2 ) 
        ),
        [Day of week] < 6
    )   
VAR full_daycount = 
    COUNTROWS(
        FILTER (full_days_table,
            ([Date] > DATEVALUE(Start_Date) && [Date] < DATEVALUE(End_Date) )
        )
    )
	
/* calculate the number of full business hours on the start day */

VAR first_day_hours_table = 
	FILTER (
		ADDCOLUMNS (
			GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value]), (hour(Start_Date) < [Hour] && hour(Start_Date)>=Start_Hour) )
			

VAR first_day_hours = COUNTROWS(first_day_hours_table)

/* calculate the number of full business minutes on the start day */

VAR first_day_minutes = 60 - MINUTE(Start_Date)

/* calculate the number of full business seconds on the start day */
VAR first_day_seconds = (60 - SECOND(Start_Date))/60

/* calculate the number of full business hours on the end day */
VAR last_day_hours_cal = 
	FILTER (
		ADDCOLUMNS (
			GENERATESERIES ( Start_Hour, End_Hour ), "Hour", [Value]), (hour(End_Date) > [Hour] && hour(End_Date)<=End_Hour) )
			

VAR last_day_hours = COUNTROWS(last_day_hours_cal)

/* calculate the number of full business minutes on the end day */
VAR last_day_minutes = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,MINUTE(End_Date),0)
/* calculate the number of full business seconds on the end day */
VAR last_day_seconds = if(hour(End_Date)>Start_Hour && HOUR(End_Date)<End_Hour,SECOND(End_Date),0)



/* add them all up to get the response time in minutes */

VAR response_time =  (full_daycount*(End_Hour-Start_Hour)*60) + (first_day_hours*60) + first_day_minutes - first_day_seconds + (last_day_hours*60) + last_day_minutes + last_day_seconds


RETURN
    response_time

response_time_minutes = if(DATEDIFF('Data Table'[opened],'Data Table'[responded],DAY)=0,'Data Table'[sameday_response_minutes],'Data Table'[slow_response_minutes])

Hasta ahora no he encontrado ningún error matemático, pero cuando se implemente el informe, lo monitorearé para detectar cualquier escenario que no se haya tenido en cuenta.

@MarcelWoodman ¡Muchas gracias por tu ayuda! Pero todavía no funciona para mí.

@PBI_newuser, sinceramente, no estoy seguro. Me encontré con muchos problemas similares cuando se me ocurrió ese código, porque hay una serie de excepciones que deben tenerse en cuenta.

Una cosa que hice para ayudar fue dividir las funciones en partes para poder ver de dónde venían los errores y luego trabajé el código nuevamente en una sola función. Creo que voy a tener que regresar y verificar mis datos para ver si también está extrayendo el conteo de horas incorrecto.

prueba esta sencilla solución:

//Nueva columna

Hora de trabajo = DATEDIFF ( [ACTIVITY_DATE], [LASTMODIFIEDDATE], HORA )

Hola,

Genial ver este modelo, definitivamente puedo usarlo. Pero tenía una pregunta más:

¿Alguna idea de cómo lidiar con los diferentes horarios de apertura por día de la semana? (por ejemplo, lunes 09:00-18:00, martes 10:00-21:00)

¡Cualquier ayuda es muy apreciada!

Hola

También recibo el error «La fecha de inicio en la función Calendario no puede ser posterior a la fecha de finalización»

pero cuando cambio mis horas de trabajo de esta manera, como en la sección de código, no obtengo el error PERO obviamente tengo 0 porque no es tiempo de trabajo

SELECTCOLUMNS ( GENERATESERIES (17,6 ), "Hour", [Value] )

@v-shex-msft

Estuve mirando su código y creo que probablemente será bastante útil para lo que estoy haciendo.

Me preguntaba si sería posible considerar más de un «rango» de horas de trabajo (quiero eliminar la hora del almuerzo) sin tener que crear varias columnas y luego agregarlas, solo para que el código sea más limpio.

En mi caso, el rango de tiempo sería como:

– 8:00 – 12:00

– 13:00 – 17:15

¡Gracias de antemano!

Verifique su tipo de datos para la columna de fecha. Debe ser formato de fecha.