Date Error: Cannot identify the table that contains [Date] column.

[Miguel_Rojo]

Hi community!

Power bi is having problems to create a "Calendar Table" using my code (which in my opinion is correct).

After I provided the code (please check below), I get the next error: "Cannot identify the table that contains [Date] column."

I need the table to create a relation between my other two tables (both tables have multiples records in their respective "Date" columns, and the data type of those columns is "Date".

Do you know what's wrong? As you can see, indeed the table and column to which I'm making reference exists and are correct (I provide an screen shot of my two single tables in the model).

CODE:

Calendar Table =

    VAR __Start_Date = DATE(YEAR(MIN('PB_traffic_trend'[Date])), 1, 1)

    VAR __End_Date = DATE(YEAR(MAX('PB_traffic_trend'[Date])), 12, 31)

    VAR __Range = CALENDAR(__Start_Date, __End_Date)

    VAR __Calendar_Table =

        ADDCOLUMNS(

            __Range,

            "Date Join", FORMAT( [Date], "mm/dd/yyyy"),

            "Year", YEAR([Date]),

            "Quarter", "Q" & FORMAT([Date], "Q, YYYY"),

            "Quarter Sort", FORMAT([Date], "YYYY-MM-Q"),  -- Modified for sorting

            "Month Number", MONTH([Date]),

            "Month Name", FORMAT([Date], "MMMM"),

            "Month Year", FORMAT([Date], "MMM, YYYY"),

            "Month Sort", FORMAT([Date], "YYYY-MM"),

            "Year to Date", FORMAT(DATE(YEAR([Date]), 1, 1), "MMM YYYY") & FORMAT([Date], " - MMM YYYY"),

            "Max Year", YEAR(MAX([Date])),

            "Max Month", MONTH(MAX([Date]))

        )

    RETURN

        __Calendar_Table

[OwenAuger · Accepted solution]

Hi @Miguel_Rojo 

The problem is in the expressions for "Max Year" and "Max Month".

The MAX function must take a column reference from a physical table as its argument.

Did you want these to be the overall maximum year/month appearing in the Calendar Table?

If so you could write this (since the maximum date is already contained in the variable __End_Date )

"Max Year", YEAR( __End_Date ),
"Max Month", MONTH( __End_Date )

If that's not what you wanted, please post back to clarify what you wanted these values to be.

Regards

Owen

[Miguel_Rojo]

Thanks Owen!!, you were right!, I was selecting an unexisting table using the MAX() function; I changed the code by:

    "Max Year", YEAR(MAXX(__Range, [Date])),

            "Max Month", MONTH(MAXX(__Range, [Date]))

and it worked!