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

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

6 Replies

    • 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

         

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