We have the following table with Request ID, date time value on when it was created and when it was resolved, and we need to calculate the hours it takes for each request to be completed.
Our team works from 8 AM to 5 PM, Monday to Friday.
First, make sure you create a calendar table for your data, for simplicity I created one from 1/1/2021 to 12/31/2022.
Add the following calculated columns in your Calendar Table
//if Saturday or Sunday, then workday = 0, else workday = 1
Workday = If(Weekday(Calendar_Table[Date]) = 7 || Weekday(Calendar_Table[Date]) = 1, 0,1)
//indicate start time of shift, we’ll add the date of each day to this hour
Start =
var workDayStart = time(8,0,0)
var eachdate = DATEVALUE(Calendar_Table[Date])
return eachdate + workDayStart
//indicate end time of shift, we’ll add the date of each day to this hour
End =
var workEndStart = time(17,0,0)
var eachdate = DATEVALUE(Calendar_Table[Date])
return eachdate + workEndStart
This is how your calendar table should look
Now, go back to your transactions table and add a calculated column
Resolution Time (Hrs) =
VAR _Start = 'Request Data'[Created At] //when request was created
Var _End = 'Request Data'[Resolved At] // when request was resolved
Return SUMX(
CALCULATETABLE(
Calendar_Table,
DATESBETWEEN(Calendar_Table[Date],_Start,_End), //include days between creation and resolved dates
Calendar_Table[Workday] = 1 //include only working days, workday = 1 (exclude weekends, workday = 0)
),
MAX(MIN(Calendar_Table[End],_End) - MAX(Calendar_Table[Start],_Start),0) * 24 //calculate hours
)
You’re all set! Short and pretty, right?
