cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johonnatan
Regular 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
Icey
Community Support
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 / 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

9 REPLIES 9
Ville
Helper I
Helper I

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.

Icey
Community Support
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 / 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.

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
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!