Power BI Custom Format String Examples, Part 2: Dates And Times

In my last post I showed lots of examples of how Power BI’s new custom format string feature can be used to format numbers. This post, looking at dates and times, will be a bit different for two reasons: there are a lot more useful examples of custom date and time formats built into Power BI Desktop, and some of the format placeholders listed in the VBA documentation aren’t supported in Power BI. As a result I’m going to concentrate on some useful formats that aren’t covered well by the examples and highlight a few things that aren’t possible right now.

Here’s the table containing the sample data, in the form of date/time values, for my examples:

image

[Note that the dates shown above are formatted in dd/mm/yyyy format]

The reason I’ve used date/time values for my examples is that they can be used to demonstrate formats for values of data type date and data type time, as well as data type date/time. As with my previous post I’m going to create a series of measures to show the effects of different format strings, each with the same DAX definition:

Eg1 = SELECTEDVALUE(Examples[DateAndTime])

With the default format of :

image

…applied, here’s what the output looks like in a Power BI report:

image

Dates

Let’s start by looking at date formats. The first thing to point out is that you can format a date/time so it only shows the date part and not the time and vice versa. For example, applying the custom format string:

dd/mm/yyyy

image

…where dd is day number, mm is month number and yyyy is a four digit year, gives you:

image

If you’re American and want your months to come before your days you can simply swap the dd and the mm, for example with the format string:

mm/dd/yyyy

image

You’re not forced to use a / as your separator in a date; in fact you can use any character. For example, the custom format:

dd*mm*yyyy

…gives you:

image

If you don’t want a leading zero in front of your day or month number you can use a single d or m, and if you want a two-digit year you can use yy instead of yyyy. So, for example:

d/m/yy

…gives you:

image

You can add full day names and month names using dddd and mmmm, so the format:

dddd dd mmmm yyyy

…gives you:

image

You can also get abbreviated day and month names using ddd and mmm, so:

ddd dd mmm yyyy

…gives you:

image

Last of all, for dates, if you want to make it clear that you’re not showing really, really, really old data you can put a g on the end of your date format like so:

dd/mm/yyyy g

…and you get the following:

image

Although the VBA documentation talks about showing day number of week, week number and quarter number, I haven’t found a way of making that work in Power BI (although I may have missed something and it may be possible).

Times

Times are a bit more straightforward. The main placeholders are hh for hours, mm or nn for minutes and ss for seconds. So for example:

hh:mm:ss

and

hh:nn:ss

…both give you times formatted using the 24-hour clock as follows:

image

If you prefer to use the 12-hour clock you can add AM/PM onto the end of your format string, so:

hh:nn:ss AM/PM

…gives you:

image

The VBA documentation has several variants on AM/PM with slightly different outputs, but this is the only format that I could make work in Power BI.

The very last thing to mention is that, at the time of writing, although Power BI can store times with millisecond values there is no way to make milliseconds appear in a formatted time or date/time. For example in all the screenshots above there are two rows displaying 11/11/2019 15:15:15; they appear as different rows in the table because the millisecond values for each are different, but there’s no way of formatting these values to show that they are different. Hopefully this will be rectified soon; in the meantime you will need to store the millisecond part of any time or date/time separately in a different column in your dataset if you want to display it.

You can download the example .pbix file for this post here.

12 responses

  1. Is it possible to change weekday, day month format into different localized version without changing model language? E.g. with custom format strings I’m getting all possible date formats in Latvian but at the same time it is not possible to choose from any date format in English. In excel it works with TEXT(“1/1/2019″;”[$-lv-LV]dddd”) for Latvian or “[$-zh-CN]dddd” for Chinese

  2. Is it possible to change custom format string dddd dd mmmm yyyy into another language? Currently with localized formats in Latvian it is possible to choose date formats only in Latvian (which is cool, of course). But at the same time for some reports I might need some other language format at the same time. In excel it works with TEXT(“1/1/2019″;”[$-lv-LV]dddd”) for Latvian or “[$-zh-CN]dddd” for Chinese

  3. Pingback: Custom Power BI Date and Time Formats – Curated SQL

  4. Hi,

    I have a column for date column for quarter. It contains the first date in the quarter.
    EX: q1 = 2019-01-01 , q2 = 2019-04-01 , ect.

    My goal is to bring it into the model as a date so it can be used as a continuous axis. However to display the text as Q1 2019, Q2 2019 ect.

    Is this possible with the new formatting options?

    Best,
    Dan

  5. Pingback: Power BI Custom Format String Examples, Part 2: Dates And Times | Pardaan.com

    • I don’t think that’s possible- adding a ‘th’ would be easy, but knowing whether to add a ‘th’ or ‘rd’ would be the hard part. It might be possible with dynamic format strings and calculation groups…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: