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: DATE('Date'[Date])  --> no result.


Can brief me any easy fix for this?

 

 

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 :)

16 Replies

  • 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

    =FORMAT('Date'[Date], "dd/mm/yyyy")*1

    • Manuel-Levo's avatar
      Manuel-Levo
      Regular Visitor
      4 years ago

      I know this is an old answer, but this is only calling the related/hidden date column which is not really suggested.

  • dujos's avatar
    dujos
    Icon for Helper I rankHelper I
    7 years ago

    Hello Hwang, see in the picture below the result.

    IMPORTANT use a calculated Column to FORMAT DAX function works fine.

     

    sorry the menus are in portuguese but i think you can identify the itens easy

    and the date format fuction uses FORMAT(table[field];"mm/dd/yyyy")

     

    Good Luck!!!

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Dujos,

       

      The problem is i don't have access to the modelling tab, because its a company SQL BI cube.

      I'm only able to modify the visual and and make DAX measure in the report.

       

      I have tried above solution with FORMAT( 'Date'[Date],"dd/mm/yyyy)*1 but it also doesn't provide any result.

       

       

  • tsmiley's avatar
    tsmiley
    New Member
    3 years ago

    after some trial and error this worked;

    DateTime.FixedLocalNow()

  • almirneral's avatar
    almirneral
    New Member
    2 years ago

    A função TRUNC resolve isso e é bem mais fácil de usar. Precisa apenas alterar o tipo de dados posteriormente