Date Table Relationship Not Working

[sirlanceohlott]

Good afternoon, 

I've been at an impasse for the past hour regarding a Date Table I created within a new report. 

I'll share what I've attempted prior to sharing DAX & images. 

• Date Table is marked as the Date Table.
• I've made sure the Date Table and any of the tables it would be connecting to are all in the same format Date/Time through the relationship pane as well as Power Query Editor.
• I've also verified in SSMS the field it is being joined to is Date/Time. 

I'm not sure if this would present an issue, but the data tables are pulled in through Direct Query. 

Here is the DAX I utilized to make the Date Table:

Date = 
  GENERATE (
    CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
    VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday    
    VAR currentDay = [Date]
    VAR days = DAY( currentDay )
    VAR months = MONTH ( currentDay )
    VAR years = YEAR ( currentDay )
    VAR nowYear = YEAR( TODAY() )
    VAR nowMonth = MONTH( TODAY() )
    VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
    VAR todayNum = WEEKDAY( TODAY() )
    VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
  RETURN ROW (
    "day", days,
    "month", months,
    "year", years,
    "day index", dayIndex,
    "week index", weekIndex,
    "month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
    "year index", INT( years - nowYear )
  )
)

Here is a view from the relationship pane:

Relationship Pane View

Here is the Edit Relationship View: 

I appreciate any help in resolving this issue!

[VasTg · Accepted solution]

@sirlanceohlott 

Is there any reason why you should keep dateadded and Date columns in Date/Time datatype instead of Date(mm/dd/yyyy format)?

Try it and let us know.

If this helps, mark it as a solution.

Kudos are good too.

[sirlanceohlott]

@VasTg, it worked like a charm, I'll leave the Date/Time variation out of the mix for the time being.

I appreciate your help!

[sirlanceohlott]

@VasTg I'll shift to from Date/Time to Date and see if this resolves the issue.

The only reason I was keeping it in Date/Time was if I wanted to do some calculations on submission times.

I'll let you know how it goes!