Forum Discussion
Max 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.
Hello Irwan,
This is brilliant so far thank you, the logic has worked which is great.
How do I now show this information within another table that I've captured already for the engineers, see below screenshot, within in this view you can filter by week, month, year etc and gives me the count within the timeframe I'm selecting.
thanks
Daniel
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.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
Hello Irwan,
I'm not sure this is the correct solution for me sorry, I need to be able join this was another table but wont let me due to the information that is summarized on this table, is there a way that without summarzing the table it identifies late start and early over the past 6 months on appointment date (that way it doesnt kill my machine lol)
So I want the query to identify if the first 'OnSite' date for that engineer each day, if not state 'No' within 'Late Start (After 08:30), if it is then check to see if its after 08:30, if yes, updated 'Late Start (After 08:30) to 'Yes' if not 'No'.
Pretty much repeat this process for the 'Early Finish' using the 'Offsite' date.
Below is an example of what I'm looking for, being able to do it this way allows me to add more measures and adding into an existing table I've created.
apologies again on not being clearer on the requirements.
i dont understand.
this is third time.
the first and second requirement are good as your reply post.
what do you exactly need?
this thread originally asked to seach max datetime per id per day as you requested.
and now, you are asking not max datetime anymore as your original question. your screenshot is basically just if statement in late and early column.
why dont you provide a pbix contain of your whole data/table and tell what you want so we can have this done rather than piece by piece.
Thank you.
Hello Irwan,
Again apologies for the confusing, the requirements changed based on the business needs, essentionally you could use the pbix I already provided to be able to achieve this if its possible
so I just need and yes or no for late start and early finish with a rule that its the either first appointment of the day for late start and last appointment for the early finish.
If this is a problem based on my orginal post I create a new thread to achieve my requirements.
Thanks
Daniel
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"
))as you can see in DAX above, i am using your original table 'Sheet1' (not the summarize table).Hope this will help you.Thank you.Hello 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