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

[some_analyst]

I have been workinbg on a dashboard that was already created and working.

I added some connections and Did not do any changes to my dim_date and I am getting the error: 

"Column reference to 'Date' in table 'Date_DIM' cannot be used with a variation 'Year' because it does not have any."

Some of the affected measure are this: 

AverageGrowthRate = 
AVERAGEX(
    VALUES(Date_DIM[Date].[Year]),
    [YoYGrowthRate]

I went through the help files here, market the table as a date table and now I need to remove the variation .Year from lots of measures and I think it messes up my frmula, an example is this:  

YoYGrowthRate = 
VAR CurrentYear = MAX('Date_DIM'[Date].[Year])
VAR CurrentSubmissions = [YearlySubmissions]
VAR PreviousSubmissions = 
    CALCULATE(
        [YearlySubmissions],
        FILTER(
            ALL('Date_DIM'[Date].[Year]),
            'Date_DIM'[Date].[Year] = CurrentYear - 1
        )
    )
RETURN
    DIVIDE(
        (CurrentSubmissions - PreviousSubmissions),
        PreviousSubmissions,
        BLANK()
    )

[pankajnamekar25 · Accepted solution]

Hello @some_analyst ,

The error occurs because the 'Date' column in your Date_DIM table doesn't have a Year variation after marking it as a Date Table. Ensure that 'Date_DIM'[Date] is of Date data type. If the Year column was removed after marking the table, recreate it using Year = YEAR('Date_DIM'[Date]). Modify your measures to use YEAR('Date_DIM'[Date]) instead of 'Date_DIM'[Date].[Year]. Also, check if the Date Table is correctly marked in Model View by right-clicking Date_DIM, selecting Mark as Date Table, and choosing the 'Date' column. Finally, restart Power BI and refresh the dataset to apply schema changes.

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos, it would be greatly appreciated.

[some_analyst]

Thanks @pankajnamekar25 , the issue seemed to be a relationship between a date from the table (date time format) and dim date . Strange, because that date time format I missed and it worked until now with refreshes.

I changed tha SimDate.Date.Year to Dim.Year and it seems to show fine