Forum Discussion
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, in a DAX query I've written I have to add a date to be used as a filter however, the correct outcome is only achieved by writing the date in a mmddyyyy format instead of ddmmyyyy. How can this be resolved as I'm unsure how i resolve this and I don't want to have to use the US format for my dates. Thanks!!
3 Replies
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 )The issue is that date column I'm using is already formatted as ddmmyyyy so i don't need to do anything with that column. The issue arises when i use datevalue
The issue you're encountering is likely due to how DAX handles date literals, which are interpreted in the US date format (mm/dd/yyyy) by default, even if your data and locale are set to UK (dd/mm/yyyy).
