Forum Discussion

sovereignauto's avatar
sovereignauto
Helper III
5 years ago
Solved

Working hours, Saturdays different

Good Afternoon, 

I have looked at a number of posts including this one: 

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration-Working-Hours/m-p/481543#M182

 

but a) they dont have saturday hours built in that are differnt to mon-friday and b) also the above just returns 0 for me when i add it as as a column. 

1. Date_Recvied - This could be "out of hours" but will not be blank
2. Date_called - This could be blank 

Woking hours are 9-5:30 - Mon-Fri 9-1 Saturday

Thank you!!  

Show and Tell
Tutorial Requests

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    5 years ago

    sovereignauto You would need to add equivalent variables for the Saturday stuff, do essentially the same calculations and then add the two calculation branches together. You could get the Saturday's doing this:

     

    VAR __SaturdayNetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR(__dateStart,__dateEnd),"WeekDay",WEEKDAY([Date],2)),[WeekDay]=6),[Date])

     Note the = 6 versus < 6. 

    • sovereignauto's avatar
      sovereignauto
      Helper III
      5 years ago

      thanks Greg_Deckler , thought that would be the case.

      any Idea why it woud calculate Zero for every row 

      mNetWorkDuration =

      // Get the start and end dates

      VAR __dateStart = MAX([DateReferred])
      VAR __dateEnd = MAX(CLAIM[FirstClientContact])

      // Calculate the Net Work Days between the start and end dates
      VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR(__dateStart,__dateEnd),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
      // Set this to the start of the work day (7:30 AM)
      VAR __startTime = TIME(7,30,0)
      // Set this variable to the end of the work day (6:00 PM)
      VAR __endTime = TIME(18,0,0)
      // Calculate the duration of a full day, in this case in minutes
      VAR __fullDayMinutes = DATEDIFF(__startTime,__endTime,MINUTE)
      // Calculate teh number of full days, this accounts for the possibility that tickets start and end on the same day
      VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
      // Calculate the total duration of all full days.
      VAR __fullDaysDuration = __fullDays * __fullDayMinutes
      // Calculate the start time of the current record
      VAR __startDayTime = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
      // Caclulate the duration of time for the first day
      VAR __startDayDuration = DATEDIFF(__startDayTime,__endTime,MINUTE)
      // Calculate the end time of the current record
      VAR __endDayTime = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
      // Calculate the duration of time for the last day
      VAR __endDayDuration = DATEDIFF(__startTime,__endDayTime,MINUTE)
      // The total duration is the duration of all full days plus the durations of time for the first and last days
      RETURN
      IF(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,MINUTE),__fullDaysDuration + __startDayDuration + __endDayDuration)