Hello,
I'm looking to find the max datetime per id per day, the reason for doing this to identify the amount of times an engineer has finished there last appointment too early, once I have a solution for this it should be simple to find a solution to indentify engineers that have started late, here is an example of below of the expected results outcome:
If someone is able to assist please that will be great
Thanks
Daniel
Hello @Daniel_carle
i see, sorry for my misunderstanding.
So the easiest way to readch your goal is you need to create calendar table, since you want to see your information daily while you dont want to miss out those time information.
1. create a new table with minimum date and maximum date (this will generate calendar table within your table data range).
There are only two dates because the sample data only covers two dates.
3. Combine those two table. This will create those date for every engineer.
4. create calculated column for calculating maximum Date/Time
This DAX will calculate maximum Time. This DAX will compare date value in your original table and date value in newly made table. The result will be the maximum Time of the date.
5. lastly, create calculated column for Appointment status.
This DAX will find out the Appointment status based on Maximum Date/Time in step 4. Same as before, this DAX will compare the DateTime from your original table and newly made table then if there is a same DateTime value, it will take the value on Last Appointment column on exact DateTime.
6. plot these information into table visual. Add Engineer ID slicer.
Hope this will help you.
Thank you.
OK, i roughly got the idea. So basically you want to check the earliest time and latest time each day while it the onsite or offsite that is not the earliest or latest will be written as "No".
is this what you need?
Create new calculated column for each DAX.
Late Start (After 8:30) =
var _Time = MINX(FILTER('Sheet1','Sheet1'[AppointmentEngineerID]=EARLIER('Sheet1'[AppointmentEngineerID])&&'Sheet1'[Date]=EARLIER('Sheet1'[Date])),'Sheet1'[AppointmentOnSiteTime])
Return
IF(
ISBLANK('Sheet1'[AppointmentOnSiteTime]),
"",
IF(
'Sheet1'[AppointmentOnSiteTime]=_Time&&'Sheet1'[AppointmentOnSiteTime]>'Sheet1'[Date]+TIME(8,30,00),
"Yes",
"No"
))
Early Finish (Before 16:30) =
var _Time = MAXX(FILTER('Sheet1','Sheet1'[AppointmentEngineerID]=EARLIER('Sheet1'[AppointmentEngineerID])&&'Sheet1'[Date]=EARLIER('Sheet1'[Date])),'Sheet1'[AppointmentOffSiteTime])
Return
IF(
ISBLANK('Sheet1'[AppointmentOffSiteTime]),
"",
IF(
'Sheet1'[AppointmentOffSiteTime]=_Time&&'Sheet1'[AppointmentOnSiteTime]<'Sheet1'[Date]+TIME(16,30,00),
"Yes",
"No"
))
Helpful resources
Power BI Monthly Update - November 2025
Check out the November 2025 Power BI update to learn about new features.
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
