Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

Converting yyyyMMdd from whole number to date




I am having some issues with converting a column formatted as a whole number into a date format.

I have a column named "myDate", formatted as a whole number with zero decimal points.
I can't simply change the data type to "Date" so I'm trying to create a whole new column using the DAX date function.


I'm using this function:
NewDate = DATE(LEFT(Table[myDate];4);LEFT(RIGHT(Table[myDate];4);2);RIGHT(Table[myDate];2))

and it throws me this error:
An argument of function DATE has the wrong data type or the result is too large or too small (translated from swedish)


however, if I use the same function but replace the last argument (day) with a number it seems to work, allthough this obviously won't give me my desired result.


I manually created a similar CSV-file and loaded it into Power BI with the column formatted as a whole number with the exact same name (as far as I can tell) just to see if it would generate the same error but to my surprise it actually worked......

I also tried the same function on another column in my original table (also date, but formatted as a whole number) and it worked there too..

And the same function in Excel works perfectly fine..


What could be the issue with the data from my original column (Table[myDate])? I'm running out of ideas and any help would be greatly appreciated. Please note that I'm a newbie when it comes to Power BI.


Much appreciated,

Solution Specialist
Solution Specialist

Use the query editor, make sure that your myDate field is formatted as text, and add a custom column with the following statement:




Your end result should look like this:


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors