Forum Discussion
Average Time Between Calls - End Time Versus Start Time
I've figured it out.
I know the total hours worked each day from the first start time and last end time. I also know the total time for each call - the difference between start and end time. So if I total the time in call and subtract it from the total hours worked I get the total time between calls each day. Then all I need to do is divide this by the total number of calls made minus 1.
Here's the DAX solution:
TimeBetweenCalls = ((([TotalHoursWorked]-(sum('TITO Data'[TimeOnCall])/60)))/(count('TITO Data'[EntryDate])-(1*DISTINCTCOUNT('TITO Data'[EntryDate]))))*60
I need to be able to roll this up to an average for the total week so I've added in DISTINCTCOUNT of the EntryDate so the function removes 1 additional call for each day worked.
mjholland
mjholland You will have to do some grouping. So in power bi desktop via query editor -> under Transform tab -> click Group By and group by staff member column, date column and get minimum of call StartTime column and maximum of call EndTime column.. Then simply to get difference subtract StartTime column from EndTime column. Hope that make sense.
ankitpatira this isn't quite what I'm looking for. This would only give me the average time worked across the week. I'm trying to work out if staff members are being effective between customer visits.
Here's a small example:
Staff Name Date Customer Start Time End Time
Staff A 22nd September A 09:00 09:45
Staff A 22nd September B 10:15 10:45
Staff A 22nd September C 11:00 12:00
Staff member A makes 3 calls on 22nd September. They leave the Customer A at 9:45 and gets to Customer B 30 minutes later at 10:15. They then leave Customer B to go to Customer C, 15 minutes later. The total time between these customers is 45 minutes. So on average Staff member A is taking 22.5 minutes between calls. The first start time and last end time are excluded.
I would then need to take the average for each day and come up with an average for the total number of days worked that week.
Does that make things a little clearer?
Hi mjholland,
You can refer below steps to get daily average time.
Data table:
Calculate column:
Time Range = DATEDIFF([Start Time],[End Time],MINUTE)Measure:
Day Average =
var currentDate=LASTNONBLANK(Sheet1[Date],Sheet1[Date])
return
AVERAGEX(FILTER(Sheet1,Sheet1[Date].[Date]=currentDate),Sheet1[Time Range])Create two visuals to display the result:
Daily average(group by straff)
Daily average:
Regards,
Xiaoxin ShengAnonymous thanks for this but it's not quite what I'm looking for. I need to work out the time difference between the end of a visit and the start of the next visit.
So if a visit ends at 10:30 and the next one begins at 11:00, it has taken the staff member 30 minutes to get between appointments. I'm not interested in the length of time a staff member is in an appointment (start versus end).
Does this make sense?
MattAllington are you able to help on this one?