Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
If I have multiple locations based on users. How I can handle the multiple locations holidays?
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
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/
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!!
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!!
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.
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.
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 |
@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?
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
Please find below a example.
I would like to consider only weekdays between a time range disregarding holidays.
Johonnatan
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
43 | |
19 | |
18 |