PowerBI is changing my dates randomly

[nelsonmjsilva]

In the above example I am extracting the date (in text format) in [Action] to [Last Update Date] using DATEVALUE(LEFT([Action],16)) I formated the date with dd/mm/yyyy, but I noticed that some of those dates are mm/dd/yyyy in some rows (in red) and others are correct (in green).

Can anyone help?

[v-qiuyu-msft]

Hi @nelsonmjsilva,

I have reported this issue internally: CRI 80230823. Will update here once I get any information. 

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @nelsonmjsilva,

Please see below information I got internally: 

There are a few issues here so I will try to explain all of them:

1. As far as I know PowerBI Desktop is not localized for en-GB, so probably the customer is using en-US.

2. DAX is not using the OS region, it's using the model locale (I noticed the documentation specifies otherwise so I created a separate bug to fix it). The locale in the attached PBIX is en-US. This is set in one of 2 ways when the report is created and it cannot be changed after that:

   a. For the msi installed version of Desktop, this is the locale of the installer. E.g. if you download a French       installer, all the reports created with it will have French as their model locale.

   b. For the Windows Store App, the model locale can be selected from the options. But again this needs to     be done before creating the report.

3. Looking at the code, DateValue does switch the order of month and day if the input month is too big. Say the locale expects "mm/dd/yyyy" and I call DATEVALUE("20/4/2018"), the result is April 20th. This looks like a feature and not a bug so we will need to ask the PMs if the customer wishes to change this behavior.

Best Regards,
Qiuyun Yu