Calculating handling time during office hours

Posted On 2021-09-16

From time to time, I stumble upon data that have a starting and an ending time. It could be opportunities in a CRM system, time between an email arrived in you mailbox until it was responded to or a very common one; A service desk case.

Calculating the amount of time between the start and end is super simple. Subtract the start time from the end time and you’re done! That is, if you want to know the full time. Sometimes, like for a Service desk, your opening hours might not be 24/7. Sometimes you want to know the total time for the full life of a case, but for handling times we need to calculate the available time within the office hours. That is the fair time when someone could have actually handled the case.

The calculation steps looks like this:

In the left case, where it was created and closed on the same date, we only need to take the time into account. There are 3 scenarios where we don’t have any handling time at all here:

1. The case was created after office hours
2. The case was closed before office hours
3. The case was created on a weekend (assuming we don’t have office hours on weekends)

In scenario 1 the case was created after we’ve closed. It’s impossible for it to be closed during office hours since it was closed the same day and thus we had 0 handling time for this case. The same logic is applied to scenario 2, only reversed. If it was closed before opening hours it must also have been created before office hours.

If we instead say that our office hours are 8 til 17, Mondays to Fridays, and a case was created at 09:00 and closed at 15:00, we want to subtract the closed time with the creation time. But not so fast! 15-9 is 6 so in this case it would be correct, but what if the case was actually created at 07:00 and closed at 13:00? 13-7 is also 6, but our opening hour is 8 so we only had 5 hours of handling time here. Here’s the calculation we need to add:

If the case was closed after 17:00, set this value to 17 otherwise set it to the actual value and subtract the opening hour value (in our case above it would be 13-8 = 5).
If the case was created before 08:00, set this value to 0 else set it to the actual value and subtract the opening hour value (in our case 7 is lower than 8, so we set 0. Had it been 09:00 we would have taken 9-8 = 1).
Now we take the first number up there and subtract the later one. In our case we get 5-0 = 5. You can test this calculation on any set up and will always get you the amount of hours the case was opened during office hours of that day.

Now let’s begin the DAX journey of this calculation! We begin by setting up the office hours like this:

Var Starthour = 8
Var EndHour = 17

// This recaculates the start and end hour to a time value readable by Power BI
Var StartHourValue = Starthour / 24
Var EndHourValue = EndHour / 24
Return

Then to complete the left step, explained above, we use this formula:

