Forum Discussion

mjholland's avatar
mjholland
Icon for Advocate II rankAdvocate II
9 years ago
Solved

Average Time Between Calls - End Time Versus Start Time

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 t...
Need Help
  • mjholland's avatar
    mjholland
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
mjholland
Icon for Advocate II rankAdvocate II
9 years ago

Anonymous 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's avatar
    mjholland
    Icon for Advocate II rankAdvocate II
    9 years ago

    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