Forum Discussion
Helper IVDateAdd error
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')
Super UserHi 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.
- OwenAuger
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
Thanks so much for your quick response!
What I want to show is a table that displays the due date for each account, this is, 90 days after the start date. Is the same effect if in Excel I just enter Start Date + 90:
I am able to achieve my result using Power Query/adding a new column, and I think that is the solution I will stick to, as it's very easy to do.
I just wanted to understand if it's possible to achive the result I want, but using a measure, so I didn't have to add width to my tables.
Thank you!