0845 643 64 63

Dynamic Date Formats in Power BI

Which date format styles should we use if we are building a report that is being consumed internationally?

Remember, 01/12/2021 is December 1st or January 12th depending in which part of the world it is being read.

The decision may be taken from our hands if there is a company policy in place. If the company is based in the USA, for example, they may choose to use US formatted date fields as a standard for reporting across the entire business, however, if the field needs to be truly dynamic depending on the consumers location, the answer lies in this tool tip:

Explanation of dynamic date formats

There are 2 formats in the selection that are prefixed with an asterisk:

Selection of dynamic date formats
* We shall use ‘General Date’ in the examples throughout this post for reasons explained later

There are 2 variables that the Power BI Service checks when loading reports in the service.

First it will check the language setting of the user account in the service. This is set under ‘Settings >> General >> Language’. There is a dropdown option that acts as both a language and regional setting, this drives how dates are formatted when dynamic date formats are used.

Power BI service language settings

If this is set to ‘Default (browser language)’ the second variable, the browser’s default language setting, will take effect.

In Edge this is set under ‘Settings >> Language’, when multiple languages are set, the topmost one is considered the default.

Language settings in Edge

In Chrome it is set under ‘Settings >> Advanced >> Language’, this uses the same system as Edge where the topmost language is used as default.

Language settings in Chrome

Here is an example of a table loaded in a browser using both English UK and English US:

English UK
English US

This example shows that not only does the format of the date itself change (day and month have switched) but there are also visual connotations to account for. The US format uses a 12-hour clock by default and the addition of the AM/PM suffix changes the column width and drastically alters the readability of the table and potentially the entire report. It is these occurrences we need to be aware of when developing reports for international consumption.

This issue can easily be avoided by using the ‘Auto-size column width’ setting under ‘Column Headers’ on the formatting tab of the visual, or by allowing for the growth when setting manual column widths. (For a great guide on manually setting equal column widths, please read this helpful post by my colleague, Nick Edwards)

Unfortunately, this post comes with a caveat, at the time of writing it would seem there is a bug in Power BI. Remember this from earlier?

Explanation of dynamic date formats
Selection of dynamic date formats

As you can see below, both fields use the UK format of DD/MM/YYYY when the browser language is set to English UK.

Settings set to UK
UK dates

However, when the browser settings are changed to English US, only the *‘General Date’ format has changed, the *’DD/MM/YYYY’ format is still showing in the UK format even though there is an asterisk next to it in the selection list.

Settings set to US
Erroneous mix of US and UK dates

Hopefully once this issue is addressed, the use of regionally dynamic date formats will be available for both long and short formats.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

370,573 Spambots Blocked by Simple Comments

HTML tags are not allowed.

Power BI Sentinel
The Frog Blog

Team Purple Frog specialise in designing and implementing Microsoft Data Analytics solutions, including Data Warehouses, Cubes, SQL Server, SSIS, ADF, SSAS, Power BI, MDX, DAX, Machine Learning and more.

This is a collection of thoughts, ramblings and ideas that we think would be useful to share.

Authors:

Alex Whittles
(MVP)
Reiss McSporran
Jeet Kainth
Jon Fletcher
Nick Edwards
Joe Billingham
Microsoft Gold Partner

Data Platform MVP

Power BI Sentinel
Frog Blog Out
twitter
rssicon