Forum Discussion

Vinxsta's avatar
Vinxsta
Frequent Visitor
2 years ago

DATEVALUE function recognising date as mmddyyyy instead of ddmmyyyy

I've looked through this forum and haven't found a response that solves this issue.  I have a table from ssms that comes into Power BI as a date (ddmmyyyy) and my locale is also set to UK however, i...
aduguid's avatar
aduguid
Icon for Memorable Member rankMemorable Member
2 years ago

You could convert the column to a date in power query by adding a custom column. 

 

Date.FromText(Text.Middle([YourColumnName], 4, 4) & "-" & Text.Middle([YourColumnName], 2, 2) & "-" & Text.Start([YourColumnName], 2))

 

Or in DAX

 

DATE(
    MID([YourColumnName], 4, 4),   // Year
    MID([YourColumnName], 2, 2),   // Month
    MID([YourColumnName], 1, 2)    // Day
)