working hours

5 Topics

date/time difference
Hi all I am trying to work out the working day/time difference to establish the case length. ideally discounting non work days/hours. This are the 2 fields i have, and a date table. Any help will be great. Thanks
Solved
EloiseJane
3 years ago Place DAX Commands and Tips
1.1KViews
0likes
2Comments
Service Duration in seconds Calculation in DAX
Hi, I have this code in excel that calculates the service duration (in seconds) of a Support ticket, excluding bank holidays, weekends and out of office hours. Excel Code - =((NETWORKDAYS.INTL(A2,B2,1,$H$2:$H$11)-1)*("18:00"-"7:00")+IF(NETWORKDAYS.INTL(B2,B2,1,$H$2:$H$11),MEDIAN(MOD(B2,1),"7:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS.INTL(A2,A2,1,$H$2:$H$11)*MOD(A2,1),"7:00","18:00"))*86400 My Dax code so far - Measure.ServiceHours = VAR _StartDate = SELECTEDVALUE(TICKET_MASTER[TICKETSUBMITDATE]) VAR _EndDate = SELECTEDVALUE(TICKET_MASTER[CLOSEDTIME]) RETURN ((NETWORKDAYS(_StartDate, _EndDate,1,BankHolidayDates)-1)*("18:00"-"7:00")+IF(NETWORKDAYS(_EndDate,_EndDate,1,BankHolidayDates),MEDIAN(mod(_EndDate,1),"7:00","18:00"),"18:00")-MEDIAN(NETWORKDAYS(_StartDate,_StartDate,1,BankHolidayDates)*MOD(_StartDate,1),"7:00","18:00"))*86400 Unfortunately Median dax code works differently to excel, can anyone help me convert this into DAX? Example of excel code working below
Solved
LSHagger
3 years ago Place DAX Commands and Tips
1KViews
0likes
2Comments
Working Hours Formula Not Working
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
Solved
rosemilo
4 years ago Place DAX Commands and Tips
1.6KViews
0likes
8Comments
How to calculate working hours per day per employee
Hello, I have data that includes Date Emp Id Hrs 6/20/22 12345 3 6/20/22 12345 8 I need to agregate the data to sum up Emp ID hours for the same day, and flag anything over 8 hrs as OT. Like so Date Emp ID Total Hrs Reg hrs OT 6/20/22 12345 11 8 3 This would ideally aggregate to a higher level that does not show employee ID's and shows by locations. Any help or resources that will help?
Solved
OCBB_SFAFPandA
4 years ago Place DAX Commands and Tips
1.7KViews
0likes
4Comments
WTD, MTD, YTD working hours of each employee with different timestamps for each day using DAX
This is a sample data. I need to calculate working hours in DAX. Working hours for one day = Last Timestamp of the day - First Timestamp of the day It is easy to calculate for one day but I can't seem to figure out how to calculate total working hours WTD, MTD, YTD. Every day timestamps are different and we want working hours for each Worker(Name) separately. Please guide. I can share the actual data as well if anybody requires. amitchandak Anonymous Jihwan_Kim daxer-almighty PaulOlding Fowmy
Solved
manuvats
5 years ago Place DAX Commands and Tips
2.3KViews
0likes
7Comments