IF(CaseWasCreatedAndClosedTheSameDay,
 IF(WEEKDAY('Table'[CreatedDate],2) >= 6 || 'Table'[CreatedTime] > EndHourValue ||   'Table'[ClosedTime] < StartHourValue,
 0,


  IF('Table'[ClosedTime] > EndHourValue,
  EndHourValue - StartHourValue,
  'Table'[ClosedTime] - StartHourValue
)
 -
  IF('Table'[CreatedTime] < StartHourValue,
  0,
  'Table'[CreatedTime] - StartHourValue
)
 ),

The comma at the end above indicates more is coming and now it’s time for the right path in the flow chart! What happens if the case was not created and closed on the same date?

As mentioned we break this down into three blocks. The first block is the amount of time during the creation day. If the case was created at 11:00 we had 6 hours left of that day before closing time and so the first block would be 6 hours.

But before we get to this, there is actually 2 scenarios where the first block will be 0.

1. The creation time was after closing hours
2. The case was created during a weekend

If the above is untrue, we want the full amount of available hours by taking our closing time minus our opening time i.e. 17-8 which is 9. If the case was created exactly at 8 or before, there’s 9 available handling hours during this day.

Next we want to see how many hours to subtract, i.e. how many hours had passed of the available 9 before the case was created. If the case was created before opening hours, we set this value to 0 otherwise we set this value to whatever it is minus the opening hour. If the case was created at 11:00 we subtract 8 hours and we now know that we should reduce 3 hours from the available 9. We’re left with the 6 available hours of handling time for the first block!

It looks like this in DAX:

IF(WEEKDAY('Table'[CreatedDate],2) >= 6 || 'Table'[CreatedTime] > EndHourValue,
0,

(EndHourValue - StartHourValue) -
IF('Table'[CreatedTime] < StartHourValue, 
0 ,
'Table'[CreatedTime] - StartHourValue)
)

+

Note the + sign at the bottom. It means more is coming! This was the first block and it’s time for the second. In this block we want the amount of days between the creation and closing of the case. For each day we want to multiply by the amount of available hours in 1 office day. If the case was closed the day after it was created, there are 0 days in between. In this case we will add nothing. If the case was created at day 1 and closed at day 5, there where 3 full days in between. We calculate the time for day 1 in block 1 and we will calculate the amount of time of day 5 in block 3 so we’re left with day 2, 3 and 4. However, there is another thing to take into account here as well! We only want to count the amount of week days, not weekends.

To solve this, I’ve created a separate Calendar table with one row for each date. In that table I have a column with week day number 1-7 where 6 and 7 are Saturday and Sunday. Since I’m creating the handling time as a column in my Case table, I can make use of the actual date for creation and closed dates for this specific case. The calculation here is:

Count the amount of rows from my calendar table where the week day number is 5 or lower and the calendar date is higher than the creation date of the case and the calendar date is lower than the close date of the case. Take the amount of matching rows and multiply by the number we get from subtracting the start hour with the closing hour i.e 17-8 = 9. For each day, we get 9 hours of handling time. In the example above, we would get 3 days times 9 hours = 27 hours of handling time during office hours.

The DAX formula looks like this:

COUNTAX(
 FILTER('Calendar',
 'Calendar'[Weekday nr] <= 5 &&
 'Calendar'[Date] > 'Table'[CreatedDate] &&
 'Calendar'[Date] < 'Table'[ClosedDate]),
 'Calendar'[Date])
* (EndHourValue - StartHourValue)

+

Again, we have a + sign at the bottom since we have one last block to get this done! The last block is just like the first one, but reversed. We want to see how many hours away from the closing hours the case was closed and subtract our available hours from that. If the case was closed at 14:00, we get 6 hours of handling time during this, the last day.

Again there’s 3 scenarios here.

1. The case was closed before opening hours (0 hours handling time during the closed date)
2. The case was closed after closing hours (9 hours of handling time during the closed date)
3. The case was closed on a weekend (0 hours here as well)

If these are untrue, we calculate the closed time of the case minus the start time of the office hours to get how many hours we add to our handling time for the last day. In the example above, 14-8 is 6 so we would add 6 hours to our last block.

The formula in DAX looks like this:

IF(WEEKDAY('Table'[ClosedDate],2) >= 6 || 'Table'[ClosedTime] < StartHourValue,
0, 

IF('Table'[ClosedTime] > EndHourValue,
EndHourValue - StartHourValue,
'Table'[ClosedTime] - StartHourValue
 )
)

The last part we need to do is multiply the total time we got from all the steps above with 24 to change the decimal value into amount of hours in a human readable format.

The entire Dax formula looks like this:

Handling Time (office hours) = 
// Provide the starting hour and end hour of the working shift for a day. If you begin at 08:00, set 8, if you begin at 07:30, set 7.5
Var Starthour = 8
Var EndHour = 17

// -------- Do not change stuff under this line ------------
// This recaculates the start and end hour to a time value readable by Power BI
Var StartHourValue = Starthour / 24
Var EndHourValue = EndHour / 24
Return

IF('Table'[Skapad & Stängd samma dag],
        IF(WEEKDAY('Table'[CreatedDate],2) >= 6 || 'Table'[CreatedTime] > EndHourValue || 'Table'[ClosedTime] < StartHourValue,
            0, // If the case was created during a weekend or after closing hours, set 0 hours

            // This is what happens if the case was opened and close the same day, during a workday and before closing time            
            
                IF('Table'[ClosedTime] > EndHourValue,
                    EndHourValue - StartHourValue,
                    'Table'[ClosedTime] - StartHourValue
                    )
                -
                IF('Table'[CreatedTime] < StartHourValue,
                        0, // Set 0 hours if the case was created before starting hours
                        'Table'[CreatedTime] - StartHourValue
                    )
        ),


    // This is what happens if the case was not opened and closed during the same day

    // Check the amount of time from the first day
        
        IF(WEEKDAY('Table'[CreatedDate],2) >= 6 || 'Table'[CreatedTime] > EndHourValue,
            0, // If the case was created on a weekend or after closing hours, the first part has 0 hours

            (EndHourValue - StartHourValue) -
            IF('Table'[CreatedTime] < StartHourValue, 
                0 ,// Set 0 if the case was created before start hours
                'Table'[CreatedTime] - StartHourValue) // Subtract the amount of hours already passed in the day
        )
        
        +

    // Check the amount of full work days between created and closed date
        (
        COUNTAX(
            FILTER('Calendar',
                'Calendar'[Weekday nr] <= 5 &&
                'Calendar'[Date] > 'Table'[CreatedDate] &&
                'Calendar'[Date] < 'Table'[ClosedDate]),
            'Calendar'[Date]) * (EndHourValue - StartHourValue)
        )
        +

    // Check the amount of time from the last day
        (
        IF(WEEKDAY('Table'[ClosedDate],2) >= 6 || 'Table'[ClosedTime] < StartHourValue,
            0, // If the case was closed on a weekend of before the starting hours, set 0 hours

            IF('Table'[ClosedTime] > EndHourValue,
                EndHourValue - StartHourValue, // If the case was closed after closing hours, set full value
                'Table'[ClosedTime] - StartHourValue // Adds the amount of hours passed in the day
            )
        )
        )

) * 24 // This recalculates the time value back into hours

And this is how it looks in the table of Power BI. Note that there’s quite the difference!

It’s not unusual for a service desk case to be created on the Friday afternoon and completed on Monday morning the very next week. Notice the difference when we take office hours into account compared to when we simply look at the full time of the case:

Show the full handling time to your organizations leadership and they might say “Woa! These people have been waiting 1,5 work weeks worth of time to get this solved?” but in fact it was just a couple of hours.

When looking at how long it typically takes for a handler to close a case, it makes a significant difference if we calculate office hours instead of full hours!

No matter the dimension, the results are staggering!

And this my friends, is why we go the extra mile of creating a DAX formula of about 60 rows! Because evidently it’s worth it!