Time Intelligence - Date format changing every time the report refreshes

[SahityaYeruband]

Hi All,

The format of fct table in my report changes from - dd/mm/yyyy to mm/dd/yyyy when the report is refreshed on server.
But this format change happens only where dates are less than 12.
Eg - 11-Jun-24 & 24-Jun-24

format in PBI Desktop & Before refreshing on server	After
11/06/2024	06/11/2024
24/06/2024	24/06/2024

The date formats have been maintained as dd/mm/yyyy from query level and in pbi column formatting as well.

[v-xuxinyi-msft · Accepted solution]

Hi @SahityaYeruband 

Thanks for the reply from uzuntasgokberk .

SahityaYeruband, did you create the report in Power BI Desktop RS and then upload it to Power BI Report Server? If so, you can also try the following:

Do the following in Power Query.

In the Locale option select a locale that displays dates in day/month/year format.

Power BI reads dates according to the date format of the region. Since the month will not exceed 12, Power BI will recognize that 24 is date rather than month. However, for 11, Power BI will automatically read the data according to the date format of your region. If the format of your region is month, day, and year, Power BI will read the date according to the month, day, and year, and then display it according to the "dd/mm/yyyy" you set.

The above method forces Power BI to read the date in a day/month/year format.

Output in Report Server after refresh:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SahityaYeruband]

@v-xuxinyi-msft , thank you for the solution, i just implemented it, willhave to test it out for a few days. Hopefully it works out 🙂

[v-xuxinyi-msft]

Hi @SahityaYeruband 

Thanks for your feedback. Hope it works!😊

Best Regards,
Yulia Xu

[SahityaYeruband]

Hi @v-xuxinyi-msft,
It worked.
The issue I think was the data type of the column used in the DB Table. For testing I had also added a new column to my sql, which was to_date conversion of this text column. That worked too.

[uzuntasgokberk]

Hello @SahityaYeruband ,

From Chrome or Edge it formats automatically of your language. You can choose the correct language from the web browser.

Best Regards,
Gökberk Uzuntaş

LinkedIn: https://www.linkedin.com/in/g%C3%B6kberk-uzunta%C5%9F-b43906198/

Medium: https://medium.com/@uzuntasgokberk

İf this post helps, then please consider Accept it as solution and kudos to help the other members find it more quickly.

[SahityaYeruband]

Hi @uzuntasgokberk , 
I have made the language change in the browser and in the pbix set to default browser language. Still the same issue.

What confuses me is that for dates <12 the format changes to mm/dd where as for the other dates it remains dd/mm. all of this in the same visual ..