Relationship with date table showing no data

[kwil]

Hi, I feel like I've searched everywhere and just can't find the solution to my issue here. 

I am trying to make a relationship between a table I made with DirectQuery from a local SQL server and a Date Table that I made with CALENDARAUTO, and I need to make a visual that shows TotalExtended by month. However, when I try to make a visual it returns no data, like so:

I set the relationship between DateAdded and Date, and it seems to be an incomplete relationship for some reason but I'm not sure if this matters.

Another thing that I think may be the cause is that in the "edit relationship" window it only shows three rows, and the rows it shows are not actually related.

Our data in the table technically goes back to 2015, but I have in the WHERE clause of the DirectQuery to only return data from the past 12 months, so I'm not sure if this could also be causing problems.

If anyone has any ideas I would be very grateful, thank you!

[amitchandak · Accepted solution]

@kwil , A date joining with date of the date table should not have a timestamp. So you should bring a date from DB not having timestamp for join

Also consider date table using calendar function if needed

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[kwil]

I tried removing the timestamp by changing the format to just "Date" instead of "Date/Time" but it didn't seem to do anything.

[kwil]

Nevermind, I had to go into the SQL statement and cast my dates to remove the timestamp from there. Thank you!