Forum Discussion

schwar29's avatar
schwar29
New Member
4 years ago

Calculating time between consecutive calls

Hey all,

 

I am pretty new to Power BI and saw some similar forums to my question, but still haven't been able to figure out how to calculate the time between calls for specific callers in my dataset. Here is a snapshot of the data. I realize that the talk time is given, but I am interested in knowing the total time between consecutive calls. Additionally, I would like to be able to calculate the idle time so I know how long the callers are idling between calls. I tried to color the rows that should be calculated together. I would like another set of columns to calculate the time between calls and idle time. I hope I made this clear enough, thank you! 

 

Index  From       Date/Time              Talk time   Idle Time   Time Between Calls   

1Person B10/20/2021 01:01 pm0  
2Person B10/20/2021 01:02 pm11  
3Person C10/20/2021 01:03 pm582  
4Person B10/20/2021 01:03 pm16  
5Person B10/20/2021 01:04 pm60  
6Person D10/20/2021 01:05 pm5  
7Person E10/20/2021 01:05 pm56  
8Person B10/20/2021 01:05 pm117  
9Person D10/20/2021 01:07 pm56  
10Person E10/20/2021 01:07 pm56  
11Person E10/20/2021 01:10 pm3  

 

Thanks!

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    schwar29 , the expected output is not very clear .

     

    You can diff between two call from same person like, in a new colum 

     

    Datediff(maxx(filter(Table, [From] = earlier([From]) && [Date/Time] <earlier([Date/Time])),[Date/Time]), [Date/Time], second)

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi schwar29 ,

     

    Can you explain why the eighth row is not colored blue. There is no way to calculate by color in power bi. You should mark by data or string instead of color.

    Please give some example for Idle Time and  Time Between Calls, or calculation logic.

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • schwar29's avatar
      schwar29
      New Member
      4 years ago

      Thanks for your response! I did not color the 8th row blue because it wouldn't be part of the calculations for any of the table that is shown. For example, time between calls for Person B in row 4 and row 5 would be the difference between the two times. However, with row 8, we would have to know the next time a call was made by Person B. Technically I could have colored it blue, but I was thinking about the calculation being between a pair of two consecutive times, and then calculating the difference between them. Does that make sense?

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        Hi schwar29 ,

         

        Try thes measures.

        Idle Time =
VAR _SameFromTable =
    FILTER(
        'Table',
        [Index] <= EARLIER( 'Table'[Index] )
            && [From] = EARLIER( 'Table'[From] )
    )
VAR _LastTalkTime =
    CALCULATE(
        LASTNONBLANK( 'Table'[Talk time], 1 ),
        FILTER(
            'Table',
            [Index] < EARLIER( 'Table'[Index] )
                && [From] = EARLIER( 'Table'[From] )
        )
    )
VAR _CurrentTalkTime = [Talk time]
VAR _IfFirstCall =
    IF(
        ISODD( MOD( COUNTROWS( _SameFromTable ), 2 ) ),
        _CurrentTalkTime,
        _CurrentTalkTime + _LastTalkTime
    )
RETURN
    _IfFirstCall

        Time Between Calls =
VAR _SameFromTable =
    FILTER(
        'Table',
        [Index] <= EARLIER( 'Table'[Index] )
            && [From] = EARLIER( 'Table'[From] )
    )
VAR _LastDateTime =
    CALCULATE(
        LASTNONBLANK( 'Table'[Date/Time], 1 ),
        FILTER(
            'Table',
            [Index] < EARLIER( 'Table'[Index] )
                && [From] = EARLIER( 'Table'[From] )
        )
    )
VAR _CurrentDateTime = [Date/Time]
VAR _IfFirstCall =
    IF(
        ISODD( MOD( COUNTROWS( _SameFromTable ), 2 ) ),
        0,
        _CurrentDateTime - _LastDateTime
    )
VAR _Time =
    FORMAT( _IfFirstCall, "hh:nn:ss AMPM" )
RETURN
    MINUTE( _Time ) + HOUR( _Time ) * 60

        I setted the first call as zero in Time Between Calls.

        Pbix in the end.

         


        Best Regards

        Community Support Team _ chenwu zhu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.