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,

     

     

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,