Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jacmanda
Frequent Visitor

idle time call log

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. idletime test.jpg

 

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

 

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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 

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.