Forum Discussion
Column reference to 'Date' in table 'Date' cannot be used with a variation 'Year' because it does
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?
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.
4 Replies
HasanResolver I
Can you make sure that Data type is correct? You can check it out under the modeling
The data type for my Date column is set to Date - I checked it again within the Modelling tab.
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.
Simply uncheck "Mark as a Date Table" and formula goes well.
