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.

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.
    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      7 years ago

      Hi,

       

      My formula is a calculated column formula (DAX not M).  It looks like you are trying to write my formula in the Query Editor.  I can offer further help if you share the PBI file.

       

      Also, for creating the Calendar Table, go to Modelling > New Table and write this formula there

       

      =CALENDAR(MIN(Data[Date]),MAX(Data[Date]))

       

      Now create a relationship from the Date column of your Data Table to the Date column of the Calendar Table.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Ashish,

         

        Please find the sample PBI file in the link below - 

        https://drive.google.com/file/d/1Vfo0iq57F2FJxnMQxH6QYYrBWl1XWVC9/view?usp=sharing

         

        File Name: Difference Report - Sample Data Method 1

         

        In this, the Year Month in the dropdown selections should be of format "YYYYMM". 

         

        This report is needed for a specific requirement.

         

        Let me know if you need anything else.

         

        Thanks,

        Vishy