Forum Discussion

rocky09's avatar
rocky09
Solution Sage
8 years ago
Solved

Calculating Working hours

I have this following Data, I am trying to find a way to calculating  Working hours in betwen dates excluding Weekends. Works hours are between: Morning 9:00 AM to Evening 6:00 PM and Saturday and S...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

     

    HI rocky09,

     

    You can try to use below calculated column formula to calculate valid working hour:

     

    Work Hour = 
VAR filtered =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( [ACTIVITY_DATE], [LASTMODIFIEDDATE] ),
                SELECTCOLUMNS ( GENERATESERIES ( 9, 18 ), "Hour", [Value] )
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
            && [TicketID] = EARLIER ( Table1[TicketID] )
    )
VAR hourcount =
    COUNTROWS (
        FILTER (
            filtered,
            (
                [Date] >= DATEVALUE ( [ACTIVITY_DATE] )
                    && [Hour]
                        > HOUR ( [ACTIVITY_DATE] ) + 1
            )
                && (
                    [Date] <= DATEVALUE ( [LASTMODIFIEDDATE] )
                        && [Hour]
                            > HOUR ( [LASTMODIFIEDDATE] ) - 1
                )
        )
    )
VAR remained =
    DATEDIFF (
        TIMEVALUE ( [ACTIVITY_DATE] ),
        TIME ( HOUR ( [ACTIVITY_DATE] ) + 1, 0, 0 ),
        MINUTE
    )
        + DATEDIFF (
            TIME ( HOUR ( [LASTMODIFIEDDATE] ) - 1, 0, 0 ),
            TIMEVALUE ( [LASTMODIFIEDDATE] ),
            MINUTE
        )
RETURN
    IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )

     

     

    Regards,

    Xiaoxin Sheng

MarcelWoodman's avatar
MarcelWoodman
Regular Visitor
6 years ago

I know this is a bit of an older thread, but needed to implement this and found that there were some errors/inconsistencies in the accepted solution, so I thought I'd post my modified code in the event that anyone else needs it in the future:

FR_WorkHour = 

VAR Start_Date = 'SQL: Tickets'[created_at] //set the start date/time variable
VAR End_Date = if('SQL: Tickets'[first_responded_at] <> BLANK(),'SQL: Tickets'[first_responded_at],if('SQL: Tickets'[resolved_at] <> BLANK(), 'SQL: Tickets'[resolved_at],NOw())) // set the end date/time variable


/* Create a dynamic calendar from the start and end dates, and create a cross join table with the available working hours */
VAR workcal =
    FILTER (
        ADDCOLUMNS (
            CROSSJOIN (
                CALENDAR ( DATEVALUE(Start_Date), DATEVALUE(End_Date) ),
                SELECTCOLUMNS ( GENERATESERIES ( 8, 17 ), "Hour", [Value] ) // set the work hours here. If you need to include a break, change the generate series to an array of the start hours e.g. [9,10,11,13,14,15...]
            ),
            "Day of week", WEEKDAY ( [Date], 2 )
        ),
        [Day of week] < 6
    )

/* Count the number of whole hours between the datetimes */
VAR hourcount = 
    COUNTROWS(
        FILTER (workcal,
            (
            ([Date] = DATEVALUE( Start_Date )
                && 
                [Hour] > HOUR( Start_Date ))
            && 
            ([Date] = DATEVALUE( End_Date ) 
                && 
                [Hour] <= HOUR( End_Date ))
            )
            ||
            ([Date] > Start_Date  && [Date] < End_Date )
        )
    )

/* Determine how much extra time (in minutes) is outside of the whole hours (hourcount) */
VAR remained = 
    if(
        DATEVALUE(Start_Date) = DATEVALUE(End_Date) && HOUR(Start_Date) = HOUR(End_Date), 
            DATEDIFF(Start_Date,End_Date,MINUTE),
            DATEDIFF (
                TIMEVALUE ( Start_Date ),
                TIME ( HOUR ( Start_Date ) + 1, 0, 0 ),
                MINUTE
            )
            + 
            DATEDIFF (
                TIME ( HOUR ( End_Date ) - 1, 0, 0 ),
                TIMEVALUE ( End_Date ),
                MINUTE
            )
    )

VAR worktime = (hourcount*60 + remained)/60

RETURN
    worktime
  • PBI_newuser's avatar
    PBI_newuser
    Post Prodigy
    6 years ago

    Hi MarcelWoodman ,

    There is an error message "An argument of function 'TIME' has the wrong data type or the result is too large or too small."

    I have changed the data type for [Start_Date] and [End_Date] to Date/Time.

    Please help. Thank you.

     

     

    • MarcelWoodman's avatar
      MarcelWoodman
      Regular Visitor
      6 years ago

      PBI_newuser, If I had to guess, I think it would have to be in the last function where we subtract 1 from the hour. If the time that the ticket is closed is at the midnight hour (0:00) then it could return -1, which is an invalid time.

       

      Try throwing an if statment in to say if hour = 0, then 23, else -1. Something like this:

      DATEDIFF (
                TIME ( IF ( HOUR ( End_Date ) = 0, 23, HOUR ( End_Date ) - 1), 0, 0 ),
                TIMEVALUE ( End_Date ),
                MINUTE
            )
      • PBI_newuser's avatar
        PBI_newuser
        Post Prodigy
        6 years ago

        Thanks MarcelWoodman ! It works now but the work hours is incorrect.

        Below is the column i created. Is there any error? Please help. Thanks.

        Work Hour = 
        VAR workcal =
         FILTER (
         ADDCOLUMNS (
         CROSSJOIN (
         CALENDAR ( DATEVALUE([Start Date]), DATEVALUE([End Date]) ),
         SELECTCOLUMNS ( GENERATESERIES ( 8, 17 ), "Hour", [Value] )
         ),
         "Day of week", WEEKDAY ( [Date], 2 )
         ),
         [Day of week] < 6
         )
        VAR hourcount = 
         COUNTROWS(
         FILTER (workcal,
         (
         ([Date] = DATEVALUE( [Start Date] )
         && 
         [Hour] > HOUR( [Start Date] ))
         && 
         ([Date] = DATEVALUE( [End Date] ) 
         && 
         [Hour] <= HOUR( [End Date] ))
         )
         ||
         ([Date] > [Start Date] && [Date] < [End Date] )
         )
         )
        VAR remained = 
         if(
         DATEVALUE([Start Date]) = DATEVALUE([End Date]) && HOUR([Start Date]) = HOUR([End Date]), 
         DATEDIFF([Start Date],[End Date],MINUTE),
         DATEDIFF (
         TIMEVALUE ( [Start Date] ),
         TIME ( HOUR ( [Start Date] ) + 1, 0, 0 ),
         MINUTE
         )
         + 
         DATEDIFF (
         TIME ( IF ( HOUR ([End Date] ) = 0, 23, HOUR ([End Date] ) - 1), 0, 0 ),
         TIMEVALUE ( [End Date] ),
         MINUTE
         )
         )
        VAR worktime = (hourcount*60 + remained)/60
        RETURN
         worktime