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✌️!!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Greg_Deckler Are you spamming?
You replied 13 seconds after I posted and your answer is irrelevant to my questions.

Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

Anonymous So, if you subtract your two date/time columns and then use the link I sent where you convert that decimal time to minutes that is irrelevant and spamming. Good to know.

Duration (mins) =
  VAR __Value = ([Drop Time] - [Accept Time]) * 1.
RETURN
  TRUNC ( __Value * 24*60 )

As for the getting the previous row part of your post, I suppose I could have included the link for MTBF but it was late and I was going to bed. 

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous