Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Change Date format to YYYYMM

Hi,   I have a date column in YYYYMMDD format, when I import to Power BI, the Data Type is Text.   I need to convert to Data Type "Date" and Date Format -> YYYYMM.    In the Modeling tab, the D...
Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    In the Date Table, write this calculated column formula

     

    =FORMAT('Date'[Date],"YYYYMM")

     

    In the slier, drag this column.

     

    Hope this helps.

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

Hi,

 

In the Query Editor, write this single M statement to convert YYYYMMDD to a proper date format

 

Date.From(Text.From([Date], "en-US"))

 

Hope this helps.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Ashish,

     

    Is there any way to do this using DAX? 

     

    Regards

    Vishy

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

      Hi,

       

      Write this calculated column formula

       

      =1*(DATE(LEFT('Table1'[Date],4),MID('Table1'[Date],5,2),RIGHT('Table1'[Date],2)))

       

      Format this as Date.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Ashish,

         

        I created a calculated column - 

        Year Month = 1*(DATE(LEFT('Date'[Date],4),MID('Date'[Date],5,2),LEFT('Date'[Date],0))), I only need the year and month in format "YYYYMM", not the day.
        My Date table and column is 
        Date = CALENDARAUTO( )
         
        When I use the above formula, it throws an error "Cannot convert '1/1/' of type Text to type Integer". I changed the data type of Year Month to Date.
         
        Date column from Date table is related in a 1-M relationship with the Corp Date column in the other table.