Forum Discussion
Detect Previous Job DropOff Time
Hi there, Hope you are doing well. I need help for the below. Duration (mins) is the column I need to create. Grouping by Driver ID, the blue highlighted are using [Drop Time] - [Accept Tim...
Hi Anonymous
First add a new column with below code to rank the rows by Drop time:
Rank = RANKX ( FILTER ( 'Table', 'Table'[Driver ID] = EARLIER ( 'Table'[Driver ID] ) ), 'Table'[Drop Time], , ASC, DENSE )Then use below code to add a Duration (mins) column:
Duration (mins) = VAR _Drop = 'Table'[Drop Time] VAR _DropRank = 'Table'[Rank] VAR _LDrop = CALCULATE ( MAX ( 'Table'[Drop Time] ), FILTER ( ALL ( 'Table' ), 'Table'[Rank] = ( _DropRank - 1 ) && 'Table'[Driver ID] = EARLIER ( 'Table'[Driver ID] ) ) ) VAR _AT = 'Table'[Accept Time] RETURN IF ( _AT > _LDrop, DATEDIFF ( _AT, _Drop, MINUTE ), DATEDIFF ( _LDrop, _Drop, MINUTE ) )Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!
Hi VahidDM
Below is the data in a table format, is this sufficient?
| Driver ID | Accept Time | Drop Time | accept_is_after_previous_job | Duration (mins) |
| 456 | 21/09/2021 10:30 | 21/09/2021 10:40 | 1 | 10 |
| 456 | 21/09/2021 10:45 | 21/09/2021 11:00 | 1 | 15 |
| 456 | 21/09/2021 10:46 | 21/09/2021 11:10 | 0 | 10 |
| 456 | 21/09/2021 11:30 | 21/09/2021 11:45 | 1 | 15 |
| 456 | 21/09/2021 11:30 | 21/09/2021 11:55 | 0 | 10 |
| 456 | 21/09/2021 11:32 | 21/09/2021 11:58 | 0 | 3 |
| 410 | 21/09/2021 11:25 | 21/09/2021 11:45 | 1 | 20 |
| 410 | 21/09/2021 11:30 | 21/09/2021 11:50 | 0 | 5 |
| 410 | 21/09/2021 11:31 | 21/09/2021 11:55 | 0 | 5 |
Ary
Hi Anonymous
First add a new column with below code to rank the rows by Drop time:
Rank =
RANKX (
FILTER ( 'Table', 'Table'[Driver ID] = EARLIER ( 'Table'[Driver ID] ) ),
'Table'[Drop Time],
,
ASC,
DENSE
)Then use below code to add a Duration (mins) column:
Duration (mins) =
VAR _Drop = 'Table'[Drop Time]
VAR _DropRank = 'Table'[Rank]
VAR _LDrop =
CALCULATE (
MAX ( 'Table'[Drop Time] ),
FILTER (
ALL ( 'Table' ),
'Table'[Rank] = ( _DropRank - 1 )
&& 'Table'[Driver ID] = EARLIER ( 'Table'[Driver ID] )
)
)
VAR _AT = 'Table'[Accept Time]
RETURN
IF (
_AT > _LDrop,
DATEDIFF ( _AT, _Drop, MINUTE ),
DATEDIFF ( _LDrop, _Drop, MINUTE )
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos✌️!!