Can't connect order table to calendar table

[seanpratt]

A bizarre situation today. I cannot seem to connect my Orders table to my calendar table. I want to start building out Revenue totals (monthly, MTD, YTD, YoY%) so I built a Calendar table, connected it to my preferred date column within my Orders table (Created Date) and start building measures and visualizations and nothing. 

I've attached my pbix here and you can see that I have "Year" as my rows and Revenue (my measure) as my value and it's just a blank matrix. 

Can someone please take a look and tell me where I went wrong. I'm sure this is an easy fix but I'm spinning my wheels.

LINK

[v-diye-msft]

Hi @seanpratt 

If you've fixed the issue on your own please kindly share your solution. if the above posts help, please kindly mark it as a solution to help others find it more quickly. thanks!

[jdbuchanan71 · Accepted solution]

@seanpratt 

It's because you have the time included in your [Created Date] so the relationship is not finding any matching rows in your Calendar table.

Remove the Time from the field and it should work for you.

You could remove it a couple ways.

CREATED DATE = 
COALESCE (
    DATE ( 
        YEAR ( ORDERS[ORIGINAL ORDER CREATED DATE] ), 
        MONTH ( ORDERS[ORIGINAL ORDER CREATED DATE] ), 
        DAY ( ORDERS[ORIGINAL ORDER CREATED DATE] ) 
    ),
    DATE ( 
        YEAR ( ORDERS[CREATED TIME] ), 
        MONTH ( ORDERS[CREATED TIME] ), 
        DAY ( ORDERS[CREATED TIME] ) 
    )
)

or

CREATED DATE = 
COALESCE (
    INT ( [ORIGINAL ORDER CREATED DATE] ),
    INT ( ORDERS[CREATED TIME] )
)

If you use the INT approach you will have the change the data type back to date.