Date slicer interpreting dates as it pleases (UK and US)

[Anonymous]

Hi there,

Hope you can help me out with this one. I've got a report connected to a tabular cube. This report contains a date slicer which was intended to serve as a filter, but instead it's only causing pain and suffering. It works as expected on PBI desktop on development time, but once it's published, it works as it pleases: sometimes interprets dates in UK format and, when possible, it interprets them in US format.

Check out these examples. On the top the report, and on the bottom a query to the underlying cube.

1st example: 13th of april. Everything looks fine, the data in the report matches that in the cube.

2nd example: I go back one day to the 12th of April. Now the report tells me there is no data for this date. But the underlying cube proves it wrong.

That's strange... now what a coincidence 12 is also a month, unlike 13. Let's go to December and see what's going on there:

3rd example: Going forward to the 4th of December displays data from the 12th of April as per the previous image query against the cube.

In short, despite having everything set up to display dates in dd/mm/yyyy format (Windows regional settings, PowerBI desktop regional settings and Chrome/Firefox language settings with PBI web language set as default) PowerBI still interprets the dates in US format. Sometimes. Because for dates not compatible with US format, it displays everything correctly.

Any help or hint will be appreciated.

Regards,

Martin.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Please change your date in Power query to your local one as below.

[Anonymous]

Thanks Frank, that worked!