Forum Discussion

rkalantan's avatar
rkalantan
New Member
4 years ago

Difference between two flight arrival & departure

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 AlphaKeyDept StaArvl StaDept TimeArvl TimeWeekday
JEDDXBDXBJEDDXBJED5:056:00Monday
JEDCAIJEDCAIJEDCAI8:0510:30Monday
JEDCAICAIJEDCAIJED11:0513:00Monday
JEDDXBJEDDXBJEDDXB15:0517:00Monday

 

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 ?

Need Help

2 Replies

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      rkalantan
      New Member
      4 years ago

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