Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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) /* */
Solved! Go to Solution.
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
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.
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 😉
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |