The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 😉
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.
User | Count |
---|---|
137 | |
108 | |
80 | |
69 | |
59 |
User | Count |
---|---|
258 | |
128 | |
116 | |
100 | |
80 |