Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to Solution.
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.
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.
User | Count |
---|---|
141 | |
70 | |
70 | |
54 | |
53 |
User | Count |
---|---|
208 | |
95 | |
64 | |
61 | |
57 |