Max Per Day Per ID

[Daniel_carle]

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

[Irwan]

Hello @Daniel_carle 

please check if this accomodate your need.

Create a measure with following DAX:

Max DateTime =

var _ID = SELECTEDVALUE('Table'[EngineerID])

var _Appointment = SELECTEDVALUE('Table'[Last Appointment?])

Return

MAXX(FILTER('Table','Table'[EngineerID]=_ID&&'Table'[Last Appointment?]="Yes"),'Table'[Offsite Date/Time])

Offsite Date/Time value is there to differentiate between days because the DAX calculates the maximum time (without Offsite Date/Time, it will only show one maximum value).

However, if you dont like the visual like above (the redundancy Date/Time value shown), you can summarize the table first with only taking Yes value in Last Appointment column.

Create new table with following DAX : 

Max Time/Date =

FILTER(

    SUMMARIZE(

        'Table',

        'Table'[EngineerID],

        'Table'[Offsite Date/Time],

        'Table'[Last Appointment?],

        "Max Time/Date",

        MAXX(FILTER('Table','Table'[EngineerID]='Table'[EngineerID]&&'Table'[Last Appointment?]="Yes"),'Table'[Offsite Date/Time])

    ),

    'Table'[Last Appointment?]="Yes"

)

Hope this will help you.

Thank you.

[Daniel_carle]

Hello Irwan,

Thanks for the quick response but I should of given more information on the requirements, apologies.

I want the 'Last Appointment' to state 'Yes' or "No" if its the last date and time of the engineer ID per day please

Thanks

Daniel

[Irwan]

Hello @Daniel_carle 

let me clarify your goal.

you want to find the maximum datetime of each engineer of each day?

 I thought you want to find out those yellow highlight.

is this what you need?

Engineeer 888 clocks 17:28 or 5:28 PM on 7/8/2024

Engineeer 888 clocks 14:00 or 2:00 PM on 7/9/2024

Thank you.

[Daniel_carle]

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

[Irwan · Accepted solution]

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.

[Daniel_carle]

Hello @Irwan 

Thank you so much for your help, I've gor the information I require to move forward

Thanks

Daniel

[Irwan]

hello @Daniel_carle 

glad to be a help.

Thank you.

[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.

[Daniel_carle]

Hello Irwan,

For some reason my table is not displaying the times, any idea why? its set to date/time across all dates within the structure

Thanks

Daniel

[Irwan]

hello @Daniel_carle 

I think you might misplace your target column when you calculate 'Time'.

Are you using 'Offsite Date/Time' or 'Date'? Time information is in 'Offsite Date/Time'.

if possible, please share your pbix, otherwise, here is the pbix if you want to check the process by yourself.

https://www.dropbox.com/scl/fi/1gsuq7wbnp9pi3tnlp022/Daniel_Carle.pbix?rlkey=gw1ayboadiws21bvpc5d47k... 

Hope this will help you.

Thank you.

[Daniel_carle]

Hello Irwan,

Really appreciate all the help on this, I've done an export directly out my system and dumped it into Excel and then into PowerBi https://drive.google.com/file/d/1_bob-MuGsDljhVU5fqATtRVsK7A4MnwM/view?usp=sharing 

This is an export of all the appointments in June 2024 to give meaningful data, so the goal is to count the amount times in the month that an engineer has either started late or finished early (this needs to be a count per day per engineer)

AppointmentOnSiteTime = to find started late date (After 08:30)

AppointmentOffSiteTime = to find early finish date (Before 16:30)

Once we establish the amount of times an engineer has started late or finished early in paticular week/month/year I will need to combine this with a view that I've already created which provides other information about the engineers.

Hopefully this time this gives a clearer picture on what I'm trying to achieve, apologies for all the confusion

Thanks

Daniel

[Irwan]

hello @Daniel_carle 

no worries. The pbix makes much easier to process as it has complete informations.

Here a comparison: 

- Original sample data

- Summarize data.

Min Time shows the earliest time of AppointmentOnSite for each date for each ID.

Max Time shows the latest time of AppointmentOffSite for each date for each ID.

i.e for 30/06/2024, Both Min Time and Max Time have show the earliest and the latest time for 4241.

Here is the visual including the count of ID that EITHER start late OR finish early.

Column 'Start Status' and 'Finish Status' are for showing status. You can delete it if you dont need those information (count measure doesnt use column value).

Here is the pbix:

https://www.dropbox.com/scl/fi/ja272gyobcqcnwvyefw3r/Daniel_Carle_v2.pbix?rlkey=n9rg4c6qciasvtzgyhps... 

Hope this will help you.

Thank you.

[Daniel_carle]

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

[Irwan]

hello @Daniel_carle 

glad to be a help.

Thank you.

[Daniel_carle]

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.

[Irwan]

@Daniel_carle 

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.

[Daniel_carle]

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

[Irwan]

@Daniel_carle 

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.

[Daniel_carle]

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