Forum Discussion
Advocate IICalculate Latest Start and Earliest Finish Time
Hi,
My data has 2 columns - StartTime and EndTime - generated when an employee completes a visit to a customer. I need to use this information to work out the Earliest and Latest Start and Finish Times for each employee each week.
I'm able to work out the Earliest Start Time using MIN and the Latest Finish Time using MAX, but the Latest Start and Earliest Finish are causing me problems. Below I've given a simplified example of some data:
Day StartTime(Min) EndTime(Max)
Monday 08:00:00 17:00:00
Tuesday 09:00:00 16:30:00
Wednesday 08:30:00 17:10:00
Thursday 08:10:00 16:50:00
Friday 09:10:00 17:30:00
Earliest 08:00:00 16:30:00
Latest 09:10:00 17:30:00
So the Earliest EndTime looks at the MAX finish time across each day and takes the MIN value of these to get 16:30:00. Similarly the Latest StartTime takes the MIN start time across each day and takes the MAX value to get 09:10:00.
Hopefully this makes sense. Can anyone help?
Thanks,
mjholland
Nothing like a demo workbook to flush out an error :smileysurprised:
Try this
LatestStart = MAXX(values('Calendar'[Day]),CALCULATE(min(Data[Start Time])))
I missed the CALCULATE - sorry.
Here is the workbook demo https://www.dropbox.com/s/u6o1p0rpzuwnl4y/latest%20start.pbix?dl=0
12 Replies
- MattAllington
Community Champion
It doesn't really make sense to me. If start time is in its own column, and you can work out the earliest start time with min, why can't you just use max to get the latest start time?
maybe you have multiple start times and finished times for each person for each day - is that what you mean?
it is tricky to help you without seeing the entire data model. You probably need to do something like minx(values(calendar[day]),max(data[finish time])) and the opposite for start time.
- mjholland
Correct - I have multiple start and end times for each person each day. So MIN works fine in these instances but for the Latest StartTime I need to work out the MIN for each day and take the MAX of that value to get the result for that week.
Let me know what you would need to see from the data model and I'll attached a copy.
- MattAllington
Does my previous formula make any sense to you? I have assumed table names and column names. Yo need to iterate over the days with minx to find the earliest of the end times and maxx to fine the latest of the start times.
