Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
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 ?
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 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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |