Calculating Working Hours Between Business Hour Dates Excluding Weekends

[michael_laan]

I was have been trying to come up with a calculation for working hours between two dates, exlcuding weekends and non-working hours.  Additionally the calculation needed to adjust the actual start and stop date/times if they were created or modified outside of normal business hours.

I got my inspiration from this thread (https://community.powerbi.com/t5/Desktop/Calculate-Date-and-Time-difference-considering-the-weekends...) and adjusted for my porpuses. Other than meeting my needs exacly, what I like about my adaptation is that uses variables rather than creating additional columns that are only used for the final output.

I am not  a DAX expert, more of a newbie but this worked well for me and I wanted to see if there were improvments that could be made to it or errors that I might be missing?

HoursToFile = 

// Get Date Column in Date Only Format
VAR DocumentFirstDateOnly = DATE(YEAR('DATA BDM History'[Document Created Date]),MONTH('DATA BDM History'[Document Created Date]),DAY('DATA BDM History'[Document Created Date]))
VAR DocumentLastDateOnly = DATE(YEAR('DATA BDM History'[Document Modified Date]),MONTH('DATA BDM History'[Document Modified Date]),DAY('DATA BDM History'[Document Modified Date]))

// Set Work Hours for Created & Modified Dates
VAR DocumentFirstDateOnlyStart = DocumentFirstDateOnly + TIME(08,00,00)
VAR DocumentLastDateOnlyStart = DocumentLastDateOnly + TIME(08,00,00)
VAR DocumentFirstDateOnlyEnd = DocumentFirstDateOnly + TIME(17,00,00)
VAR DocumentLastDateOnlyEnd = DocumentLastDateOnly + TIME(17,00,00)
VAR WorkHours = 9

// Set Next Business Day
// Note: Used if Created or Modified occured outside of business hours
VAR DocumentCreatedNextBusiness = IF(WEEKDAY(DocumentFirstDateOnly, 1) + 1 = 1, DocumentFirstDateOnlyStart + 2, IF(WEEKDAY(DocumentFirstDateOnly, 1) + 1 = 7, DocumentFirstDateOnlyStart + 3, DocumentFirstDateOnlyStart + 1))
 
VAR DocumentModifiedNextBusiness = IF(WEEKDAY(DocumentLastDateOnly, 1) + 1 = 1, DocumentLastDateOnlyStart + 2, IF(WEEKDAY(DocumentLastDateOnly, 1) + 1 = 7, DocumentLastDateOnlyStart + 3, DocumentLastDateOnlyStart + 1)) 
        
// Establish Real Created or Modified Dates
// Note: Check if Created & Modified happened outside of business hours, if so, use Next Business Day.
VAR DocumentDateCreated = IF('DATA BDM History'[Document Created Date] < DocumentFirstDateOnlyStart, DocumentFirstDateOnlyStart,IF('DATA BDM History'[Document Created Date] > DocumentFirstDateOnlyEnd, DocumentCreatedNextBusiness,'DATA BDM History'[Document Created Date]))
VAR DocumentDateModified = IF('DATA BDM History'[Document Modified Date] < DocumentLastDateOnlyStart, DocumentLastDateOnlyStart,IF('DATA BDM History'[Document Modified Date] > DocumentLastDateOnlyEnd, DocumentModifiedNextBusiness ,'DATA BDM History'[Document Modified Date]))

// Get Real First & Last Dates Only
// Note: Redo Created & Modified dates based on Real Created & Modified Date variables
VAR DocumentRealFirstDateOnly = DATE(YEAR(DocumentDateCreated),MONTH(DocumentDateCreated),DAY(DocumentDateCreated))
VAR DocumentRealLastDateOnly = DATE(YEAR(DocumentDateModified),MONTH(DocumentDateModified),DAY(DocumentDateModified))

// Set Real Work Hours for Created & Modified dates
VAR DocumentRealFirstDateOnlyStart = DocumentRealFirstDateOnly + TIME(08,00,00)
VAR DocumentRealLastDateOnlyStart = DocumentRealLastDateOnly + TIME(08,00,00)
VAR DocumentRealFirstDateOnlyEnd = DocumentRealFirstDateOnly + TIME(17,00,00)
VAR DocumentRealLastDateOnlyEnd = DocumentRealLastDateOnly + TIME(17,00,00)

// Date & Time Difference between Real Created & Modified
VAR DateDiff = DATEDIFF(DocumentDateCreated,DocumentDateModified,DAY)

// Exclude Weekends From DateDiff
VAR FullDaysToFile = IF(DateDiff > 1,CALCULATE(DISTINCTCOUNT ('DateKey'[Date]),FILTER('DateKey','DateKey'[Date] > DocumentDateCreated && 'DateKey'[Date] < DocumentDateModified && 'DateKey'[IsWorkDay] = 1 )) -1, 0)

// Time to File Variables
VAR FirstDaySeconds = IF(DateDiff = 0, DATEDIFF(DocumentDateCreated,DocumentDateModified,SECOND), DATEDIFF(DocumentDateCreated,DocumentRealFirstDateOnlyEnd,SECOND))
VAR LastDaySeconds = IF(DateDiff >= 1, DATEDIFF(DocumentRealLastDateOnlyStart,DocumentDateModified,SECOND), 0)
VAR MiddleDaysSeconds = FullDaysToFile * WorkHours * 3600

// Add up First, Middle & Last Day seconds
VAR TotalSecondsToFile = FirstDaySeconds + LastDaySeconds + MiddleDaysSeconds

RETURN

// Return hours to file
ROUND(TotalSecondsToFile / 3600, 1)

/* Validation */
"Document Created Date: " & 'DATA BDM History'[Document Created Date] & UNICHAR(10) &
"DocumentDateCreated : " & DocumentDateCreated & UNICHAR(10) &
"Document Modified Date: " & 'DATA BDM History'[Document Modified Date] & UNICHAR(10) &
"DocumentDateModified : " & DocumentDateModified & UNICHAR(10) & UNICHAR(10) &
"DateDiff: " & DateDiff & UNICHAR(10) &
"FullDaysToFile: " & FullDaysToFile & UNICHAR(10) &
"FirstDaySeconds: " & FirstDaySeconds & UNICHAR(10) &
"LastDaySeconds: " & LastDaySeconds & UNICHAR(10) &
"MiddleDaysSeconds: " & MiddleDaysSeconds & UNICHAR(10) &
"TotalSecondsToFile: " & TotalSecondsToFile  & UNICHAR(10) & UNICHAR(10) &
"Total Hours To File: " & ROUND(TotalSecondsToFile / 3600,1)
/* */

[STIMIL]

Hi Michael,

In your code, below variables were initialized but never used, are they not needed to be used? I see for some dates I'm getting -ve values. Such as, 4/17/2020 11:59:27 PM -> 4/18/2020 12:01:14 AM for start and end time as 8am to 5pm. 

// Set Work Hours for Created & Modified Dates
VAR DocumentFirstDateOnlyStart = DocumentFirstDateOnly + TIME(08,00,00)

VAR DocumentLastDateOnlyEnd = DocumentLastDateOnly + TIME(17,00,00)

 Thanks, Stimil

[michael_laan]

Hi @STIMIL ,

I believe I added the variables for testing and never removed them. I would highly recommend looking at the accepted solution for my post though, the code from @Greg_Deckler was much cleaner and provided the same results.

[Greg_Deckler · Accepted solution]

So like this? https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration/m-p/481543#M182

[michael_laan]

Thanks, Greg!

I used this edit from your post and it worked perfectly! I did have to make one edit:

Changed:

// Get the start and end dates
VAR __dateStart = MAX([Date_Start])
VAR __dateEnd = MAX([Date_End])

To:

// Get the start and end dates
VAR __dateStart = [Date_Start] 
VAR __dateEnd = [Date_End]

Thanks again, so much cleaner than my work 😉