cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Compare records with overlapping date / time

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

1 ACCEPTED SOLUTION
Regular Visitor

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

4 REPLIES 4
Regular Visitor

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

Frequent Visitor

This is exactly what I needed!

Thank you very much!

Kind regards,

Serge

Microsoft

@Serge_van_Dun,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors