cancel
Showing results for
Did you mean:
Helper I

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Solution Supplier

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

You can do this with datediff:

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

7 REPLIES 7
Solution Supplier

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

You can do this with datediff:

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

Helper I

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

@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.

Solution Supplier

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

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

Helper I

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

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 🙂

Solution Supplier

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

@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:

Helper I

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

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:

Highlighted
Helper I

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

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

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors