topic Re: Work Hours disconsidering holidays and weekends in DAX Commands and Tips

Work Hours disconsidering holidays and weekends

Johonnatan — Fri, 08 Oct 2021 15:03:22 GMT

Hi all.

I'm a beginner in Power BI and I'd like to calculate work hours (8 to 17) between two dates disconsidering holidays and weekends. How can I do that?

Kind regads,

Johonnatan

Re: Work Hours disconsidering holidays and weekends

colacan — Fri, 08 Oct 2021 17:14:34 GMT

Johonnatan Hi, Please share sample data thanks.

Re: Work Hours disconsidering holidays and weekends

Johonnatan — Fri, 08 Oct 2021 17:44:12 GMT

colacan

Please find below a example.

I would like to consider only weekdays between a time range disregarding holidays.

Johonnatan

Re: Work Hours disconsidering holidays and weekends

colacan — Fri, 08 Oct 2021 19:00:22 GMT

Johonnatan Have a couple of questions,

1. I'd like to calculate work hours (8 to 17) between two dates ==>does 2 dates mean "Created date" and "Closed Date"? / what is formula to to calculate work hours?

2. How did you get 5:10AM at first row in your sample data?

Re: Work Hours disconsidering holidays and weekends

Johonnatan — Fri, 08 Oct 2021 20:17:06 GMT

colacan

1 - About the columns:

"Created date" is a date&time of a new register is opened
"Closed Date" is a date&time of the process is completed

Basically, the formula should be the difference in hours considering only range time 08:00 am to 05:00 pm, I mean, even if the register has been opened 05:00 am I can only count as a work hour after 08:00 am.

2 - For example

Re: Work Hours disconsidering holidays and weekends

VahidDM — Sun, 10 Oct 2021 00:54:29 GMT

Hi Johonnatan

I'm not sure this is the best solution or not, but try it:

Work Hour = VAR _DB = DATEDIFF ( [Created date], [Closed date], DAY ) - 1 VAR _DBH = IF ( ISBLANK ( _DB ) || _DB = 1, 0, _DB * 9 ) VAR _TD1 = TIME ( HOUR ( [Created date] ), MINUTE ( [Created date] ), SECOND ( [Created date] ) ) VAR _TDE = TIME ( HOUR ( [Closed date] ), MINUTE ( [Closed date] ), SECOND ( [Closed date] ) ) VAR _ET = TIME ( 17, 00, 0 ) VAR _ST = TIME ( 8, 00, 0 ) VAR _D1 = IF ( _TD1 < _ST, 9, IF ( _TD1 > _ST && _TD1 < _ET, ( _ET - _TD1 ) * 24, 0 ) ) VAR _DE = IF ( _TDE < _ST, 0, IF ( _TDE > _ST && _TDE <= _ET, ( _TDE - _ST ) * 24, if(_TDE > _ET,9,0))) VAR _Dur = IF ( ISBLANK ( _DB ) || _DB < 0, 0, _DBH + _D1 + _DE ) VAR _HH = TRUNC ( _Dur ) VAR _Min = TRUNC ( ( _Dur - _HH ) * 60 ) RETURN _HH & ":" & _Min

Output:

If you want to exclude Weekends and Holidays, you need to use a calendar table.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

Re: Work Hours disconsidering holidays and weekends

Johonnatan — Wed, 13 Oct 2021 17:03:12 GMT

Hi VahidDM

I appreciate your comments. So, for normal days, this script works fine but for weekends and holidays not yet. I've created a calendar with a column considering if the date is a workday or not and I'm excluding it from datadiff line.

However, the DAX still considering the time from not workday date.

Re: Work Hours disconsidering holidays and weekends

Icey — Wed, 20 Oct 2021 05:33:08 GMT

Hi Johonnatan ,

First, I refer to the holiday table here: Public holidays in the United Kingdom for 2021 - Holiday API. You can modify the table based on your scenario.

Then, based on your calendar table with a column considering if the date is a workday or not, try to create a calculated column like so:

