Calculate Latest Start and Earliest Finish Time

[mjholland]

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

[MattAllington]

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. 

[mjholland]

The only part I'm not clear on it CALENDAR[DAY]. Does this assume that there's a Calendar table?

My data doesn't reference a specific Calendar Table, but does have the CalendarWeekNumber for the date of each row. Should I use this instead?

[MattAllington]

---

@mjholland wrote:

The only part I'm not clear on it CALENDAR[DAY]. Does this assume that there's a Calendar table?

My data doesn't reference a specific Calendar Table, but does have the CalendarWeekNumber for the date of each row. Should I use this instead?

---

Calendar week number is not enough, because there needs to be a generic reference to the days of the week, not the actual date (just like the data you shared).  It is better to do this in a calendar table.  

[mjholland]

Hi Matt,

Sorry if I'm being a pest but I'm clearly missing something here. I've created a calendar table in my query, created a relationship between it and my main data and used your DAX formula but it's still not giving me the results I'm looking for. Here's the DAX formula I've used:

LatestStart = MAXX(values('Calendar'[Date]),MIN('TITO Data'[EntryTime])) 

If I'm reading this correctly it's looking for the MIN value on each Date and should return the MAX of these values? I'm still getting the same results my EarliestStart field which is just showing the MIN start time for the week, across all dates.

Where am I getting confused? Please help.

[MattAllington · Accepted solution]

Nothing like a demo workbook to flush out an error 

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

[mjholland]

You had me thinking I was losing my mind there for a second.

This works perfectly. Thanks again for your help, and patience.

mjholland

[MattAllington]

Your not being a pest.  Let's just get it sorted 🙂

The issue is you have VALUES(calendar[date]).  Look back at my formula - it uses DAY, not DATE.  the Day column in your calendar table should have Sun, Mon, Tues etc

Give me 5 mins and I will post a sample workbook for you

[mjholland]

I've tried adapting this for my data model:

maxx(values('TITO Data'[EntryDate]),MIN('TITO Data'[EntryTime]))

But it's just giving me the same as MIN('TITO Data'[EntryTime])

[MattAllington]

---

@mjholland wrote:

I've tried adapting this for my data model:

maxx(values('TITO Data'[EntryDate]),MIN('TITO Data'[EntryTime]))

But it's just giving me the same as MIN('TITO Data'[EntryTime])

---

This is not the correct adaptation. Do you have a calendar table?  Read about them here http://exceleratorbi.com.au/power-pivot-calendar-tables/

Your sample data showed days (Mon, Tues) not dates, and was looking for the max/min of the min/max over a week.  Murphy formula fist finds the min for a day, then the max for the week. Your formulas first finds the min for the day, then the max of that sing number for the day - which must be the same number. My formula needs to iterate over the days in the current filter context to find the answer. It expects a calendar table with days of week.  Then filter for a week of data, and it will work. The max of the min for a week only works at the week level. 

[mjholland]

Would SUMMARIZE work? If I used it to generate a table, summarizing my data by day and returning the MIN value. The I could take the MAX of the SUMMARIZE measure?

I've tried this but it's not working:

SUMMARIZE('TITO Data',
 'TITO Data'[TMName],
 'TITO Data'[EntryDate],
 "StartTime",minx('TITO Data','TITO Data'[EntryTime]))

Any thoughts?