Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
BPS
Regular Visitor

Need help with calculating correct start and end from columns to determine duration

Hello all,

 

I am looking for some assistance with finding the best way to approach the following situation:

 

I have two tables:

Table A - contains the ON and OFF date/times of equipment

Table B - contains the date/times of the phases of an aircraft turn

             - ON to IN is the Taxi In duration 

             - IN to OUT is the Turn duration

             - OUT to OFF is the Taxi Out duration

 

The only common column is the Tail #.  The values within the Tail # should relate to eachother but the number of times each tail appears in each table will vary greatly.  I need to find how many minutes was the equipment in Table A is running during the phases of the aircraft turns.  So the resulting table that I hope to acheive will show the flight information and the minutes for each of the phases.

 

I will share some sample data for each table along with the resulting table:

 

Table A:

TailONOFF
1235/1/20 8:06 AM5/1/20 8:31 AM
1235/1/20 2:46 PM5/1/20 3:55 PM
2345/1/20 4:52 PM5/1/20 5:29 PM
1235/1/20 12:00 PM5/1/20 12:23 PM
1235/1/20 5:58 PM5/1/20 10:32 PM
1235/1/20 9:33 AM5/1/20 10:01 AM
2345/1/20 6:02 PM5/1/20 8:32 PM
234`5/2/20 8:20 AM5/2/20 8:42 AM
1235/2/20 12:00 PM5/2/20 1:00 PM

 

Table B:

Flight DateFlight #Tail #OriginDestinationONINOUTOFF
5/1/2020111123ABCBCD 5/1/20 07:47 05/01/20 08:15
5/1/2020112123BCDEFG5/1/20 14:585/1/20 15:0405/01/20 17:5405/01/20 18:04
5/1/2020113234CDEDEF5/1/20 16:065/1/20 16:1605/01/20 17:2505/01/20 17:35
5/1/2020114234DEFZZY5/1/20 18:215/1/20 18:3105/02/20 08:2505/02/20 08:40
5/1/2020115123EFGABC5/1/20 18:365/1/20 18:5005/04/20 10:05 
5/1/2020116123FGHYYZ5/1/20 10:245/1/20 10:3005/01/20 12:1005/01/20 12:20

 

Results Table:

Flight DateFlight #Tail #OriginDestinationTaxi InTurnTaxi Out
5/1/2020111123ABCBCD   
5/1/2020112123BCDEFG6516
5/1/2020113234CDEDEF0334
5/1/2020114234DEFZZY1012715
5/1/2020115123EFGABC14283 
5/1/2020116123FGHYYZ000

 

A couple of explainations about the results:

Flight 111 -  resulted in blanks for all phases of flight because there was not enough data in Table B to determine duration of the phases of flight

Flight 116 - results in zeros instead of blanks because the phases of flight were determined but there was no duration of the equipment in Table A that happened during those phases.

 

Let me know if you need any additional information.  I appreciate any assistance that you may be able to provide.  

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @BPS 

When i calculate the phases according to your rules, it comes out with a different result.

But my result seems to be correct for the formula.

Please see my test below.

Turn duration = DATEDIFF(MAX(TableB[IN]),MAX(TableB[OUT]),MINUTE)

Capture8.JPG

Best Regards

Maggie

Hello @v-juanli-msft , 

 

Thank you for your response.  However I think it is missing something to determine the sum of duration of Table A that happens within the confines of Table B.  When looking at the Flight 112 that you have highlighted here, I would agree that the duration of the turn is 170 minutes however the equipment from Table A was turned off at 5/1/20 3:55 PM which stops the clock. 

 

I will share a more detailed view of how I mentally do the analysis.  In hopes it will help you or others fully understand what is happening in the example:

 

When I think through this problem I start with the two tables of of Base Data and I know I am solving for the three Phases each phase has it's own tests so I would first isolate the record in Table B that I want to look at

For this example I will use flight 114 because it is a little more interesting but faily not complex since the related data in Table A is fairly small:

 

Base.PNG

 

 

Then lay out the data from the first step and identify the potential start and end times of that phase:

 

TaxiIn.PNG

 

Turn.PNG

 

TaxiOut.PNG

 

Please let me know if this was helpful or if you still have questions. 

 

Thanks BPS

Greg_Deckler
Community Champion
Community Champion

I'm having a tough time copying and pasting your data for some reason. Can you try following the directions for posting sample data found here: Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Looks like an interesting issue. I can't tell but maybe MTBF could be helpful. 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...



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Thank you for your response.  Below I will try to paste in the tables in a different format.  Hopefully this will work better.  Also I will take a look at the other link to see if I can apply that to my problem.  

 

Table A

 

Tail,ON,OFF
123,5/1/20 8:06 AM,5/1/20 8:31 AM
123,5/1/20 2:46 PM,5/1/20 3:55 PM
234,5/1/20 4:52 PM,5/1/20 5:29 PM
123,5/1/20 12:00 PM,5/1/20 12:23 PM
123,5/1/20 5:58 PM,5/1/20 10:32 PM

123,5/1/20 9:33 AM,5/1/20 10:01 AM
234,5/1/20 6:02 PM,5/1/20 8:32 PM
234,5/2/20 8:20 AM,5/2/20 8:42 AM
123,5/2/20 12:00 PM,5/2/20 1:00 PM

 

Table B

 

Flight Date,Flight #,Tail #,Origin,Destination,ON,IN,OUT,OFF
5/1/2020,111,123,ABC,BCD,,5/1/20 07:47,,05/01/20 08:15
5/1/2020,112,123,BCD,EFG,5/1/20 14:58,5/1/20 15:04,05/01/20 17:54,05/01/20 18:04
5/1/2020,113,234,CDE,DEF,5/1/20 16:06,5/1/20 16:16,05/01/20 17:25,05/01/20 17:35
5/1/2020,114,234,DEF,ZZY,5/1/20 18:21,5/1/20 18:31,05/02/20 08:25,05/02/20 08:40
5/1/2020,115,123,EFG,ABC,5/1/20 18:36,5/1/20 18:50,05/04/20 10:05,
5/1/2020,116,123,FGH,YYZ,5/1/20 10:24,5/1/20 10:30,05/01/20 12:10,05/01/20 12:20

 

Results Table

 

Flight Date,Flight #,Tail #,Origin,Destination,Taxi In,Turn,Taxi Out
5/1/2020,111,123,ABC,BCD,,,
5/1/2020,112,123,BCD,EFG,1/6/00 00:00,2/20/00 00:00,5.916666667
5/1/2020,113,234,CDE,DEF,1/0/00 00:00,2/1/00 15:36,4.149999996
5/1/2020,114,234,DEF,ZZY,1/10/00 00:00,5/5/00 16:24,15
5/1/2020,115,123,EFG,ABC,1/14/00 00:00,10/9/00 00:00,
5/1/2020,116,123,FGH,YYZ,1/0/00 00:00,1/0/00 00:00,0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.