Relationship issue between UTC Date/Time Field and Non-UTC Date/Time Field

[Promethes_2]

Does anyone know if this is the expected behvavior of PowerBI? And if so why?

I have a calendar table that is created based on an existing tables date range 'CRM Cases'[Created On].  The calendar table statement is defined as:

Calendar = 
ADDCOLUMNS(
CALENDAR  (MINA('CRM Cases'[Created On]), MAXA('CRM Cases'[Created On])), 
		"Date as Integer", FORMAT([Date], "MMDDYYYY"),
		"Year", YEAR([Date]),
		"Month Number", FORMAT([Date], "MM"),
		"Month Year Number", FORMAT([Date], "M/YYYY"),
		"Month Year Short", FORMAT([Date], "mmm-YYYY" ),
		"Month Name Short", FORMAT([Date], "mmm" ),
		"Month Name Long", FORMAT([Date], "mmmm" ),
		"Day of Week Number", WEEKDAY([Date]),
		"Day of Week Name", FORMAT([Date], "dddd" ),
		"Day of Week Name Short", FORMAT([Date], "ddd" ),
		"Quarter", "Q" & FORMAT([Date], "Q" ),
		"Year Quarter",  "Q" & FORMAT ([Date], "Q" ) & "-" & FORMAT([Date], "YYYY" )  
)

The issue that I'm having is this.  The table the calendar table is based on has a UTC date/time value for [Created On], values look like this:

6/29/2015 6:09:05 PM +00:00

In the calendar table that gets created based on the statement above the [Date] field values look like this:

6/29/2015 12:00:00 AM

And the issue that I have is that the relationship between these fields are not operating as expected.  Here is what happens:

Before the relationship is defined everything looks ok, pleae take note that there are no blank values:

As soon as I create a relationship between the UTC to non-UTC date/time fields I get a blank value field and the relationship doesn't work as expected:

 

After spending hours on this I finally figured out that if I TRANSFORM the field 'CRM Cases'[Created On] to Date Only it then works as expected.

Is this expected behavior? Or is this a bug?

[mike_honey · Accepted solution]

It's not explicitly stated in the doco for CALENDAR, but that's always been my understanding - it returns a table of dates (1 row per day), not date-time values.

I would add a column to your table, e.g.

Created On Date = DATE ( YEAR ( [Created On] ) , MONTH ( [Created On] ) , DAY ( [Created On] )

Then I would repoint the relationship to the DateDim table to use that new column.

[Promethes_2]

It's just an interesting artifact because I can transform the date from UTC to 6/29/2015 6:09:05 PM (stripping off the +000) and it then works as expected in the relationship. 

Thanks for the reply.