Calculate working hours without non-working hours, weekends and holidays

[HugoTMO]

Hi community, 

So, i have this problem: 

I need to calculate working hours, considering business hour is 8:30am to 5pm, no weekends. 

If we receive a request after 5pm and its not attended the same day, it should start counting at 8:30am of the next day. 

Any help?   Thank you!!

(FechaSolicitudOS = start date / FechaRegistroOS= end date)

[lkalawski]

HI @HugoTMO 

Please check this link:

https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/td-p/374255

_______________
If I helped, please accept the solution and give kudos! 😀

[HugoTMO]

Hi @lkalawski 

I tried that solution and i have this issue: 

- An argument of function 'TIME' has the wrong data type or the result is too large or too small. -

I already verified that those columns are datetime type.. 

Any ideas?

[az38]

Hi @HugoTMO 

your screenshot doesn't contain full statement. please copy-paste it as text

[HugoTMO]

Hi @az38, 

You are right, you'll see below the statement im using. 

Thanks!

Work Hour =

VAR filtered =

FILTER (

ADDCOLUMNS (

CROSSJOIN (

CALENDAR ( Vw_OperacionesOS_v2[FechaSolicitudOS], Vw_OperacionesOS_v2[FechaRegistroOS] ),

SELECTCOLUMNS ( GENERATESERIES ( 9, 18 ), "Hour", [Value] )

),

"Day of week", WEEKDAY ( [Date], 2 )

),

[Day of week] < 6

&& Vw_OperacionesOS_v2[NroOS] = EARLIER ( Vw_OperacionesOS_v2[NroOS] )

)

VAR hourcount =

COUNTROWS (

FILTER (

filtered,

(

[Date] >= DATEVALUE ( Vw_OperacionesOS_v2[FechaSolicitudOS] )

&& [Hour]

> HOUR ( Vw_OperacionesOS_v2[FechaSolicitudOS] ) + 1

)

&& (

[Date] <= DATEVALUE ( Vw_OperacionesOS_v2[FechaRegistroOS] )

&& [Hour]

> HOUR ( Vw_OperacionesOS_v2[FechaRegistroOS] ) - 1

)

)

)

VAR remained =

DATEDIFF (

TIMEVALUE ( Vw_OperacionesOS_v2[FechaSolicitudOS] ),

TIME ( HOUR ( Vw_OperacionesOS_v2[FechaSolicitudOS] ) + 1, 0, 0 ),

MINUTE

)

+ DATEDIFF (

TIME ( HOUR ( Vw_OperacionesOS_v2[FechaRegistroOS] ) - 1, 0, 0 ),

TIMEVALUE ( Vw_OperacionesOS_v2[FechaRegistroOS] ),

MINUTE

)

RETURN

IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )

[az38]

@HugoTMO 

the first idea is to check your Vw_OperacionesOS_v2[FechaRegistroOS] field. maybe it has not a date time values somewhere

[HugoTMO]

@az38 

I double checked and its date-time type although there are some blank spaces in that column. 

Any other ideas?

Thanks.

[richbenmintz]

Hi @HugoTMO,

I think you answered your question, there are nulls in the data

[az38]

@HugoTMO 

what should return your statement for these blank values?

I have tested this scenario - the same error if [Column1 has blank values

[HugoTMO]

@az38 

I´ve linked Pbi with our database, thats why i´ve got some blanks in that column. Since i am trying to measure SLA i wont take in consideration operations that have no data on that column (become from an internal process that we dont wanna control). 

I hope that answers your question, as a return i dont mind if says "Dont Use" or so. 

Thanks, 

[az38]

@HugoTMO 

so, just to filter out rows with blank Vw_OperacionesOS_v2[FechaSolicitudOS] values in Power Query or in SQL-query to data source

[HugoTMO]

@az38 

I did what you suggested and i have the error of function "TIME". 

Maybe a problem with the code? Any value to change?

Thanks,

[az38]

@HugoTMO 

show, please, column profile from query editor

befor do not forget to refresh preview

[HugoTMO]

Hi @az38 

This is what i get

Thanks for your help.