Forum Discussion
DateAdd error
enclosed is a screenshot...
not sure what is happening, error doesnt make sense to me. i want to the date six year ago, let me know what you see or what the requirements are to use DATEADD
Hi ilcaa72
The DATEADD function returns a table of dates, rather than a single date as a scalar value.
The table will contain dates from the visual's filter context shifted by the specified interval (but limited to dates that are present in the date column of the underlying table).
Also DATEADD won't automatically convert a single value to a scalar, which is why you are getting that error message.
A more appropriate measure might be something like:
= EDATE ( MAX ( 'Date tbl'[Date] ), -6 * 12 )
(will return dates not present in 'Date tbl')
or
= LASTDATE ( DATEADD ( 'Date tbl'[Date], -6, YEAR ) )
(will be limited to dates present in 'Date tbl')
6 Replies
Hi ilcaa72
The DATEADD function returns a table of dates, rather than a single date as a scalar value.
The table will contain dates from the visual's filter context shifted by the specified interval (but limited to dates that are present in the date column of the underlying table).
Also DATEADD won't automatically convert a single value to a scalar, which is why you are getting that error message.
A more appropriate measure might be something like:
= EDATE ( MAX ( 'Date tbl'[Date] ), -6 * 12 )
(will return dates not present in 'Date tbl')
or
= LASTDATE ( DATEADD ( 'Date tbl'[Date], -6, YEAR ) )
(will be limited to dates present in 'Date tbl')
Hello OwenAuger ,
I am experiencing the same problem as Ilca, I tried your second approach and I am using a Date Table, which contains all days I am going to need.
I am trying to add 90 days to a date that is on each row:
Date column comes from Date Table and I am trying to create a measure:Due Date = DATEADD('Calendar'[Date].[Date],90,Day),'Calendar'[Date])And getting error:DATEADD documentation says that first statement "dates" has to be a column that contains dates, which is what I think I am using.
I am trying to avoid creating a new column, since I want to make this calculation with a measure.
I would appreciate your help!
Thanks.
Hello Anonymous
A bit hard to answer without understanding how you want to use Due Date once you have calculated it.
Could you show an example of how you want to use or visualize the Due Date values?
If Due Date is 90 days after the Date shown in your screenshot above (which I assume is from a fact table), then I would actually suggest adding a column, which could be done either:
- In Power Query by adding a column using the Date.AddDays(...) function
- In a DAX calculated column by adding 90
The DATEADD function is typically used to shift a date filter (i.e. a column of dates) by a specified number of date intervals, rather than operate on a single date.
Regards,
Owen
