A year later

[Anonymous]

I  want to calculate when a year has passed. Tried this:

AYearLater = Date(Year([Start date]) + 1,
                              Month([Start date]),
                              Day([Start date]) )

But DAX says that I have an argument of the wrong data type or too large or too small. Presumably this is (Year([Start date]) + 1) as replacing that with a number or with Year([Start date]) is fine.

So I tried

AYearLater =

Var YearOn = Year([Start date]) + 1

Return

Date(YearOn, Month([Start date]), Day([Start date]))

And that's no better.

AYearOn = Year(Start date) + 1 returns a perfectly ordinary integer.

I tried (Int(Year([Start date]) + 1) : no good.

I realise I could use Edate([Start date], 12) but I don't see why DAX objects to my original attempt.

[ppm1]

I tried your syntax (below) in a calculated column and it worked fine. Just confirming you are talking about a calculated column, and that [Start date] is not a measure.

DateTest = DATE(YEAR('Date'[Date])+1, Month('Date'[Date]), DAY('Date'[Date]))

Pat

[Anonymous]

Hi @Anonymous,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[Anonymous]

I'm afraid not. I thought I'd answered the question ppm1 asked me. It seems the code works for ppm1 but not for me. I have no idea why this should be, or what else to tell you.

PBI could be a bit more helpful about its DAX complaints. "an argument of the wrong data type or too large or too small" doesn't tell me where to look. Which argument? The interpreter must know. However changing the Year argument to remove any calculations removed the error so that tells me where to look. That doesn't explain why [Field] is an acceptable value but [Field]+1 is not.

[Anonymous · Accepted solution]

Hi @Anonymous,

This may cause by date table include some invalid date value that can't be process with date function. You can try to add a ISERROR function to check these values before calculations:

DateTest =
IF (
    ISERROR ( DATEVALUE ( 'Date'[Date] & "" ) ),
    BLANK (),
    DATE ( YEAR ( 'Date'[Date] ) + 1, MONTH ( 'Date'[Date] ), DAY ( 'Date'[Date] ) )
)

Regards,

Xiaoxin Sheng

[Anonymous]

That is helpful, thank you. There was a blank in the Start date column.

[Anonymous]

Yes, a calculated column