Find earliest arrival time for a day then compare against shift start time

[DRossi]

Morning all,

I have a data set that has multiple timestamps in columns Table1[WORK_CONDUCTED.2] that show arrival times for an engineer. There are multiple timestamps for each engineer throughout the day as they visit multiple customers. So, I'm just interested in their first Arrival time.

I also have a dataset loaded in that shows the Engineer Start time Table2[ShiftStart] for that day. 

So for for each day worked, I want to find total lost time through something like this: (Earliest or Minimum) Table1[WORK_CONDUCTED.2] - Table2[ShiftStart].

So in theory engineer arrived at 08:46:00 but their shift start time was 08:30:00 so we can then see that there was 16 minutes lost time for that day. If the engineers arrival time was earlier than their shift start time, this is fine and I don't want to capture this as it has resulted in no lost time.

I would also want it to sum up for a weeks/month or defined periods total lost time.

Quite complex I think, but I can't seem to get anything that works.

[EricHulshof · Accepted solution]

You can do this with datediff:

Lost = DATEDIFF(MAX(Table1[Workconducted]);MAX(Table2[ShiftStart]);MINUTE)

[DRossi]

@EricHulshof  Actually, that measure finds the earliest in the set of dates and latest shift in the defined period from the date slicer. So if selected one day the data is correct, but as soon as I select a wide range date period, it only finds the earliest ON Site time for that period and does the calculation for that day.

I need it to total each day and give a sum of lost time for each day.

[EricHulshof]

I assumed both starttime and arrival time were DateTime values. Can you give me some sample data to help you better? 🙂

[DRossi]

@EricHulshof  Please see tables

Engineer nameDateOn Site time

Daniel	01/01/2020	08:20:00
Daniel	01/01/2020	09:10:00
Daniel	01/01/2020	13:10:00
Fred	01/01/2020	08:16:00
Fred	01/01/2020	10:20:00
Fred	01/01/2020	12:10:00
Daniel	02/01/2020	10:00:00
Daniel	02/01/2020	11:00:00
Daniel	02/01/2020	12:15:00
Fred	02/01/2020	09:13:00
Fred	02/01/2020	09:45:00

Engineer nameDateShiftStart

Daniel	01/01/2020	08:00:00
Fred	01/01/2020	08:00:00
Daniel	02/01/2020	08:00:00
Fred	02/01/2020	08:00:00

For example, Lost time for Daniel on 1st Jan was 20 minutes and for Fred, 16 mins.

The formula needs to look for the day, from that day, what was the earliest OnSite time and compare that against the engineers ShiftStart time, thus giving the Lost time. But I also need that to sum up too as for example, if I have a slicer that selects a month period, I want it to tell me lost time for that whole period.

Thanks 🙂

[EricHulshof]

@DRossi 
Heya,

I created a relation on a Many to Many setting between the dates and used this measure.

measure = CALCULATE(Datediff(MIN(Workconducted[On Site time]);MAX(ShiftStart[ShiftStart]);MINUTE);FILTER(Workconducted;Workconducted[Date] = Workconducted[Date]))

 Result:

[DRossi]

Thanks, @EricHulshof . I aprreciate your efforts. This is returning the right way on the table as start times are 8AM, great 🙂 however, it still isn't summing up a total for the defined period. See below:

[DRossi]

Is there something I can add to DAX to  sum up?