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...
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.

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.