Working Hours Formula Not Working

[rosemilo]

Hello Power BI Community,

I am trying to calculate the handling time/working hours of all of the employees using the formula below but it's not working for me. (I got it from https://community.powerbi.com/t5/DAX-Commands-and-Tips/Work-Hours-disconsidering-holidays-and-weeken...

Here's the formula I used:

y_new Working Hours (with Calendar table) =

IF('Table'[Region] = "AMERICAS",

IF(OR(ISBLANK('Table'[CreatedDate]),ISBLANK('Table'[ClosedDate])),0,

VAR t1 =

CALENDAR ( [CreatedDate], [ClosedDate] )

VAR t2 =

FILTER (

ADDCOLUMNS (

t1,

"IsWorkDay_", LOOKUPVALUE ( 'Date'[WorkDay MNL], 'Date'[Date], [Date] )

),

[IsWorkDay_]

)

VAR Days_ =

COUNTROWS ( t2 )

VAR StartWorkingDateTime =

CONVERT ( MINX ( t2, [Date] ) & " " & TIME ( 15, 0, 0 ), DATETIME )

VAR EndWorkingDateTime =

CONVERT ( MAXX ( t2, [Date] ) & " " & TIME ( 24, 0, 0 ), DATETIME )

VAR DateDiff_Start =

IF (

StartWorkingDateTime < [CreatedDate],

DATEDIFF ( StartWorkingDateTime, [CreatedDate], MINUTE )

)

VAR DateDiff_End =

IF (

EndWorkingDateTime > [ClosedDate],

DATEDIFF ( [ClosedDate], EndWorkingDateTime, MINUTE )

)

VAR WorkingMinutes = Days_ * 9 * 60 - DateDiff_Start - DateDiff_End

RETURN

WorkingMinutes)/60,

IF(OR(ISBLANK('Table'[CreatedDate]),ISBLANK('Table'[ClosedDate])),0,

VAR t1 =

CALENDAR ( [CreatedDate], [ClosedDate] )

VAR t2 =

FILTER (

ADDCOLUMNS (

t1,

"IsWorkDay_", LOOKUPVALUE ( 'Date'[WorkDay MNL], 'Date'[Date], [Date] )

),

[IsWorkDay_]

)

VAR Days_ =

COUNTROWS ( t2 )

VAR StartWorkingDateTime =

CONVERT ( MINX ( t2, [Date] ) & " " & TIME ( 9, 0, 0 ), DATETIME )

VAR EndWorkingDateTime =

CONVERT ( MAXX ( t2, [Date] ) & " " & TIME ( 18, 0, 0 ), DATETIME )

VAR DateDiff_Start =

IF (

StartWorkingDateTime < [CreatedDate],

DATEDIFF ( StartWorkingDateTime, [CreatedDate], MINUTE )

)

VAR DateDiff_End =

IF (

EndWorkingDateTime > [ClosedDate],

DATEDIFF ( [ClosedDate], EndWorkingDateTime, MINUTE )

)

VAR WorkingMinutes = Days_ * 9 * 60 - DateDiff_Start - DateDiff_End

RETURN

WorkingMinutes)/60)

I am attaching here the my source files for reference. Help please where did I got it wrong? Thank you!
https://drive.google.com/drive/folders/1LPK-WSE-4W13aElTX-EnFou9jaYGKSxU?usp=sharing 

PS. I converted the data format of date using locale English-UK in powerquery

[Anonymous · Accepted solution]

@rosemilo , that is one hell of a complicated measure! I have taken the liberty of re-writing it in a way that I can understand. Here is my attempt which is hopefully easier to understand.

