Dates not being recognised correctly in Power BI

[Anonymous]

Hi folks, 

I have hit a deadend here while trying to make Power BI (PBI) recognise the dates, and its recognising some, and throwing error for some, specifically, where ever the date is greater than 12. 

Dates and error

 Here is the full scenario;

I am importing dataset from a SSRS report into PBI using the Desktop application. In the SSRS report, I have set report locale to en-AU.

Locale of SSRS report

And, have explicitly set the locale, and formatting for the date field

Locale and Format of date field/column

I am importing this particular report as a CSV file into Power BI

Importing from SSRS into PBI as CSV

I am hoping to resolve this in a way where I can get PBI Desktop to recognise the dates without having to implement any custom DAX and/or M formula, simply to keep the simplicity of importing data into PBI & PQ intact. 

Help please!

Thanks

Kaz

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

Based on my understanding, you have created a SSRS report and export it to a CSV file. Then you get data from this CSV file in Power BI Desktop, when you convert the [InvoiceDate] values to Date type, the error displays when the first part of the date value is grater than 12, right?

Based on my test, the issue can be caused by that the Local for the Power BI Desktop is not corresponding to the converted date value. Suppose the Local is English( United States), the valid date format is MM/dd/yyyy. If the value is 27/06/2014, when it's converted to date type value, as there is no month "27", the error throws out. In your scenario, please change Local value as English(Australia) then click Refresh Preview button in Query Editor.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[Anonymous]

Thanks @v-qiuyu-msft. For some strange reason, PBI Desktop decided the change the regional setting for this particular PBIX file to English US. If I open a new PBI Deskop instance, its set to English Australia, but opening this particular file changes it back to English US. Changing the Locale back to English Australia explicitly, and then doing the refresh doesn't give me the dates error. 

Thanks for your help, appreciate.

Kaz