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,
What you have stated as the goal is absolutely correct.
The ones in yellow are just examples of the type of dates I want to identify as they are latest date/time within the table for that engineer ID for the 7th and 8th July, so technically the 'Last Appointment' will be the measure to identify them, thank you
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.
Hello Irwan
Thank you so much for your help, I've gor the information I require to move forward
Thanks
Daniel
Hello Irwan,
Sorry to come back to this but thought solution worked but wasnt quite what I needed, let me try explain in more detail.
If you can imagine I've got a very large table of appointments across multple engineer ID's, so the goal is to find the first appointment and latest appointment per day per engineer so that I can do a count per month the amount of times an engineer has started late or finished too early, maybe we need to another appoach just to maybe just count the amount times they are late or early in a paticular week/monthy/year etc somehow.
Late = after 08:30 am
Early = Before 16:30
I've already got a table for the engineers that provides other statistics so it would also need linking to this so I can do the summary per month for management.
Hopefully this provides more clarity on what I require, I would be greatful if you can assist please.