Difference between two flight arrival & departure

[rkalantan]

Hi 

If you are familiar with transit between flights, I want to calculate the hours difference betwen arrival of flights and departure of another flights, this is sample of data:

Halph Alpha	Key	Dept Sta	Arvl Sta	Dept Time	Arvl Time	Weekday
JEDDXB	DXBJED	DXB	JED	5:05	6:00	Monday
JEDCAI	JEDCAI	JED	CAI	8:05	10:30	Monday
JEDCAI	CAIJED	CAI	JED	11:05	13:00	Monday
JEDDXB	JEDDXB	JED	DXB	15:05	17:00	Monday

the measure created will calculate the transit time from DXB-CAI & CAI-DXB taking into consideration that JED is the hub airport where all conections take place 

DXB to JED arrive to JED at 6:00 AM & JED depart to CAI at 8:05 AM, so it is 2 hours transit time at JED airpot. Also, flights from CAI arrive at JED at 13:00 PM and flights to DXB depart from JED at 15:05 so it is 2 hours connection

so how can we crete a measure to do that ?

[ERD]

@rkalantan ,

If this is what you're trying to achieve, you can try the next approach:

Create an Index column in Power Query:

Create a measure:

time_diff = 
VAR a_time = MAX ( 'Table'[Arvl Time] )
VAR b_time =
    CALCULATE (
        MIN ( 'Table'[Dept Time] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Index] > SELECTEDVALUE ( 'Table'[Index] ) )
    )
VAR diff = DATEDIFF ( a_time, b_time, MINUTE )
RETURN
    RIGHT ( "0" & INT ( diff / 60 ), 2 ) & ":"
        & RIGHT ( "0" & INT ( MOD ( diff, 60 ) ), 2 )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

Stand with Ukraine! 
1) https://bank.gov.ua/ua/about/support-the-armed-forces
USD:
BENEFICIARY: National Bank of Ukraine
BENEFICIARY BIC: NBUA UA UX
BENEFICIARY ADDRESS: 9 Instytutska St, Kyiv, 01601, Ukraine
ACCOUNT NUMBER: 400807238
BENEFICIARY BANK NAME: JP MORGAN CHASE BANK, New York
BENEFICIARY BANK BIC: CHASUS33
ABA 0210 0002 1
BENEFICIARY BANK ADDRESS: 383 Madison Avenue, New York, NY 10017, USA
PURPOSE OF PAYMENT: for crediting account 47330992708
Accounts details for other currencies (EUR|GBP|CHF|AUD|CAD|PLN) can be found here: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

[rkalantan]

Will try it and let you know ,, thanks for the hep