Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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/
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.