Working Hours = VAR t1 = CALENDAR ( [Created date], [Closed date] ) VAR t2 = FILTER ( ADDCOLUMNS ( t1, "IsWorkDay_", LOOKUPVALUE ( Calendar[WorkDay], Calendar[Date], [Date] ) ), [IsWorkDay_] ) VAR Days_ = COUNTROWS ( t2 ) VAR StartWorkingDateTime = CONVERT ( MINX ( t2, [Date] ) & " " & TIME ( 8, 0, 0 ), DATETIME ) VAR EndWorkingDateTime = CONVERT ( MAXX ( t2, [Date] ) & " " & TIME ( 17, 0, 0 ), DATETIME ) VAR DateDiff_Start = IF ( StartWorkingDateTime < [Created date], DATEDIFF ( StartWorkingDateTime, [Created date], MINUTE ) ) VAR DateDiff_End = IF ( EndWorkingDateTime > [Closed date], DATEDIFF ( [Closed date], EndWorkingDateTime, MINUTE ) ) VAR WorkingMinutes = Days_ * 9 * 60 - DateDiff_Start - DateDiff_End RETURN WorkingMinutes / 60

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Work Hours disconsidering holidays and weekends

VahidDM — Tue, 26 Oct 2021 23:10:13 GMT

Hi Johonnatan

Please see this link:

https://www.vahiddm.com/post/calculating-business-hours-in-power-bi-using-dax

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

Re: Work Hours disconsidering holidays and weekends

Anonymous — Tue, 22 Feb 2022 18:57:43 GMT

I know this is already solved in multiple ways but I'm leaving this here in hope that it might help someone in the future 🙂

https://www.villezekeviking.com/calculating-handling-time-during-office-hours/

Re: Work Hours disconsidering holidays and weekends

CeciliaRojas — Wed, 22 Jun 2022 01:40:14 GMT

Hi! I saw this solution and I think it's pretty great and clean!
I just have a concern, I'm having negative values, but honestly I don't know why or how to evaluate the formula to know where is wrong.

Table Data (Created-ClosedDateVal) and Result (Working Hoours)

Created	ClosedDateVal	WorkingHours
25/04/2019 19:51	26/04/2019 08:27	-0.4
31/05/2019 18:06	02/06/2019 22:17	-0.1
11/06/2019 09:09	11/06/2019 09:08	-0.016666667
18/07/2019 16:08	19/07/2019 00:15	-4.883333333
18/07/2019 16:18	19/07/2019 00:24	-4.9
18/07/2019 17:21	19/07/2019 00:13	-6.133333333
19/09/2019 16:17	20/09/2019 00:02	-5.25
26/11/2019 19:46	27/11/2019 08:22	-0.4
02/12/2019 21:57	03/12/2019 09:34	-1.383333333
25/01/2020 18:30	26/01/2020 23:45	-1052531.5
04/02/2020 08:27	04/02/2020 08:45	-1052761.45
04/02/2020 08:50	04/02/2020 08:57	-1052761.833
04/02/2020 10:08	04/02/2020 13:46	-1052763.133
04/02/2020 10:56	04/02/2020 13:46	-1052763.933
04/02/2020 15:00	04/02/2020 22:59	-1052768
18/03/2020 11:08	18/03/2020 11:59	-1053796.133
03/04/2020 20:02	06/04/2020 08:10	-0.866666667
03/04/2020 21:17	06/04/2020 08:12	-2.083333333
07/04/2020 10:17	07/04/2020 10:17	0
08/04/2020 20:35	12/04/2020 21:39	-2.583333333

Holidays

Date	Holiday
01/01/2019	Año Nuevo
04/02/2019	Aniversario de Constitución
18/03/2019	Natalicio Benito Juarez
18/04/2019	Jueves Santo
19/04/2019	Viernes Santo
01/05/2019	Dia del Trabajo
16/09/2019	Independencia de Mexico
18/11/2019	Aniversario de la Revolución Mexicana
12/12/2019	Virgen de Guadalupe
25/05/2019	Navidad
01/01/2020	Año Nuevo
04/02/2020	Aniversario de Constitución
18/03/2020	Natalicio Benito Juarez
09/04/2020	Jueves Santo
10/04/2020	Viernes Santo
01/05/2020	Dia del Trabajo
16/09/2020	Independencia de Mexico
18/11/2020	Aniversario de la Revolución Mexicana
12/12/2020	Virgen de Guadalupe
25/05/2020	Navidad

Re: Work Hours disconsidering holidays and weekends

DiKi-I — Tue, 26 Sep 2023 10:52:09 GMT

If I have multiple locations based on users. How I can handle the multiple locations holidays?

Re: Work Hours disconsidering holidays and weekends

Johonnatan — Mon, 09 Oct 2023 15:17:55 GMT

DiKi-I

First at all, I do believe you need a lookup table containing the holidays by location and, using DAX, correlate users with their days off.

For that, we must have a key (column) to setup up a relationship, may be, country code would be fine.

Example:

Lookup tables

DAX

LOOKUPVALUE (Holiday [Country Code])

Regards,

Johonnatan