Forum Discussion
Column reference to 'Date' in table 'Date' cannot be used with a variation 'Year' because it does
- 8 years ago
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.