powerpivot and date dimension boundaries

[droopy]

Hi,

I'm using powerpivot to process survey data.

I created a date dimension table, that is referenced by other tables containing dates.


It looks like this:

[pre]

DateKey    MonthNumber   MonthName   Fiscal Quarter   Calendar Year   Fiscal Year
4/30/2013     4            Apr            Q2             2013              2013
5/1/2013      5            May            Q3             2013              2013
5/2/2013      5            May            Q3             2013              2013

[/pre]
Problem I have is the following:

In the example above 5/1/2013 will be indeed 5/1/2013 12:00 AM


In my data table, dates are 4/30/2013 6:38 PM, 5/9/2013 5:50 PM or 5/31/2013 7:20 PM


When I do a pivot based on month name, I have an issue with month boundaries. If a date in my data table is 4/30/2013 6:38 PM, then it will be counted in May.

If date is 5/31/2013 7:20 PM it is counted in June.


Whereas if I add a calculated column to my data table, month(5/31/2013 7:20 PM) will return May as expected.


I thus suspect the mapping is not correct because days in date dimension are at 12:00 AM.


Any suggestion on how to solve this issue? apart from adding a calculated column for the month in my data tables, but this reduces the advantage of powerpivot and date dimension...


Thank you in advance for your lights!