Forum Discussion
DateAdd 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')
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!
No problem, and thanks for the explanation!
Yes, you can achieve a similar result using a measure:
Due Date Measure = VAR StartDate = SELECTEDVALUE ( YourTable[Start Date] ) RETURN -- Only return a result if a single Start Date value -- is visible in current filter context IF ( NOT ISBLANK ( StartDate ), StartDate + 90 )I've use SELECTEDVALUE(...) here to retrieve the single value of Start Date visible in the filter context. The measure will return blank unless a single Start Date value is visible.
All the best
Owen
It worked perfectly, thanks again, Owen!! 👏👏