Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Hi,
In the Date Table, write this calculated column formula
=FORMAT('Date'[Date],"YYYYMM")
In the slier, drag this column.
Hope this helps.
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.
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.
Hi Ashish,
I created a calculated column -
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.
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.
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)
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.
Thank you Ashish for your time and inputs.
You are welcome.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |