cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DRossi Helper I
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
EricHulshof Solution Supplier
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)

 

View solution in original post

7 REPLIES 7
EricHulshof Solution Supplier
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)

 

View solution in original post

DRossi Helper I
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.

datediff.PNG

EricHulshof Solution Supplier
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? 🙂

DRossi Helper I
Helper I

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

@EricHulshof  Please see tables

Engineer nameDateOn Site time

Daniel01/01/202008:20:00
Daniel01/01/202009:10:00
Daniel01/01/202013:10:00
Fred01/01/202008:16:00
Fred01/01/202010:20:00
Fred01/01/202012:10:00
Daniel 02/01/202010:00:00
Daniel02/01/202011:00:00
Daniel02/01/202012:15:00
Fred02/01/202009:13:00
Fred02/01/202009:45:00

 

Engineer nameDateShiftStart

Daniel01/01/202008:00:00
Fred01/01/202008:00:00
Daniel02/01/202008:00:00
Fred02/01/202008: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 Solution Supplier
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:

Knipsel.PNG

DRossi Helper I
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
DRossi Helper I
Helper I

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

sumup.PNG

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

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

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