Compare records with overlapping date / time

Hi Serge,

What you can do is make a copy of your Truckmovements table (here: dwh WHS... (2) ) and compare the two tables for overlapping arrival and waiting times. 

It's best to build two measures, one for every unique truckarrival and one for the total.

# Trucks with overlap > 5min =
CALCULATE (
    DISTINCTCOUNT ( 'dwh WHS201_TRUCKMOVEMENTS (2)'[TrailerNumberIncoming] );
    FILTER (
        'dwh WHS201_TRUCKMOVEMENTS (2)';
        'dwh WHS201_TRUCKMOVEMENTS (2)'[TruckArrival] >= MIN ( 'dwh WHS201_TRUCKMOVEMENTS'[TruckArrival] )
            && 'dwh WHS201_TRUCKMOVEMENTS (2)'[TruckArrival] < MIN ( 'dwh WHS201_TRUCKMOVEMENTS'[BarrierOpen] )
            && 'dwh WHS201_TRUCKMOVEMENTS (2)'[TrailerNumberIncoming] <> FIRSTNONBLANK ( 'dwh WHS201_TRUCKMOVEMENTS'[TrailerNumberIncoming]; TRUE () )
            && DATEDIFF (
                'dwh WHS201_TRUCKMOVEMENTS (2)'[TruckArrival];
                MIN (
                    'dwh WHS201_TRUCKMOVEMENTS (2)'[BarrierOpen];
                    MIN ( 'dwh WHS201_TRUCKMOVEMENTS'[BarrierOpen] )
                );
                SECOND
            )
                >= 300
    )
)

If you call this function for every unique truckarrival in the original table, you will give it the right context:

# Entrance Blocks >5 min =
SUMX (
    SUMMARIZE (
        'dwh WHS201_TRUCKMOVEMENTS';
        'dwh WHS201_TRUCKMOVEMENTS'[TrailerNumberIncoming];
        'dwh WHS201_TRUCKMOVEMENTS'[TruckArrival];
        'dwh WHS201_TRUCKMOVEMENTS'[BarrierOpen]
    );
    [# Trucks with overlap > 5min]
)

I hope this is the functionality you needed.

Regards,

Stephan de Jong