Detect Previous Job DropOff Time

[arythedj]

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 Time]

The green highlighted are using [Drop Time] - previous job [Drop Time]

We need to check if the job [Accept Time] is after the previous job [Drop Time]. then use [Drop Time] - [Accept Time]

if the job [Accept Time] is before the previous job [Drop Time]. then use [Drop Time] - previous job [Drop Time]

What's the best way to solve this?

I am thinking if I have another column "accept_is_after_previous_job", this will be used as a helper?

So if value is 1, then then use [Drop Time] - [Accept Time]

If value is 0, then use [Drop Time] - previous job [Drop Time]

The questions are:

- What's the DAX for "accept_is_after_previous_job" by Driver ID?

- What's the DAX for [Drop Time] - previous job [Drop Time] by Driver ID?

Many thanks for the help.

Warm regards,

Ary

[VahidDM]

Hi @arythedj 

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🙏!!

[arythedj]

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

[VahidDM · Accepted solution]

Hi @arythedj 

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

[Greg_Deckler]

@arythedj I recently just published something for this:

Decimal Duration Converter - Microsoft Power BI Community

[arythedj]

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

[Greg_Deckler]

@arythedj 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/3395....
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