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 datas...
Need Help
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.