Dax datevalue() Date format incorrect - Australian Locale

[dougalh]

Hi

When entering dates in dax using datevalue() , with Locale set to English(Australia) I'm getting the wrong date.

The Australian locale should read dates as dd/mm/yyyy but it only accepts this format when dd is greater than 12, otherwise reads as mm/dd/yyyy

datevalue( 1 & "/" & 12 & "/" & 2022) returns 12th of January, 2022 which is the wrong format. Should be 1st of December.

datevalue( 13 &"/" & 12 & "/" & 2022) returns 13th of December this is correct.

datevalue( 12 &"/" & 1 & "/" & 2022) returns 1st of December 2022 which is the wrong format Should be 12th of January.

datevalue( 13 &"/" & 1 & "/" & 2022) returns 13th of January 2022 this is the correct.

It seems like only when the first value is greater than 12 does it change to the correct format.

[Anonymous]

Hi @dougalh 

When converting, DATEVALUE uses the locale and date/time settings of the model to determine a date value. If conversion using the locale and date/time settings of the model fails, DATEVALUE will attempt to use other date formats.  

So you should check the the locale and date/time settings in your system . More info can refer to the following URL .

https://learn.microsoft.com/en-us/dax/datevalue-function-dax#remarks

Best Regards,
Community Support Team _ Ailsa Tao

[dougalh]

Hi @Anonymous 

I understand this is a fall back - which is what I have exploited for the workaround (entering 13/mm/yyyy to force it to read in Australian format).

I have triple checked all my systems and PBI settings are set to Australian: dd/mm/yyyy, and this is why I am raising this issue - the default DAX is reading the format incorrectly as mm/dd/yyyy.

I believe there is an issue with the English (Australian) Locale settings in PBI.

[alancs]

I am having the exact same issue. Apparently it's not fixed yet. Running Power BI Desktop Version: 2.122.746.0 64-bit (October 2023).

[MCACT]

I am also having the exact same issue. Has Microsoft fixed that yet?