Forum Discussion

Daniel_carle's avatar
Daniel_carle
Icon for Helper I rankHelper 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 solu...
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.

Irwan's avatar
Irwan
Icon for Super User rankSuper 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
Icon for Helper I rankHelper I
2 years ago

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's avatar
    Irwan
    Icon for Super User rankSuper User
    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.

    • Daniel_carle's avatar
      Daniel_carle
      Icon for Helper I rankHelper I
      2 years ago

      Hello Irwan 

       

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

       

      Thanks

       

      Daniel

      • Irwan's avatar
        Irwan
        Icon for Super User rankSuper User
        2 years ago

        hello Daniel_carle 

         

        glad to be a help.


        Thank you.