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,

 

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Ashish,

         

        Thanks, that worked.

         

        But I had a question since we are formatting the date as a Text now using the FORMAT function, will it impact the relationship or calculations.

         

        Eg: User selects 201902 i.e. Feb 2019 in the dropdown, will the measures and calculated columns work as usual? I'm clarifying because 201902 is of Text format whereas the calculations are based on date selected by end user.

         

        Please correct me if I am understanding incorrectly.

         

        Thanks,

        Vishy