Forum Discussion
Helper IWork Hours disconsidering holidays and weekends
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
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 / 60Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12 Replies
- colacan
Resolver II
Johonnatan Hi, Please share sample data thanks.
JohonnatanPlease find below a example.
I would like to consider only weekdays between a time range disregarding holidays.
Johonnatan
- colacan
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?
- Johonnatan
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 completedBasically, 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
- VahidDM
Super User
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 & ":" & _MinOutput:
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!!
- Johonnatan
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.
- Icey
Community Support
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 / 60Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- VahidDM
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!!
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/
DiKi-IPost Partisan
If I have multiple locations based on users. How I can handle the multiple locations holidays?
- Johonnatan
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
