Forum Discussion
Helper IMax Per Day Per ID
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).
Calendar = CALENDAR(MIN('Table'[Offsite Date/Time]),MAX('Table'[Offsite Date/Time]))There are only two dates because the sample data only covers two dates.
2. Summarize your engineer ID (I assumed you have multiple ID).Engineer = SUMMARIZE('Table','Table'[EngineerID])3. Combine those two table. This will create those date for every engineer.
DateTime = GENERATE('Calendar','Engineer')4. create calculated column for calculating maximum Date/Time
Max Time = MAXX(FILTER('Table','Table'[Date]='DateTime'[Date]),'Table'[Offsite 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.
Last Appointment = MAXX(FILTER('Table','Table'[Offsite Date/Time]='DateTime'[Max Time]),'Table'[Last Appointment?])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.
Super User
is this what you need then?
if this what you need, this can be achieved by conditional if in new calculated column(no need looking for max datetime since you are defining each row).
Late Start (After 8:30) =
IF(
ISBLANK('Sheet1'[AppointmentOnSiteTime]),
"",
IF(
'Sheet1'[AppointmentOnSiteTime]>'Sheet1'[Date]+TIME(8,30,00),
"Yes",
"No"
))
Early Finish (Before 16:30) =
IF(
ISBLANK('Sheet1'[AppointmentOffSiteTime]),
"",
IF(
'Sheet1'[AppointmentOnSiteTime]<'Sheet1'[Date]+TIME(16,30,00),
"Yes",
"No"
))
Helper IHello Irwan,
if you can now please check to see if its the engineers first 'OnSite' of the day and last 'Offsite' of the day, 'Yes' or 'No' will be sufficient.
Thanks
Daniel
- Irwan
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"
))Hope this will help you.Thank you. - Daniel_carle
Hello Irwan,
Its finally working!!!!!
I jhust want to thank you so much for all your support and bearing with me whilst I was going through this process, appreciate all the help on this
Thanks
Daniel
- Irwan