New notation for Date or DateTime format in Power BI

August 14, 2021
Denis Selimovic
Uncategorizedcomment 3Created with Sketch Beta.0 comments

This post is also available in: German


Quick update on the Power BI August 2021 release and this more, in case you encounter this new spelling and wonder what it is.
As the title suggests, there is a new way to create a Date or DateTime value.

If we consider the CALENDAR function as an example, then two values in the format Date must be specified as parameters. Up to now this was possible with the DATE function. For example, a calendar for the year 2021 would be created in DAX like this:

DateTable =
CALENDAR(
    DATE( 2021, 01, 01 ),
    DATE( 2021, 12, 31 )
)

If you want to have a calculation with date and time, then you have to add the TIME function:

Sales before Christmas =
CALCULATE(
    SUM( Sales[Sales Amount] ),
    Sales[OrderDate] < DATE( 2021, 12, 24 ) + TIME( 12, 00, 0 )
)

And here the notation with DATE and TIME actually becomes a bit tiresome.

Notation as text

A new option is to write a date or date and time as a simple string in the following format:

dt"YYYY-MM-DD"

Or time included:

dt"YYYY-MM-DDTHH:MM:SS"

Thus, it is possible to describe the above two examples in the following way:

DateTable =
CALENDAR(
    dt"2021-01-01",
    dt"2021-12-31"
)

Or time included:

Sales before Christmas =
CALCULATE(
    SUM( Sales[Sales Amount] ),
    Sales[OrderDate] < dt"2021-12-24T12:00:00"
)

From my point of view a very useful feature, because it saves a little bit of typing. However, I would be even happier to have an improved handling of the time calculations. But things are starting to go in the right direction.