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:

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

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] ):

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!