THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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:

image

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.

image

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

Comments

 

Enrico said:

Wonderful

Many thanks

December 6, 2016 6:55 AM
 

jguidoni said:

hi,

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:

@jguidoni

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
 

whitneyland said:

Is your memory getting bad if you forget you came to a post before?  I think saying thank you twice Marco for the post would not be inappropriate, so thank you twice.

However it leads to (yet another) criticism of DAX.  It's such a powerful language, yet providing the power does not require these odd edge cases, design quirks, and so forth that other modern languages do not suffer from.

I believe Microsoft should provide a transpiler hook into the DAX toolchain. This is the same concept leveraged by the Microsoft TypeScript team with JavaScript.  JavaScript is a limited language, but TypeScript can improve productivity with no cost to backward compatibility.

Adding a DAX transpiler hook point would make this power available not only to Microsoft, but also to the community.  Developers so inclined could extend and polish the language to fix cases like this themselves.  If the flavor developed was not productive, no one else would use it, and there is no harm.  If an enhanced DAX flavor is generally accepted to improve productivity, Microsoft could choose to bake in the best parts to future versions.

I would be interested to hear feedback on why this would not only be beneficial, but practical.  The most powerful part of the concept is the relatively low cost of adding the initial hook, and no risk of splintering compatibility because all code is eventually compiled down to the pristine and official DAX specification.

November 15, 2017 11:39 AM
 

whitneyland said:

Sorry for double post, I've realized it may not be clear what is meant by "hooks into the DAX toolchain".

One example of this would be adding a an option to Power BI desktop that allowed specifying a path to DAX transpiler.  This could allow any new features to be used within Power BI desktop in a seamless way, as if they were natively part of Power BI.

When publishing to the cloud, this all becomes irrelevant because only compiled pristine DAX would be required.

The value of additional hooks, for example to Excel or other products or tools, would of course be possible depending on the demand and practicalities of the environment.

November 15, 2017 11:46 AM
 

Marco Russo (SQLBI) said:

I get your point.

The idea is interesting, but is there a way to obtain that without losing compatibility with the original language?

For example, TypeScript and JavaScript are compatible, you can still write JavaScript while you write TypeScript. Do you have an example of the syntax you would like to see for DAX in a similar example?

November 23, 2017 5:46 AM

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Privacy Statement