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 UserHello 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.
Helper IHello Irwan
Thank you so much for your help, I've gor the information I require to move forward
Thanks
Daniel
- Irwan
- Daniel_carle
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.
- Irwan
hello Daniel_carle
i think this will be different case from before.
Previously, you need to find the latest time each day for EngineerID, and I believe the result is matched to your need (which this post title also says "Max Per Day Per ID").
Now, correct me if i am wrong, but now you want to find the earliest time each day for EngineerID?
From where we left before, the easiest way to do this is do union table since you need all value for max time and min time. To do union, you need to match both table header so you need to rename table header first.1. Change previous 'DateTime' Table name into something else (i.e I am using 'Max Time' since this table will calculate max time each date for every engineerID). Also rename 'Max Time' Column into 'Time'.Before:After:2. Create new table to calculate min time each day for every engineerID.Min Time = GENERATE('Calendar','Engineer')3. create calculated column to calculate minimum time each day and Last Appointment.
Time = MINX(FILTER('Table','Table'[Date]='Min Time'[Date]),'Table'[Offsite Date/Time])Last Appointment = MINX(FILTER('Table','Min Time'[Time]='Table'[Offsite Date/Time]),'Table'[Last Appointment?])4. Create new table to union both 'Max Time' table and 'Min Time' table.
DateTime = UNION('Max Time','Min Time')Comparing to your provided sample data, this result match to your data. Green is the latest time each day while Red is the earliest time each day.5. Create calculated column for status.Status =IF('DateTime'[Time]>TIME(08,30,00),"Late",IF('DateTime'[Time]<TIME(16,30,00),"Early","Good"))Since you only provide time as conditional status, the result is kinda off so i will leave it to you to modify it. Change the conditional if to what you need.
6. Lastly, plot the value into your visual
Hope this will help you.
Thank you.