Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

    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āœŒļø!!

VahidDM's avatar
VahidDM
Super User
4 years ago

Hi Anonymous 

 

You need to add a Index [rank] column to your table then use that column to find the previous job.

Can you share your data in a table format to be able to copy paste that?

 

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your KudosšŸ™!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi VahidDM 

     

    Below is the data in a table format, is this sufficient?

     

    Driver IDAccept TimeDrop Timeaccept_is_after_previous_jobDuration (mins)
    45621/09/2021 10:3021/09/2021 10:40110
    45621/09/2021 10:4521/09/2021 11:00115
    45621/09/2021 10:4621/09/2021 11:10010
    45621/09/2021 11:3021/09/2021 11:45115
    45621/09/2021 11:3021/09/2021 11:55010
    45621/09/2021 11:3221/09/2021 11:5803
    41021/09/2021 11:2521/09/2021 11:45120
    41021/09/2021 11:3021/09/2021 11:5005
    41021/09/2021 11:3121/09/2021 11:5505

     

    Ary

    • VahidDM's avatar
      VahidDM
      Super User
      4 years ago

      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āœŒļø!!