Hi all,
I am pretty new to Power BI and have a good idea of what I want to do, but not the exact code to do it. I am analysing airport data and want to know how often an aircraft is waiting to get into its parking position because the previous aircraft hasn't left yet. The events are called inblocks for when it arrives and offblocks for when it leaves. I have a table that lists, among other things, the aircraft code (aircraftRegistration), the parking position (stand) the actual inblock time (AIBT), the actual offblock time (AOBT) and the scheduled inblock time (SIBT).
Here is a sample of the table with the referenced columns. Notice that all rows inlcude SIBT, AIBT, and AOBT, and each row records an aircraft's trip through the airport, so it is a seperate arrival and departure. I know I need to group by the stand and then look for the times (count) that the SIBT for ACx (first aircraft) is greater than AIBT and less than AOBT for ACy (other aircraft), but how to structure it is killing me.
| factId | stand | aircraftRegistration | aldt | sibt | aibt | sobt | aobt | atot | |
| 1 | 11 | ECJZL | 2015-01-01 16:22:15.000 | 2015-01-01 16:55:00.000 | 2015-01-01 16:45:00.000 | 2015-01-01 17:55:00.000 | 2015-01-01 17:50:00.000 | 2015-01-01 17:57:53.000 |
| 2 | 7 | ECGRP | 2015-01-01 08:48:57.000 | 2015-01-01 09:00:00.000 | 2015-01-01 09:01:00.000 | 2015-01-01 11:30:00.000 | 2015-01-01 11:24:00.000 | 2015-01-01 11:29:34.000 |
| 3 | 7 | ECGRP | 2015-01-01 13:17:54.000 | 2015-01-01 13:30:00.000 | 2015-01-01 13:24:00.000 | 2015-01-01 16:00:00.000 | 2015-01-01 15:54:00.000 | 2015-01-01 16:00:32.000 |
| 4 | 7 | ECJQL | 2015-01-01 20:23:04.000 | 2015-01-01 20:30:00.000 | 2015-01-01 20:31:00.000 | 2015-01-01 21:00:00.000 | 2015-01-01 20:56:00.000 | 2015-01-01 21:01:03.000 |
| 5 | T1 | EIDCO | 2015-01-01 17:52:53.000 | 2015-01-01 18:25:00.000 | 2015-01-01 18:11:00.000 | 2015-01-01 19:00:00.000 | 2015-01-01 19:02:00.000 | 2015-01-01 19:11:34.000 |
