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,

 

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

       

       

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