Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
HxH
Advocate II
Advocate II

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 

1 ACCEPTED SOLUTION
sturlaws
Resident Rockstar
Resident Rockstar

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.

View solution in original post

1 REPLY 1
sturlaws
Resident Rockstar
Resident Rockstar

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.