topic Re: DATEVALUE function recognising date as mmddyyyy instead of ddmmyyyy in DAX Commands and Tips

DATEVALUE function recognising date as mmddyyyy instead of ddmmyyyy

Vinxsta — Mon, 05 Aug 2024 14:45:04 GMT

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

Re: DATEVALUE function recognising date as mmddyyyy instead of ddmmyyyy

aduguid — Mon, 05 Aug 2024 15:03:50 GMT

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 )

Re: DATEVALUE function recognising date as mmddyyyy instead of ddmmyyyy

Vinxsta — Mon, 05 Aug 2024 15:08:10 GMT

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

Re: DATEVALUE function recognising date as mmddyyyy instead of ddmmyyyy

aduguid — Mon, 05 Aug 2024 15:59:18 GMT

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).