Forum Discussion

jahida's avatar
jahida
Impactful Individual
10 years ago

0 Value for Fields in DATE function

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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago
    You can subtract a whole number from a date. Move -7 outside the DATE function. Actually TODAY() - 7 should do the same.
    • jahida's avatar
      jahida
      Impactful Individual
      10 years ago

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

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        10 years ago

        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