Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Miguel_Rojo
Frequent Visitor

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

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
 
calendar_table.png
 
1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

2 REPLIES 2
OwenAuger
Super User
Super User

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


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors