Forum Discussion
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
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
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,
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,
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