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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors