THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Format measures as dates in Power BI #dax #powerbi

If you want to format a measure as date in Power BI, you need an expression returning a date/time data type. This is different from what you can do in Power Pivot and in SSAS Tabular, where you can format any numeric expression using a date and/or time format, and the conversion is implicitly done to perform such a visualization.

The problem might be not very common, because you have a numeric expression only when you start manipulating a date without using the dedicated DAX functions to do that. A date is a floating point number where the integer part is the distance in days since December 30, 1899, and the decimal part is the fraction of a day. In the following examples I will use NOW() to obtain a date and time, but you might have any other expression operating on your data.

This measure in Power BI returns a date/time, and can be formatted using any “Date Time” format:

Measure := NOW ()

You can remove the time by truncating the number, obtaining only the day with the time corresponding to 12:00am:

Measure := TRUNC ( NOW () )

At this point, if you try to change the format of the measure, the “Date Time” format is disabled:


In DAX there is no type conversion operator to date/time. However, you can sum the numeric expression that you have to a recognized date/time value. If you add the equivalent of 0 (corresponding to December 30, 1899), your final data type is a date/time. The following expression returns a date/time data type corresponding to the current day.

Measure := TRUNC ( NOW() ) + DATE ( 1899, 12, 30 )

The format of the measure now displays only Date Time formats.


Please note that for the specific example used in this post, you can use the DAX function TODAY(), which returns the current date without the time. The function NOW has been used only to represent any date/time expression that you might obtain from other DAX expressions over your data.

Thanks to Jeffrey Wang for providing me the hint used in the technique I described in this blog post.

Published Tuesday, December 6, 2016 9:14 AM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Enrico said:


Many thanks

December 6, 2016 6:55 AM

jguidoni said:


it's good but there is not the format hour.

format 13:30:55 (HH:mm:ss)

example if I want the number of hours = 256:30:45

(256 hours and 30 minutes and 45 seconds)

how can i do that ?

December 6, 2016 6:17 PM

Mike said:

You can also use ROUNDDOWN to zero decimal places rather than TRUNC if you just need to remove the time while retaining the date on a date/time data type. This keeps the result in date/time data type.

December 7, 2016 10:08 AM

whitneyland said:


For hours from a time span, use DATEDIFF like this:

   numberOfHours = DATEDIFF(<start_date>, <end_date>, HOUR)

For hour of the day, use HOUR to get a number from 0 (12:00 A.M.) to 23 (11:00 P.M.)

   hourOfTheDay = HOUR(<date>)

March 29, 2017 9:56 PM

Marco Russo (SQLBI) said:

@jguidoni - it's not supported directly, but whitneyland provided a good workaround

@Mike: yes thanks!

March 30, 2017 10:37 PM

Leave a Comment


About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement