Forum Discussion

BroomJ's avatar
BroomJ
Frequent Visitor
9 years ago
Solved

Getting the time difference between two separate rows with a matching correlation ID

Hi All,   I'm currently using Power BI to connect to the Skype for Business CDR database in order to produce reports on the volume, duration and other such metrics of calls.   One of the metrics ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi BroomJ,

    Create a column in your table using the formula below and see my example in the following screenshot.

    Duration = 
VAR Lasttime =
    CALCULATE (
       FIRSTNONBLANK( Table1[InviteTime],""),
        FILTER(Table1,
            Table1[CorrelationID]
                =  EARLIER ( Table1[CorrelationID])
                && Table1[ResponseCode] = EARLIER ( Table1[ResponseCode])
                )
        )
    
RETURN
    IF (
        ISBLANK (Lasttime),
        0,
      DATEDIFF( Lasttime,Table1[InviteTime],SECOND)
           
    )




    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi BroomJ,

Create a column in your table using the formula below and see my example in the following screenshot.

Duration = 
VAR Lasttime =
    CALCULATE (
       FIRSTNONBLANK( Table1[InviteTime],""),
        FILTER(Table1,
            Table1[CorrelationID]
                =  EARLIER ( Table1[CorrelationID])
                && Table1[ResponseCode] = EARLIER ( Table1[ResponseCode])
                )
        )
    
RETURN
    IF (
        ISBLANK (Lasttime),
        0,
      DATEDIFF( Lasttime,Table1[InviteTime],SECOND)
           
    )




Thanks,
Lydia Zhang

BroomJ's avatar
BroomJ
Frequent Visitor
9 years ago

Hi Lydia,

 

I've just plugged the formula you posted into a column and it's doing exactly what I want.

 

Thank you very much for your help!