idle time call log

[jacmanda]

Good day!

I have a problem I hope the community could help me with.

We have a sales organization which uses a call platform. From this platform we get an table of alle the calls made.

With call_start, call_end and salesperson_id.

I want to calculate idletime between two calls, for each call. 

So if:

call A  started at 15/03/2019 08:15AM, and lasted 5 minutes --> call end 15/03/2019 08:20AM

call B started at 15/03/2019 08:25AM - then the idle time is 5 minutes. 

In the call table there are over 50 sales reps, so it has to evaluete idle time for the sales rep in question. And not just for all calls. 

Furthermore it would be ideal to do this evaluation per day - so that we dont get 16H idletime from one day to another.

Thanks in advance to anyone who can point me in the right direction

[v-shex-msft · Accepted solution]

Hi @jacmanda ,

You can use below calculated column formula to achieve your requirement:

Idle Time =
VAR prevEnd =
    CALCULATE (
        MAX ( Table[Call_end_date] ),
        FILTER (
            ALL ( Table ),
            [User_id] = EARLIER ( Table[User_id] )
                && [Call_time_date] < EARLIER ( Table[Call_time_date] )
        )
    )
RETURN
    DATEDIFF ( [Call_time_date], prevEnd, SECOND )

Regards,

Xiaoxin Sheng

[pra137]

Hii @v-shex-msft 

Do you have any idea how to do the same in excel I want to calculate the idel time in excel

Plz help 

Thanks 

[jacmanda]

Thank you! Looks like it works like a charm 🙂

Only had to ad a (*-1) at the end, in order to get a positive value.