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

Strange date relationships with #PowerPivot

A reader of my PowerPivot book highlighted a strange behavior of the relationship between a datetime column and a Calendar table. Long story short: it seems that PowerPivot automatically round the date to the “neareast day”, but instead of simply removing the time (truncating the decimal part of the decimal number internally used to represent a datetime value) a rounding function seems used, moving the date to the next day if the time part contain a PM time.

As you can imagine, this becomes particularly relevant for transactions made in the last day of a month or of a year, because using the relationship the transaction value is mapped to a different month/year.

In order to illustrate the problem, consider this Transactions table:

clip_image001[5]

As you can see, CloseDate is used to define the relationship with the following Calendar date:

clip_image001

I would have expected that this relationship wouldn’t work, because there is no corresponding row in Calendar table for any time other than 00:00:00. Unexpectedly, it seems to work. But take a look at what is the RelatedDate calculated column that you obtain by using the RELATED( Dates[Date] ):

clip_image001

As you can see, every time the date contains a PM time (using the international forma in this screenshot) the RelatedDate column points to the following day.

Is this a bug or not? I posted this on Connect to look for an answer. In the meantime, a possible workaround is creating a Date calculated column in the Transactions table to define the relationship, using this DAX formula that removes the time:

= DATE( YEAR( Transactions[DateTime] ), MONTH( Transactions[DateTime] ), DAY( Transactions[DateTime] ) )

I remind you I’ll be in Oslo next week for the PowerPivot Workshop and I will present to this community event on Wednesday 22nd at 6PM – see you there!

Published Tuesday, June 14, 2011 3:28 PM 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

 

Reuvain said:

It's interesting that the relationship remained intact (albeit rounded) despite the additional time component. For one of Rob Collie's clients the extra time component seemed to have totally broken the relationship.

http://powerpivotpro.com/2011/02/21/date-and-datetime-sneaky-data-types/

June 14, 2011 12:12 PM
 

Marco Russo (SQLBI) said:

I suspect there is a pattern that is activated whenever all rows contain 00:00:00 as time. We'll see what MS will say on Connect...

June 14, 2011 12:32 PM

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

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement