Time Difference between lines

[Malcolm]

Dear Community, good day!

I need to verify the date difference between two time stamps which are not in the same line?!?

The data model has a booking reference as identifier, from / to locations with its dates and a sequence. 

This theoretical model has a booking eg 1234 which has 3 train connections. From A to B, from B to C, from C to D

I need to check that the between arrival B to departure C is no longer then 2 days and of course show if that kind of delay is the case. 

Preferably DAX but also Power Query would be great. Basically appreciate any approach that can solve it

Expected result would be :

Booking = 1234 : 

Between Arrival  in Transit Station A (Sequence 1) and Departure from Transit Station A (Sequence 2) no delay that needs actions

Between Arrival in Transit Station B (Sequence 2) and Departure from Transit Station B (Sequence 3) delay exceeds 2 days and should be displayed

Booking = 2345 : 

No delays exceeding 1 day and nothing to report

Booking Ref	Train	Departure Station	Departure Date	Arrival Station	Arrival Date	Sequence
1234	Tommy	Main Station A	16.01.2023 16:00	Transit Station A	16.01.2023 23:00	1
1234	Marta	Transit Station A	17.01.2023 10:00	Transit Station B	17.01.2023 16:00	2
1234	Gilbert	Transit Station B	30.01.2023 12:00	Destination	30.01.2023 16:00	3
2345	Carla	Main Station A	23.01.2023 13:00	TT Loc A	23.01.2023 14:30	1
2345	Roberto	TT Loc A	23.01.2023 16:30	Destination	23.01.2023 18:30	2

[Malcolm]

@Greg_Deckler :

Thanks! I tried running but ran into Dax not beeing able use MAXX  for direct query. 

Will change to a data flow and test again. Just to be sure :

1 ) I coudnt find any reference to the sequence numbers. The previous date needs to be the date from the sequence before. There can be 5 or 6 sequences in row and needs to be always 3 -> looking at 2 etc

2) [Value] = Whats the reference ? In my example the booking number ? 

[v-cgao-msft · Accepted solution]

Hi @Malcolm ,

Please try this:

Column =
VAR _booking = 'Table'[Booking Ref]
VAR _arrival_date = 'Table'[Arrival Date]
VAR _departure_date =
    CALCULATE (
        MAX ( 'Table'[Departure Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Booking Ref] = _booking
                && 'Table'[Sequence]
                    = EARLIER ( 'Table'[Sequence] ) - 1
        )
    )
VAR _days =
    DATEDIFF ( _departure_date, _arrival_date, DAY )
VAR _result =
    IF ( _days >= 2, "Delay" )
RETURN
    _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Malcolm]

@v-cgao-msft : Thanks! Exactly what I needed. You nailed it!

[Greg_Deckler]

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