cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mjholland Regular Visitor
Regular Visitor

Calculate 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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Calculate Latest Start and Earliest Finish Time

Nothing like a demo workbook to flush out an error Smiley Surprised

 

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

12 REPLIES 12
Super User
Super User

Re: Calculate Latest Start and Earliest Finish Time

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
mjholland Regular Visitor
Regular Visitor

Re: Calculate Latest Start and Earliest Finish Time

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.

Super User
Super User

Re: Calculate Latest Start and Earliest Finish Time

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. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
mjholland Regular Visitor
Regular Visitor

Re: Calculate Latest Start and Earliest Finish Time

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?

mjholland Regular Visitor
Regular Visitor

Re: Calculate Latest Start and Earliest Finish Time

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])

mjholland Regular Visitor
Regular Visitor

Re: Calculate Latest Start and Earliest Finish Time

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?

Super User
Super User

Re: Calculate Latest Start and Earliest Finish Time


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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Super User
Super User

Re: Calculate Latest Start and Earliest Finish Time


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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
mjholland Regular Visitor
Regular Visitor

Re: Calculate Latest Start and Earliest Finish Time

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 44 members 807 guests
Please welcome our newest community members: