Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX - Extract Date from TimeStamp (contain date & time)

Dear Experts,   Which formula I can use to extract date from time stamp contains date & time?   Table looks like this on the table 'Date' [Date]   Date 1/1/2018 12:00:00AM   DAX being tried:...
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi Anonymous,

     

    Please check what data type of your Date column firstly.

     

    If your Date column is Date/Time type in Power BI, you only need to change the Data format to be M/d/yyyy under Modeling.

     

     

    If your Date column is Text type in Power BI, you could create the measure with the fomula below.

     

    Measure 2 = LEFT(MAX('Table2'[Date]),9)

    Here is the result.

     

     

    Best Regards,

    Cherry

  • Anonymous's avatar
    Anonymous
    7 years ago

    v-piga-msft I don't have access for modelling the SQL database.

     

    The measure looks legit, I will try on it. Thank you :)

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

Hi,

 

Try this calculated column formula

 

=INT('Date'[Date])

 

Format this column as Date.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Ashish & all experts here,

     

    Thanks for all your advice, i guess i have to check on the source of my 'Date' [Date] table column, because in the formula, it has the red underlined. 

    • v-piga-msft's avatar
      v-piga-msft
      Icon for Resident Rockstar rankResident Rockstar
      7 years ago

      Hi Anonymous,

       

      Please check what data type of your Date column firstly.

       

      If your Date column is Date/Time type in Power BI, you only need to change the Data format to be M/d/yyyy under Modeling.

       

       

      If your Date column is Text type in Power BI, you could create the measure with the fomula below.

       

      Measure 2 = LEFT(MAX('Table2'[Date]),9)

      Here is the result.

       

       

      Best Regards,

      Cherry

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        v-piga-msft I don't have access for modelling the SQL database.

         

        The measure looks legit, I will try on it. Thank you :)

  • maclura's avatar
    maclura
    Icon for Resolver I rankResolver I
    4 years ago

    Hi Ashish_Mathur,

     clean and neat, the best solution for me.

    m

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

      Thank you.