Average Time Between Calls - End Time Versus Start Time

[mjholland]

Hi,

I have data for a number of members of staff from the beginning of 2016 to now. Each member of staff visits a different number of customers each day. These customer visits are recorded with time for the start of each visit and the end of each visit - my data has a column for each, StartTime and EndTime.

I'm trying to work out the average time taken between each call, so the difference between the EndTime of one call and the StartTime of another for each staff member, each day.

Does anyone have an idea as to how I would do this? The first StartTime and last EndTime would have to be excluded in some way I'd imagine to the report doesn't take in to consideration the time between someone finishing work and starting again the next day.

Any ideas?

[ankitpatira]

@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.

[mjholland]

@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?

[v-shex-msft]

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 Sheng

[mjholland]

@v-shex-msft 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?

[mjholland · Accepted solution]

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