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

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

DATE function does not support all the dates in DAX by design #powerpivot #tabular #dax

The DATE function in DAX has this simple syntax:

DATE( <year>, <month>, <day> )

If you are like me, you never read the BOL notes that says in a clear way that it supports dates beginning with March 1, 1900. In fact, I was wrongly assuming that it would have supported any date that can be represented in a Date data type in Data Models, so all the dates beginning with January 1, 1900. The funny thing is that in some of the BOL documentation you will find that Date data type supports dates after March 1, 1900 (which seems not including that date, but this is a detail…). But we should not digress. The real issue is that if you try to call the DATE function passing values between January 1 and February 28, 1900, you will see a different day as a result.

evaluate row ( "x", DATE( 1900, 1, 1 ) )

-- return WRONG result
-- [x] 12/31/1899 12:00:00 AM


evaluate row ( "x", DATE( 1901, 2, 29 ) )

-- return WRONG result
-- [x] 2/28/1900 12:00:00 AM


evaluate row ( "x", DATE( 1900, 3, 1 ) )

-- return CORRECT result
-- [x] 3/1/1900 12:00:00 AM

As usual, this is not a bug. It is “by design”. The DATE function works in this way in Excel. And also in Excel it was “by design”. In this case the design is having the same bug of Lotus 1-2-3 that handled 1900 a leap year, even though it isn’t. The first release of Lotus 1-2-3 is dated 1983. I hope many of my readers are younger than that. I tried to open a bug in Connect. Please vote it. I would like if Microsoft changed this type of items from “by design” (as we can expect) to “by genetic disease”. Or by “historical respect”, in order to be more politically correct. Smile

Published Monday, November 5, 2012 2:52 PM by Marco Russo (SQLBI)
Filed under: , , ,



Ian Yates said:

November 6, 2012 5:51 AM

Laurent Couartou said:

Microsoft's answer is misleading.

DATE(1900,1,1) evaluates to 1 in Excel, but evaluates to 0 in PowerPivot.

In PowerPivot, 1900 is (correctly) not considered a leap year.

So, Microsoft actually fixed the leap-year issue by implementing this odd DATE function glitch.

Thanks for pointing that out.

November 15, 2012 10:12 AM

Antonio Marines said:

Wow! Until now I know, thank you very much for this blog!

November 15, 2016 12:48 PM
New Comments to this post are disabled

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