Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

How to Calculate hours difference between two dates/times taking into consideration working hours

How can i calculate the difference in hours (Minutes as well if possible) taking into consideration that working hour is:

From 9:00:00

To 17:00:00

 

I have searched online but these topics do not answer my questions directly.

 

I have tried to use DATEDIFF Function which works as measure but I do not know how to include only working hours in this case.

 

DATEDIFF( MIN(Incident.dataaanmk); MIN(Datwijzig); HOUR)

 

I am using MIN because an incident may have:

 

- Only 1 Dataaanmk

- But many Datwijzig (We only take the first one).

 

Thank you.

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Try something along the lines of:

     

    mNetWorkDuration = 
// Get the start and end dates
VAR __dateStart = MAX([Date_Start])
VAR __dateEnd = MAX([Date_End])
// 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 __startHour = TIME(7,30,0)
// Set this variable to the end of the work day (6:00 PM)
VAR __endHour = TIME(18,0,0)
// Calculate the duration of a full day, in this case in minutes
VAR __fullDayMinutes = DATEDIFF(__startHour,__endHour,MINUTE)
VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
VAR __fullDaysDuration = __fullDays * __fullDayMinutes 
VAR __startDayHour = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
VAR __startDayDuration = DATEDIFF(__startDayHour,__endHour,MINUTE)
VAR __endDayHour = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
VAR __endDayDuration = DATEDIFF(__startHour,__endDayHour,MINUTE)
RETURN  __fullDaysDuration + __startDayDuration + __endDayDuration

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler 

       

      Thank you for your answer.

       

      I have applied the measure but it takes too long to to apply when using it in a table.

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        So does it work? How long is too long? Potentially could be optimized somehow.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 

     

    I am not sure about your expected result. If you want to calculate the difference between two time in HH:MM:SS. You could just create a column: column =  [Time column1]-[Time column2], then change the new new column into time type.

     

    Paul Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly