topic Re: A year later in DAX Commands and Tips

A year later

Anonymous — Thu, 15 Dec 2022 13:02:27 GMT

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.

Re: A year later

ppm1 — Thu, 15 Dec 2022 13:21:36 GMT

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

Re: A year later

Anonymous — Thu, 15 Dec 2022 13:24:50 GMT

Yes, a calculated column

Re: A year later

Anonymous — Mon, 19 Dec 2022 07:52:06 GMT

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

Re: A year later

Anonymous — Mon, 19 Dec 2022 15:05:53 GMT

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.

Re: A year later

Anonymous — Thu, 22 Dec 2022 05:17:10 GMT

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

Re: A year later

Anonymous — Tue, 03 Jan 2023 11:22:35 GMT

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