Calculating time gap excluding times that overlap with the ability to filter

[walter_564]

I need a calculation which will tells me the number of minutes where there is a gap in vehicle usage times. For example, in the image below:

Yellow represents a length in time where the vehicle is being used, the time where the vehicle is not being used is in grey . I am searching for the number of minutes in grey. The issue is sometimes the times overlap. This also needs to be calculated and filterable per vehicle number.

I found the below link which was very close to what I needed but found the generateseries calculation would not look at a column to calculate lower and upper boundaries rather it needs a specific value.

I have tried to get the start work time by calculating the minimum vehicle start datetime and maximum stop date time through using the calculate function, but I then cant get past the ‘All’ step

https://community.powerbi.com/t5/Desktop/Power-BI-exclude-the-duration-of-overlapping-events-within-...

An example of the result expected would be vehicle_number SFT212A where there was a gap. Vehicle stop datetime 2019-04-07 at 05:31. The next soonest time vehicle start datetime occurred was 2019-04-07 07:22. The gap was for 111 mins.

Another example was MLF016A there was the below gap 2019-04-17 14:36 – 2019-04-17 15:31 (55 mins).

Many thanks

	vehicle Start Datetime	vehicle Stop Datetime
Vehicle_number
SFT210A	23/03/2019 21:16	23/03/2019 23:00
SFT210A	23/03/2019 23:00	24/03/2019 7:00
SFT210A	24/03/2019 7:00	24/03/2019 13:34
SFT210A	23/03/2019 21:37	23/03/2019 23:00
SFT210A	23/03/2019 23:00	24/03/2019 7:00
SFT210A	24/03/2019 7:00	24/03/2019 12:13
OOB001A	4/04/2019 15:31	4/04/2019 20:07
OOB001A	4/04/2019 11:12	4/04/2019 15:00
OOB001A	4/04/2019 15:00	4/04/2019 20:18
OOB001A	4/04/2019 11:29	4/04/2019 15:00
OOB001A	4/04/2019 15:00	4/04/2019 21:39
RIL003A	6/04/2019 0:47	6/04/2019 7:00
RIL003A	6/04/2019 7:00	6/04/2019 15:00
RIL003A	6/04/2019 15:00	6/04/2019 18:06
RIL003A	6/04/2019 0:49	6/04/2019 7:00
RIL003A	6/04/2019 7:00	6/04/2019 15:00
RIL003A	6/04/2019 15:00	6/04/2019 18:47
RIL003A	6/04/2019 0:48	6/04/2019 7:00
RIL003A	6/04/2019 7:00	6/04/2019 15:00
RIL003A	6/04/2019 15:00	6/04/2019 19:44
GTT024A	6/04/2019 18:48	6/04/2019 23:00
GTT024A	6/04/2019 23:00	7/04/2019 7:00
GTT024A	6/04/2019 19:27	6/04/2019 23:00
GTT024A	6/04/2019 23:00	7/04/2019 1:45
GTT024A	7/04/2019 7:00	7/04/2019 13:59
GTT024A	6/04/2019 20:49	6/04/2019 23:00
GTT024A	6/04/2019 20:00	6/04/2019 20:30
SFT212A	7/04/2019 2:00	7/04/2019 5:29
SFT212A	7/04/2019 7:22	7/04/2019 15:00
SFT212A	7/04/2019 15:00	7/04/2019 20:14
SFT212A	7/04/2019 2:01	7/04/2019 5:31
SFT212A	7/04/2019 7:29	7/04/2019 15:00
SFT212A	7/04/2019 15:00	7/04/2019 20:16
MN4004A	8/04/2019 15:16	8/04/2019 19:29
MN4004A	8/04/2019 7:58	8/04/2019 15:00
MN4004A	8/04/2019 15:00	8/04/2019 19:44
MN4004A	8/04/2019 7:53	8/04/2019 15:00
MN4004A	8/04/2019 15:00	8/04/2019 19:53
AWE015A	10/04/2019 8:05	10/04/2019 14:31
AWE015A	10/04/2019 8:04	10/04/2019 15:00
AWE015A	10/04/2019 15:00	10/04/2019 20:44
AWE015A	10/04/2019 7:49	10/04/2019 15:00
AWE015A	10/04/2019 15:00	10/04/2019 21:02
MLF016A	17/04/2019 15:31	17/04/2019 20:24
MLF016A	17/04/2019 8:10	17/04/2019 13:39
MLF016A	17/04/2019 7:34	17/04/2019 14:36

[wdx223_Daniel]

GapTime = 
SUMX (
    'Schedule',
    VAR _pre =
        OFFSET ( -1,, ORDERBY ( 'Schedule'[vehicle Start Datetime] ) )
    RETURN
        VAR _pre =
            MAXX (
                WINDOW (
                    0,
                    abs,
                    -1,
                    rel,
                    ORDERBY (
                        'Schedule'[vehicle Start Datetime], ASC,
                        'Schedule'[vehicle Stop Datetime]
                    ),
                    PARTITIONBY ( 'Schedule'[Vehicle_number] )
                ),
                'Schedule'[vehicle Stop Datetime]
            )
        RETURN
            IF (
                _pre > 0
                    && _pre < 'Schedule'[vehicle Start Datetime],
                VALUE ( 'Schedule'[vehicle Start Datetime] - _pre ) * 1440
            )
)

[walter_564]

Hi Daniel,

Thanks very much for your reply, I am getting an error message around the below saying the parameter is not the correct type. I have done some research but cant really find an explanation, do you know why this might not work?

Many thanks 

Alice 

                    ORDERBY (
                        'Schedule'[vehicle Start Datetime], ASC,
                        'Schedule'[vehicle Stop Datetime]
                    ),