Working Hours = 
    // Get the CreatedDate as just YYYYMMDD
    VAR vCreatedDate =
        DATE(
            YEAR('Table'[CreatedDate]),
            MONTH('Table'[CreatedDate]),
            DAY('Table'[CreatedDate])
        )

    // Get the ClosedDate as just YYYYMMDD
    VAR vClosedDate =
        DATE(
            YEAR('Table'[ClosedDate]),
            MONTH('Table'[ClosedDate]),
            DAY('Table'[ClosedDate])
        )

    RETURN
        IF(NOT ISBLANK('Table'[CreatedDate]) && NOT(ISBLANK('Table'[ClosedDate])),
            // Get all MNL Working Days between CreatedDate and ClosedDate
            SUMX(
                FILTER(
                    'Date',
                    'Date'[Date] >= vCreatedDate
                    && 'Date'[Date] <= vClosedDate
                    && 'Date'[WorkDay MNL] = TRUE()
                ),

                // Get the working start time for this day and region
                VAR vStartTimeWorkingHours = 
                    SWITCH('Table'[Region],
                        "AMERICAS", 'Date'[Date] + TIME(15, 0, 0),
                        "APAC", 'Date'[Date] + TIME(9, 0, 0)
                    )

                // Get the working end  time for this day and region
                VAR vEndTimeWorkingHours =
                    SWITCH('Table'[Region],
                        "AMERICAS", 'Date'[Date] + TIME(23, 59, 59),
                        "APAC", 'Date'[Date] + TIME(18, 0, 0)
                    )

                // Work out the appropriate StartTime for this day
                VAR vStartTimeToUse =
                    IF(
                        vStartTimeWorkingHours < 'Table'[CreatedDate],
                        'Table'[CreatedDate],
                        vStartTimeWorkingHours
                    )

                // Work out the appropriate EndTime for this day
                VAR vEndTimeToUse =
                    IF(
                        vEndTimeWorkingHours > 'Table'[ClosedDate],
                        'Table'[ClosedDate],
                        vEndTimeWorkingHours
                    )
                
                // Get the duration in minutes (only when EndTime > StartTime)
                VAR vWorkingMinutesDuration = 
                    IF(
                        vEndTimeToUse > vStartTimeToUse,
                        DATEDIFF(vStartTimeToUse, vEndTimeToUse, MINUTE)
                    )

                // Return the duration in hours
                RETURN vWorkingMinutesDuration / 60.0 
            )
        )

What it does is:

- For each row in 'Table', loop through rows in 'Date' table that are between CreatedDate and ClosedDate, and that have [Workday MNL] = TRUE

- For each day, work out the start and end working times, taking into account the region

- Get the minutes between the start and end working times

- Divide that by 60 to get working hours

- SUMX the working hours

It seems to give the correct results, but unfortunately is seems to be quite slow. With your sample PBIX file, it takes about 2 minutes to calculate the new column. I leave it as an excercise for others to improve the speed!

And I'm not sure how the table 'Holidays and Weekends' fits into the calculation, as your measure does not seem to refer to that table at all.

[tamerj1]

@rosemilo 
This is a long code 🙂 Would you please advise what is the expected result?

[rosemilo]

@tamerj1 , I need to get the working hours excluding holidays and weekends and considering the shift of 2 regions. If AMERICAS then 15:00 to 24:00, if APAC then 9:00 to 18:00.

[tamerj1]

@Anonymous 
Here is a sample file with the solution https://we.tl/t-6ba0k6mWX1

New Working Hours = 
VAR StdStartAMER = TIME ( 15, 0, 0 )
VAR StdEndAMER = TIME ( 23, 59, 59 )
VAR StdStartAPAC = TIME ( 9, 0, 0 )
VAR StdEndAPAC = TIME ( 18, 0, 0 )
VAR Created = 'Table'[CreatedDate]
VAR ClosedWB = 'Table'[ClosedDate]
VAR Closed = COALESCE ( ClosedWB, Created )
VAR Region = 'Table'[Region]
VAR StdStart = IF ( Region = "APAC", StdStartAPAC, StdStartAMER )
VAR StdEnd = IF ( Region = "APAC", StdEndAPAC, StdEndAMER )
VAR T1 = CALENDAR ( Created, Closed )
VAR T2 = 
    ADDCOLUMNS ( 
        T1, 
        "@Start", IF ( Created > StdStart + [Date], Created, [Date] + StdStart ),
        "@Stop", IF ( Closed  < StdEnd + [Date], Closed, [Date] + StdEnd )
    )
VAR T3 = FILTER ( T2, NOT ( [Date] IN VALUES ( 'Holidays and Weekends'[Weekends and Holidays] ) ) )
VAR T4 = ADDCOLUMNS ( T3, "@Hours", IF ( [@Start] < [@Stop], DATEDIFF ( [@Start], [@Stop], SECOND ) ) )
RETURN
    DIVIDE ( SUMX ( T4, [@Hours] ), 3600 )

[Anonymous]

@tamerj1 , I think your measure is returning incorrect results. For example, for the row with:

CreatedDate = 31/12/2021 13:02

ClosedDate = 31/12/2021 22:59

Region = AMERICAS

The Working Hours should be:

from 15:00 to 22:59

= 479 minutes /60

= 7.98 hours

Your measure returns 4.95 hours

[tamerj1 · Accepted solution]

@Anonymous 
Looks better now 

[Anonymous]

@tamerj1 , excellent! Very nice work, and super fast as well!

[tamerj1]

@Anonymous 
I just switched between AMERICAS time and APAC time. I will amend in the original reply.

[Anonymous · Accepted solution]

@rosemilo , that is one hell of a complicated measure! I have taken the liberty of re-writing it in a way that I can understand. Here is my attempt which is hopefully easier to understand.

Working Hours = 
    // Get the CreatedDate as just YYYYMMDD
    VAR vCreatedDate =
        DATE(
            YEAR('Table'[CreatedDate]),
            MONTH('Table'[CreatedDate]),
            DAY('Table'[CreatedDate])
        )

    // Get the ClosedDate as just YYYYMMDD
    VAR vClosedDate =
        DATE(
            YEAR('Table'[ClosedDate]),
            MONTH('Table'[ClosedDate]),
            DAY('Table'[ClosedDate])
        )

    RETURN
        IF(NOT ISBLANK('Table'[CreatedDate]) && NOT(ISBLANK('Table'[ClosedDate])),
            // Get all MNL Working Days between CreatedDate and ClosedDate
            SUMX(
                FILTER(
                    'Date',
                    'Date'[Date] >= vCreatedDate
                    && 'Date'[Date] <= vClosedDate
                    && 'Date'[WorkDay MNL] = TRUE()
                ),

                // Get the working start time for this day and region
                VAR vStartTimeWorkingHours = 
                    SWITCH('Table'[Region],
                        "AMERICAS", 'Date'[Date] + TIME(15, 0, 0),
                        "APAC", 'Date'[Date] + TIME(9, 0, 0)
                    )

                // Get the working end  time for this day and region
                VAR vEndTimeWorkingHours =
                    SWITCH('Table'[Region],
                        "AMERICAS", 'Date'[Date] + TIME(23, 59, 59),
                        "APAC", 'Date'[Date] + TIME(18, 0, 0)
                    )

                // Work out the appropriate StartTime for this day
                VAR vStartTimeToUse =
                    IF(
                        vStartTimeWorkingHours < 'Table'[CreatedDate],
                        'Table'[CreatedDate],
                        vStartTimeWorkingHours
                    )

                // Work out the appropriate EndTime for this day
                VAR vEndTimeToUse =
                    IF(
                        vEndTimeWorkingHours > 'Table'[ClosedDate],
                        'Table'[ClosedDate],
                        vEndTimeWorkingHours
                    )
                
                // Get the duration in minutes (only when EndTime > StartTime)
                VAR vWorkingMinutesDuration = 
                    IF(
                        vEndTimeToUse > vStartTimeToUse,
                        DATEDIFF(vStartTimeToUse, vEndTimeToUse, MINUTE)
                    )

                // Return the duration in hours
                RETURN vWorkingMinutesDuration / 60.0 
            )
        )

What it does is:

- For each row in 'Table', loop through rows in 'Date' table that are between CreatedDate and ClosedDate, and that have [Workday MNL] = TRUE

- For each day, work out the start and end working times, taking into account the region

- Get the minutes between the start and end working times

- Divide that by 60 to get working hours

- SUMX the working hours

It seems to give the correct results, but unfortunately is seems to be quite slow. With your sample PBIX file, it takes about 2 minutes to calculate the new column. I leave it as an excercise for others to improve the speed!

And I'm not sure how the table 'Holidays and Weekends' fits into the calculation, as your measure does not seem to refer to that table at all.