DAX calculated table column error – PBI Desktop ADDCOLUMNS() to CALENDARAUTO()

I trying to create a DAX calculated table in PBI Desktop using ADDCOLUMNS() to CALENDARAUTO() table. All of the calculated columns work great, except a random few of the calculations have a problem with using the [Date] columns and give an error:

Column ‘Date’ cannot be found or may not be used in this expression.

Parameter is not the correction type

This calculation (plus a dozen others) are successfully computed:

  • "SortDesc_Date"
"SortDesc_Date", - DATEDIFF(date(9999, 12, 31), [Date], DAY)

These 3 calculations fail:

  • "Mo_startDate"
"Mo_startDate", STARTOFMONTH([Date])
  • "Yr_startDate"
"Yr_startDate", STARTOFYEAR([Date])
  • "SortDesc_YrMo"
"SortDesc_YrMo", - DATEDIFF(date(9999, 12, 31), STARTOFMONTH([Date]), MONTH)

Does anyone have any clues why these columns have computation errors?

enter image description here

enter image description here

SCRIPT (Updated)

Working after correcting columns to substitute the usage of time intelligence functions.

Calendar (v3) = 
-- var_MoName = FORMAT([Date], "MMM")
-- var_MoStartDate = EOMONTH([Date],-1) + 1
ADDCOLUMNS (
    CALENDARAUTO (),
    "DateKey", FORMAT([Date], "YYYY") & FORMAT([Date], "MM") & FORMAT([Date], "DD")
    , "Year", YEAR([Date])
    , "Mo", MONTH([Date]) 
    , "day", DAY([Date])
    , "Mo Name", FORMAT([Date], "MMM")
    , "Mo-Yr", FORMAT([Date], "MMM") &"-"& FORMAT([Date],"YY")
    , "Mo_startDate", EOMONTH([Date],-1) + 1
    , "Yr_startDate", DATE(YEAR([Date]),1,1)
    , "Qtr", QUARTER([Date])
    , "Qtr Name", "Qtr " & FORMAT([Date], "Q")
    , "Yr Name", "YR " & FORMAT([Date], "YYYY")
    , "Mo_endDate", EOMONTH([Date],0) 
    , "SortDesc_YrMo", - DATEDIFF(date(9999, 12, 31), EOMONTH([Date],-1)+1, MONTH)
    , "SortDesc_Date", - DATEDIFF(date(9999, 12, 31), [Date], DAY)
    , "Date (DESC)", [Date]
    , "Mo-Yr (DESC)",  FORMAT([Date], "MMM") &"-"& FORMAT([Date],"YY")
)

>Solution :

STARTOFMONTH and STARTOFYEAR are Time Intelligence functions that expect a date column as the first argument rather than a single date value. They are designed to work in measures to manipulate filter context rather than on single date values.

You can use the following definitions instead:

"Mo_startDate", EOMONTH ( [Date], -1 ) + 1

"Yr_startDate", DATE ( YEAR ( [Date] ), 1, 1 )

Leave a Reply