Column reference to 'Date' in table 'Date' cannot be used with a variation 'Year' because it does

[lisaeugene]

Hi All, 

I just created a Date table with starting with a Date column that has unique values for each row. For some reason, I'm getting a weird error message for my Year column. "Column reference to 'Date' in table 'Date' cannot be used with a variation 'Year' because it does not have any."

The Date column data type is set to Date and the format is m/d/yyyy. 

The Year formula I used was Year = 'Date'[Date].[Year]. I am using the exact same formula in another date table in another file and it works perfectly. 

Anyone have any ideas what's causing this error message for this calculated column formula? 

[jmlipari]

Simply uncheck "Mark as a Date Table" and formula goes well.

[Hasan]

Can you make sure that Data type is correct? You can check it out under the modeling

[lisaeugene]

The data type for my Date column is set to Date - I checked it again within the Modelling tab. 

[lisaeugene · Accepted solution]

So my solution was to delete and use another date table entirely. I ended up using many of the functions suggested by Giles in another post How do i create a date table ? in his reply to that user posting the question. Here's what I did: 

Click the insert new table button on the ribbon and copy in below:

DateKey = CALENDAR(DATE(2000,01,01),DATE(2025,12,31))

Then I add in a new column from the ribbon for each of the following:

Year = YEAR(DateKey[Date])
Month number = MONTH(DateKey[Date])
Month = FORMAT(DateKey[Date],"MMM")
Day = FORMAT(DateKey[Date],"ddd")
Week = WEEKNUM(DateKey[Date],1)
Quarter = "Q" & ROUNDUP(MONTH(DateKey[Date])/3,0)
MonthYr = FORMAT(DateKey[Date],"MMM")&" " &DateKey[Year]
Day number = DAY(DateKey[Date])
Financial week = IF(DateKey[Month number]>6,DateKey[Week]-26,DateKey[Week]+26)
Total Month Days = DAY(DATE(DateKey[Year],DateKey[Month number]+1,1)-1)
MonthYr number = DateKey[Year]&DateKey[Month number]

Weekday Num = WEEKDAY(DateKey[Date],1)

Sorting Text Columns to display in chronological order: I sorted some of the text columns so that they would display in chronological order in my visualizations. In "Modeling" tab, select "Sort by Column" and sort

• Month by Month Number
• Day by Weekday Number
• MonthYr by MonthYr number

After that, I marked the table as a date table.