Forum Discussion

AmeenVanakar's avatar
AmeenVanakar
Advocate II
1 year ago
Solved

Calendar Table with EOM. Need Help!

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
 
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    1 year ago

    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.)

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    1 year ago

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

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    1 year ago

    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.)