Forum Discussion

harryfasb6000's avatar
harryfasb6000
Frequent Visitor
6 years ago
Solved

How do I get Max Timestamp for Each Day using DAX

I have a table that updates 1-3x a day. Does anyone know how I can return only the max timestamp for each day using DAX. Thanks.

 

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  harryfasb6000 

    You could use this formula to create a column 

    Column = 
CALCULATE(MAX('Table'[Datetime]),FILTER('Table',FORMAT([Datetime],"yyyy/MM/dd")=FORMAT(EARLIER([Datetime]),"yyyy/MM/dd")))

    or use this formula to create a measure

    Measure = CALCULATE(MAX('Table'[Datetime]),FILTER(ALLSELECTED('Table'),FORMAT([Datetime],"yyyy/MM/dd")=FORMAT(SELECTEDVALUE('Table'[Datetime]),"yyyy/MM/dd")))

     

    Regards,

    Lin

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago
    Try this

    IF(CALCULATE(MAX(Table1[date]),ALLEXCEPT(Table1,Table1[date]))=MAX(Table1[date]),1,0)

    And add this measure to visual level filter ans set it to 1.
    • harryfasb6000's avatar
      harryfasb6000
      Frequent Visitor
      6 years ago

      Thanks. Tried that but couldnt get to work. Do I need to split the column into two ... date, time?

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago
        Yes that will make it much easier.
  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    6 years ago

    hi  harryfasb6000 

    You could use this formula to create a column 

    Column = 
CALCULATE(MAX('Table'[Datetime]),FILTER('Table',FORMAT([Datetime],"yyyy/MM/dd")=FORMAT(EARLIER([Datetime]),"yyyy/MM/dd")))

    or use this formula to create a measure

    Measure = CALCULATE(MAX('Table'[Datetime]),FILTER(ALLSELECTED('Table'),FORMAT([Datetime],"yyyy/MM/dd")=FORMAT(SELECTEDVALUE('Table'[Datetime]),"yyyy/MM/dd")))

     

    Regards,

    Lin