Calendar Table with EOM. Need Help!

[AmeenVanakar]

Hi,

Can you help in solving this error? Not sure how to solve this. Also need to verify the EOM column.

DateTable =

VAR Startate = MIN(Customer_Data[BILL DATE])

VAR EndDate = MAX(Customer_Data[BILL DATE])

VAR DateTable =

ADDCOLUMNS(

    CALENDAR(Startate,EndDate),

    "Year", YEAR([Date]),

    "Quarter Name", FORMAT([Date], "\QQ"),

    "Quarter Number", QUARTER([Date]),

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

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

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

    "Month Year Sort", VALUE(FORMAT([Date], "YYYYMM")),

    "End Of Month", EOMONTH([Date],0),

)

RETURN

DateTable

[AlexisOlson · Accepted solution]

EOM looks fine.

You can't have a variable with the same name as a table or else it's ambiguous which one you're referring to. As Greg points out, it's common practice to use prefix variable names with underscores to prevent conflicts with table and reserved words. Like this:

DateTable =
VAR __Startate = MIN ( Customer_Data[BILL DATE] )
VAR __EndDate = MAX ( Customer_Data[BILL DATE] )
VAR __DateTable =
    ADDCOLUMNS (
        CALENDAR ( __Startate, __EndDate ),
        "Year", YEAR ( [Date] ),
        "Quarter Name", FORMAT ( [Date], "\QQ" ),
        "Quarter Number", QUARTER ( [Date] ),
        "Month Name", FORMAT ( [Date], "MMM" ),
        "Month Number", MONTH ( [Date] ),
        "Month Year", FORMAT ( [Date], "MM YYYY" ),
        "Month Year Sort", VALUE ( FORMAT ( [Date], "YYYYMM" ) ),
        "End Of Month", EOMONTH ( [Date], 0 )
    )
RETURN
    __DateTable

(Extra comma before the final ) removed.)

[AmeenVanakar]

Thanks @AlexisOlson & @Greg_Deckler this worked!

[Greg_Deckler]

@AmeenVanakar Put one or two underscore characters prior to your variable names and you will avoid these kinds of things.