Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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 )
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading