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