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:

This article describes how DAX automatically converts data types in arithmetic operations. These small details can cause and explain differences in results when using the same operations in other languages. Read more