Work Hours disconsidering holidays and weekends

[Johonnatan]

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

[Ville]

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]

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]

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

[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 · 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

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]

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

@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

[colacan]

@Johonnatan  Hi, Please share sample data thanks.

[Johonnatan]

@colacan 

Please find below a example.

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

Johonnatan