Converting wholenumber to yyyyMMdd

[robama]

Hello! 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,

Robert.

[v-qiuyu-msft · Accepted solution]

Hi @robama,

In your scenario, do all values in [myDate column] in yyyyMMdd format? eg: 20170920

Please try to create a calculated column use this DAX: 

Column = IFERROR(DATE(VALUE(LEFT('Table'[myDate],4)),VALUE(LEFT(RIGHT('Table'[myDate],4),2)),VALUE(RIGHT('Table'[myDate],2))),BLANK())

Then check if the new column has blank value, and check its corresponding myDate value to find why the error occurs originally. 

By the way, please try to run the latest Power BI desktop version. 

Best Regards,
Qiuyun Yu