topic Re: Working Hours Formula Not Working in DAX Commands and Tips

Working Hours Formula Not Working

rosemilo — Mon, 04 Jul 2022 17:33:55 GMT

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-weekends/m-p/2144688#M49337

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

Re: Working Hours Formula Not Working

tamerj1 — Tue, 05 Jul 2022 04:50:43 GMT

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

Re: Working Hours Formula Not Working

rosemilo — Tue, 05 Jul 2022 05:18:10 GMT

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.

Re: Working Hours Formula Not Working

Anonymous — Tue, 05 Jul 2022 08:06:12 GMT

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.

Re: Working Hours Formula Not Working

tamerj1 — Tue, 05 Jul 2022 11:28:56 GMT

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 )

Re: Working Hours Formula Not Working

Anonymous — Tue, 05 Jul 2022 10:55:38 GMT

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

Re: Working Hours Formula Not Working

tamerj1 — Tue, 05 Jul 2022 11:13:10 GMT

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

Re: Working Hours Formula Not Working

tamerj1 — Tue, 05 Jul 2022 11:32:59 GMT

Anonymous
Looks better now

Re: Working Hours Formula Not Working

Anonymous — Tue, 05 Jul 2022 11:40:04 GMT

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