cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mjholland
Helper II
Helper II

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

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

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.

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.

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.

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

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.

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

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

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

 

mjholland

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



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

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

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?

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.