0 Value for Fields in DATE function

[jahida]

Hi all,

I had a question about the DATE function in DAX, specifically the expected outcome when the "days" field is 0. Here's the documentation for that:

"If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified."

This seems pretty clear to me: for example, I expected DATE(2016, 2, 0) to give January 31, 2016. Instead, that function gives an error. Is this me being stupid, poor documentation, or something else..?

If it's poor documentation and I shouldn't expect that behaviour to change anytime soon, what's the recommended (safe) way to generate the date 7 (or any other non-zero number) days ago as of refresh? I was doing

DATE(YEAR(TODAY()), MONTH(TODAY()), DAY(TODAY()) - 7)

but that broke on the 7th of the month since it evaluated to day = 0 (I think that's why at least).

Thanks for your help! Sorry if this question is a duplicate, I'd imagine I'm not the first one to have this problem (if it is a problem) but I couldn't find a similar one in the forum.

[KHorseman]

You can subtract a whole number from a date. Move -7 outside the DATE function. Actually TODAY() - 7 should do the same.

[jahida]

That'll work great as a work-around, thanks. Still curious about the behaviour of the DATE function though.

[v-qiuyu-msft]

Hi @jahida,

Based on my research, the <day> parameter in the DATE() function needs to obey the rules in this article: DATE Function (DAX)

"A number representing the day or a calculation according to the following rules:

If day is a number from 1 to the last day of the given month then it represents a day of the month.

If you enter an integer larger than last day of the given month, the following computation occurs: the date is calculated by adding the value of day to month. For example, in the formula DATE( 2008, 3, 32), the DATE function returns a datetime value equivalent to April 1st of 2008, because 32 days are added to the beginning of March yielding a value of April 1st.

If you enter a negative integer, the following computation occurs: the date is calculated subtracting the value of day from month. For example, in the formula DATE( 2008, 5, -15), the DATE function returns a datetime value equivalent to April 15th of 2008, because 15 days are subtracted from the beginning of May 2008 yielding a value of April 2008.

If day contains a decimal portion, it is rounded to the nearest integer value. "

If the day is 0 in the DATE() function, it's not belonged to any valid rules. In my opinion, if we specify the expression =DATE(2016, 2, 0), it will try to be converted to date 2/0/2016 on backend, but 0 day is not available in any month, so the error throws out. In your scenario, it's expected that the error will throws out if the day parameter is 0 in DATE() function.

If you have any question, please feel free to ask.

Best Regards,
Qiuyun Yu

[jahida]

If that was the only part of the documentation, then I agree, having 0 as an error would be fair. But the section I included in my initial post seems to clearly specify the expected behaviour of 0: giving the last day of the previous month. Also, the documentation gives very similar rules for month:

"A number representing the month or a calculation according to the following rules:

If month is a number from 1 to 12, then it represents a month of the year. 1 represents January, 2 represents February, and so on until 12 that represents December.

If you enter an integer larger than 12, the following computation occurs: the date is calculated by adding the value of month to the year. For example, if you have DATE( 2008, 18, 1), the function returns a datetime value equivalent to June 1st of 2009, because 18 months are added to the beginning of 2008 yielding a value of June 2009. See examples below.

If you enter a negative integer, the following computation occurs: the date is calculated subtracting the value of month from year."

But having experimented, <month> = 0 isn't an error but a valid way to express the last month of the previous year.

Also, if -1 gave the last day of the previous month, I think it would be fair to have 0 give an error. But it doesn't; it gives the date 2 days before. So for example, if I wanted to use the format DATE(2016, 2, x), I can generate essentially every date imaginable EXCEPT for January 31, 2016. That doesn't seem reasonable. Anyway, I'll get over it. Thanks!

[skavan]

I had exactly the same issue. It's illogical.

The simple fix would be to provide a DATEADD function for scalar dates. (DATEADD only works with columns).

s.