Forum Discussion

topazz11's avatar
topazz11
Helper III
6 years ago
Solved

max date

I need help for this DAX...I created to get max date from the timestamps, but it gives me date with 12:00 AM.

How can I get the exact same date?

Thanks,

 

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

    Hi topazz11 ,

     

    We can use two ways to meet your requirement.

     

    1. Create a measure using DATE function,
    Measure =
VAR a =
    DATE ( YEAR ( MAX( 'Table'[ingestion_ts] ) ), MONTH ( MAX ( 'Table'[ingestion_ts] ) ), DAY ( MAX ( 'Table'[ingestion_ts] ) ) )
VAR b =
    CALCULATE (
        MAX ( 'Table'[ingestion_ts] ),
        FILTER (
            ALLSELECTED('Table'),
            DATE ( YEAR (  'Table'[ingestion_ts]  ), MONTH (  'Table'[ingestion_ts]  ), DAY ( 'Table'[ingestion_ts]  ) ) = a
        )
    )
RETURN
IF(MAX('Table'[ingestion_ts])<>b,BLANK(),b)

     

     

     

    1. Create a measure using FORMAT function,
    Measure 2 =
var t = FORMAT(MAX('Table'[ingestion_ts]),"YYYYMMDD")
var t2 = CALCULATE(MAX('Table'[ingestion_ts]),ALLSELECTED('Table'),FORMAT('Table'[ingestion_ts],"YYYYMMDD")=t)
return
IF(
t2=MAX('Table'[ingestion_ts]),t2,BLANK())
     

     

    BTW, pbix as attached.


    Best regards,

     

     

3 Replies

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi topazz11 ,

     

    You can try removing the ".[Date]", so you code will look like:

    MAXX('Table'; 'Table'[Date])
     
    I think using the ".[Date]", you are telling DAX to just get the date part, kind of convertion.
     
    Ricardo
  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi topazz11 ,

     

    We can use two ways to meet your requirement.

     

    1. Create a measure using DATE function,
    Measure =
VAR a =
    DATE ( YEAR ( MAX( 'Table'[ingestion_ts] ) ), MONTH ( MAX ( 'Table'[ingestion_ts] ) ), DAY ( MAX ( 'Table'[ingestion_ts] ) ) )
VAR b =
    CALCULATE (
        MAX ( 'Table'[ingestion_ts] ),
        FILTER (
            ALLSELECTED('Table'),
            DATE ( YEAR (  'Table'[ingestion_ts]  ), MONTH (  'Table'[ingestion_ts]  ), DAY ( 'Table'[ingestion_ts]  ) ) = a
        )
    )
RETURN
IF(MAX('Table'[ingestion_ts])<>b,BLANK(),b)

     

     

     

    1. Create a measure using FORMAT function,
    Measure 2 =
var t = FORMAT(MAX('Table'[ingestion_ts]),"YYYYMMDD")
var t2 = CALCULATE(MAX('Table'[ingestion_ts]),ALLSELECTED('Table'),FORMAT('Table'[ingestion_ts],"YYYYMMDD")=t)
return
IF(
t2=MAX('Table'[ingestion_ts]),t2,BLANK())
     

     

    BTW, pbix as attached.


    Best regards,

     

     

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    Hi topazz11 ,

     

    How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


    Best regards,