calculate time in hours extending to multiple days

[Anonymous]

Hi,

Am preparing a dashboard based on airline departure and arrival details, the issue am facing is some airlines depart from say  Washington DC  and arrives at Malaysia  after 2+ days of journey which includes layover also.  How can we calculate the total journey time 

( eg: Lufthansa flight departs from Washington Dulles Airport at 09:50 pm on 01st March 2022 and reaches Frankfurt after 8 hours of flight on 02nd March 2022 at 10:00 am.  After a  layover of 5 hours at Frankfurt airport fligth departs at 03:00 pm  and arrives at Malaysia after  12 hours flight at 05:30 am local time. ) 

Request your guidance 

regards,

dsmitha

[Anonymous]

Hi amirabedhiafi ,

This is how my input model looks like

I would like to add a column which will display the total duration of flight ( including layover time )

[amirabedhiafi]

The format of the departure and arrival date may cause you issues.

Even if it can be formatted in DAX (using a complex formula), I suggest you to update it to the right format in the source.

Otherwise, what is the formula that calculate the duration?

Also in the time zone is missing in your case, are you willing to include it ?

[Anonymous]

hi amirabedhiafi,

am ok to change the departure and arrival date format, which format will be better in this case - request your advise 

formula for calculating duration :

( ARRIVAL TIME AT TRANSIT AIRPORT  -  DEPARTURE TIME AT ORIGINA AIRPORT) +(LAYOVER TIME AT TRANSIT AIRPORT) + (ARRIVAL TIME AT DESTINATION AIRPORT - DEPARTURE TIME AT TRANSIT AIRPORT ) 

Yes, i missed out time zone, will include it

[amirabedhiafi · Accepted solution]

You can check this link below, and please keep in mind that you need the time zone in your dataset :

https://www.enjoysharepoint.com/power-bi-date-format/#:~:text=Power%20bi%20date%20format%20dd%2Fmm%2....

[Anonymous]

Thank you for sharing the link, it helped

sure, timezone will be included

[amirabedhiafi]

Can you please share your model? the input and the expected output ?