DateAdd error

[ilcaa72]

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

[OwenAuger · Accepted solution]

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')

[edavilac]

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 @edavilac 

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

[edavilac]

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!

[OwenAuger]

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

[edavilac]

It worked perfectly, thanks again, Owen!! 👏👏