Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Johonnatan
Frequent Visitor

Work 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

1 ACCEPTED 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

 

Icey_0-1634707708409.png

 

 

Best Regards,

Icey

 

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

View solution in original post

12 REPLIES 12
DiKi-I
Helper III
Helper III

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

@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

Johonnatan_0-1696864483576.png

DAX

LOOKUPVALUE (Holiday [Country Code]) 

 

Regards,

Johonnatan

Anonymous
Not applicable

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/

VahidDM
Super User
Super User

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!!

 

VahidDM
Super User
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 & ":" & _Min

 

 

 

Output:

VahidDM_0-1633782008492.png

 

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.

 

Johonnatan_1-1634140511048.png

 

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

 

Icey_0-1634707708409.png

 

 

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.

CeciliaRojas_0-1655861413223.pngCeciliaRojas_1-1655861854706.png

 

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

CreatedClosedDateValWorkingHours
25/04/2019 19:5126/04/2019 08:27-0.4
31/05/2019 18:0602/06/2019 22:17-0.1
11/06/2019 09:0911/06/2019 09:08-0.016666667
18/07/2019 16:0819/07/2019 00:15-4.883333333
18/07/2019 16:1819/07/2019 00:24-4.9
18/07/2019 17:2119/07/2019 00:13-6.133333333
19/09/2019 16:1720/09/2019 00:02-5.25
26/11/2019 19:4627/11/2019 08:22-0.4
02/12/2019 21:5703/12/2019 09:34-1.383333333
25/01/2020 18:3026/01/2020 23:45-1052531.5
04/02/2020 08:2704/02/2020 08:45-1052761.45
04/02/2020 08:5004/02/2020 08:57-1052761.833
04/02/2020 10:0804/02/2020 13:46-1052763.133
04/02/2020 10:5604/02/2020 13:46-1052763.933
04/02/2020 15:0004/02/2020 22:59-1052768
18/03/2020 11:0818/03/2020 11:59-1053796.133
03/04/2020 20:0206/04/2020 08:10-0.866666667
03/04/2020 21:1706/04/2020 08:12-2.083333333
07/04/2020 10:1707/04/2020 10:170
08/04/2020 20:3512/04/2020 21:39-2.583333333

 

Holidays

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

 

colacan
Resolver II
Resolver II

@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?

@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

 

Johonnatan_1-1633724201035.png

 

colacan
Resolver II
Resolver II

@Johonnatan  Hi, Please share sample data thanks.

@colacan 

 

Please find below a example.

 

Johonnatan_1-1633714911399.png

 

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

 

Johonnatan

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors