cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vishy86
Post Patron
Post Patron

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 Data Type -> Date does not Format YYYYMM. 

 

How do I achieve this without changing the Date Type "Date"? This column is used in Relationships while joining to other tables. Please advise.

 

Thanks,

Vishy

1 ACCEPTED SOLUTION

Hi,

 

In the Date Table, write this calculated column formula

 

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

 

In the slier, drag this column.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
Ashish_Mathur
Super User
Super User

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.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

 

Is there any way to do this using DAX? 

 

Regards

Vishy

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

Hi,

 

In the Date Table, write this calculated column formula

 

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

 

In the slier, drag this column.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hello Ashish, 

I have similar kind of problem. 

 

When I use above solution, then I am not able to convert "YYYYMM" format to Date. It shows as text. I want this format to be converted to date as I am performing time intelligence calculations on it.

 

Do you know anyother solution for it ?

 

Thank you for help. 

 

Hi,

Try this calculated column formula

=date(left(Data[Date],4),right(Data[Date],2),1)

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

This is another column you have created in the Calendar Table.  Since the Date column stays intact and that is the column you hae built the relationship to, all measures and columns will work as expected.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you Ashish for your time and inputs.

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.