Forum Discussion

Daniel_carle's avatar
Daniel_carle
Helper I
2 years ago
Solved

Max Per Day Per ID

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

 

 

Need Help
  • Irwan's avatar
    Irwan
    2 years ago

    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.

24 Replies

  • Irwan's avatar
    Irwan
    Super User
    2 years ago

    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's avatar
      Daniel_carle
      Helper I
      2 years ago

      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's avatar
        Irwan
        Super User
        2 years ago

        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's avatar
    Daniel_carle
    Helper I
    2 years ago

    Hello,

     

    The file is linked directly to my companies system, I wont be able to provide a file with this information on, apologies, hopefully a solution can be provided without the pbix file.

     

    Thanks

     

    Daniel