Compare records with overlapping date / time

[Anonymous]

Hi,

I would like to know if during 5 minutes 2 entrances were not working:

I have a column with the trailernumber

I have a column with a TruckArrival date and time.

I have a column with a BarrierOpen date and time.

I have a calculated column: Time Diff = DATEDIFF('dwh WHS201_TRUCKMOVEMENTS'[TruckArrival],'dwh WHS201_TRUCKMOVEMENTS'[BarrierOpen],MINUTE)

So I can see if at some point trailers weren't able to enter for 5 minutes.

However I need to know if at least 2 entrances were blocked for example:

truck a arrived at 03/03/2018 14:10:10 and the barrier opened at 14:20:10

truck b arrived at 03/03/2018 14:05:00 and the barrier opened at 14:16:00

In this case the time between 14:10:10 - 14:16:00 indicates there was more than 5 minutes a problem.

Does anyone have an idea how to deal with this?

Kind regards,

Serge

[paintmynumbers · Accepted solution]

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

[Anonymous]

Hi @paintmynumbers,

This is exactly what I needed!

Thank you very much!

Kind regards,

Serge

[v-yuezhe-msft]

@Anonymous,

Check if the following measures return your expected result, if not, please post expected result based on the above sample  table.

Measure = CALCULATE(COUNTA('dwh WHS201_TRUCKMOVEMENTS'[TrailerNumberincoming]),FILTER('dwh WHS201_TRUCKMOVEMENTS','dwh WHS201_TRUCKMOVEMENTS'[Time Diff]>5))

checkblock = IF([Measure]>=2,1,0)

Regards,

Lydia

[Anonymous]

@v-yuezhe-msft,

Thank you for replying but it is unfortunately a little more complicated than that.

If you change in your table for TrailerNumberincoming 614 the BarrierOpen to 3/10/2018 14:00:00,

So you have: 614 3/10/2018 13:44:33 vs 3/10/2018 14:00:00 which is more than 5 minutes

This record should not be in the result because I only require the records with 2 overlapping results

Maybe this picture helps. It is a timeline from 00:00 until 23:59 for a specific day

I am only interested in the information when at least 2 trailers overlap in their waiting time.

The first two have an overlapping time, while the last one doesnt.

Kind regards,

Serge