Forum Discussion

lisaeugene's avatar
lisaeugene
Frequent Visitor
8 years ago
Solved

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...
  • lisaeugene's avatar
    lisaeugene
    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.