Issue with DATEADD function in DAX
I recently started learning DAX. I am unable to understand DATEADD function properly. The below expression is giving error.
DATEADD(FIRSTNONBLANK(DATATABLE("TodaysDate",DATETIME,{{"9/24/2016"}}),TRUE()),4,MONTH)
A table expression containing more than one column was specified in the call to function 'DATEADD'. This is not supported.
But it's working with EDATE
EDATE(FIRSTNONBLANK(DATATABLE("TodaysDate",DATETIME,{{"9/24/2016"}}),TRUE()),4)
and FIRSTDATE is not working like FIRSTNONBLANK. Is there a problem with DATATABLE usage?
FIRSTDATE(DATATABLE("Today1",DATETIME,{{"9/24/2016"}}))
A table expression containing more than one column was specified in the call to function 'FIRSTDATE'. This is not supported.
Answer
The DATEADD function takes a column that contains dates as first parameter, you are passing a table so that is causing the error. Even if you are passing a one-column table it is different to a column.
Note FIRSTNONBLANK and FIRSTDATE functions return a table containing a single column and single row with the computed first value or date respectively, so you are passing a table.
Even if you were passing one column with one row to the DATEADD function it won't throw errors but return nothing since DATEADD returns the computed date if it is present in the column, so if you have one value there is no more dates to return. If it is not clear for you, consider this example to understand how DATEADD works:
You have this table called MyDates
If you use DATEADD to create a calculated column called 4MonthsAfter:
4MonthsAfter = DATEADD(MyDates[Date],4,MONTH)
It returns this:
As you can see the column 4MonthsAfter for the date 24/08/2016 value is blank since the column doesn't contain 24/12/2016 value, the same happens for 24/01/2017 because there is no 24/05/2017 value. But 4 months after 24/09/2016 is computed since 24/01/2017 is present in the column.
The way DATEADD works is pretty different from EDATE way. EDATE takes a date (not a table column of dates) in datetime or text format and compute the given number of months before or after the date.
In essence, DATEADD expects a table column of dates as first parameter as most of Time Intelligence functions, which are designed to work with Date/Calendar tables (Date dimensions).
If you come from a programming background, you could get confused by the way DATEADD works in DAX. Commonly DATEADD only needs a date, the number of intervals and a specific interval (MONTH, DAY, YEAR, QUARTER) to compute a date, in DAX it requires an explicit column of dates as first parameter.
Let me know if this helps.
REFERENCES: