Forum Discussion

HxH's avatar
HxH
Advocate II
6 years ago
Solved

Difference in time between rows with the same ID

Hi everyone, 
I have a dataset that tracks calls made by a call center, related to different leads. Each lead has multiple calls. What I want to do is calculate the difference in time (in minutes or seconds) between each row associated with one lead, so how much time passes between each call. The data is is structured like this (let's say we have 3 calls on 2 leads)

Date     Time    LeadID

01/11    8.00     1

01/11    8.30     2

01/11    9.00     1

Each row represents a call. In this example, a new column with the difference in time since the last call should have 0 in the first row (it's the first call on lead 1), 0 on the second row (it's the first call on lead 2) and 60 minutes (or the equivalent in seconds I don't care about the unit of measure right now) in the third row (it's the second call on lead 1 and an hour has passed). 

Thanks for any help 

Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    Hi HxH,

     

    I created a new datatime column, it makes it easier to handle differences when calls are made at different days.

     

    Then you can create this calculated column:

    Time since last call =
VAR _currentID =
    CALCULATE ( SELECTEDVALUE ( 'Table'[LeadID] ) )
VAR _currentTime =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Date time] ) )
VAR _prevTime =
    CALCULATE (
        MAX ( 'Table'[Date time] );
        FILTER (
            ALL ( 'Table' );
            'Table'[LeadID] = _currentID
                && 'Table'[Date time] < _currentTime
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( _prevTime ) );
        DATEDIFF ( _prevTime; _currentTime; MINUTE );
        BLANK ()
    )

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

1 Reply

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    Hi HxH,

     

    I created a new datatime column, it makes it easier to handle differences when calls are made at different days.

     

    Then you can create this calculated column:

    Time since last call =
VAR _currentID =
    CALCULATE ( SELECTEDVALUE ( 'Table'[LeadID] ) )
VAR _currentTime =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Date time] ) )
VAR _prevTime =
    CALCULATE (
        MAX ( 'Table'[Date time] );
        FILTER (
            ALL ( 'Table' );
            'Table'[LeadID] = _currentID
                && 'Table'[Date time] < _currentTime
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( _prevTime ) );
        DATEDIFF ( _prevTime; _currentTime; MINUTE );
        BLANK ()
    )

     

    Cheers,
    Sturla

    If this post helps, then please consider Accepting it as the solution. Kudos are nice too.