Power Pivot Principles: The A to Z of DAX Functions – DATE

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions.  This week, we look at DATE.

The DATE function

The DATE function is one of the ‘Date and Time’ functions.  It returns the specified date in datetime format, and it operates under the following syntax:

• year: this argument is required, and it is a number representing the year.  This argument can have one [1] to four [4] digits and is interpreted according to the date system used by your computer.

• supported dates span back to March 1, 1900
• this will be rounded to an integer
• the DATE function returns an #VALUE! error for year numbers greater than 9999 or less than zero (negative values)
• if the year value is in the range of 0 to 1899, the value will be added to 1990 to produce the final value.  For instance, using 07 gives the year value of 1907.  To avoid undesirable outcomes the year argument should always be four digits long.
• month: this argument is required, and it is a number from one [1] to twelve [12] representing the month
• this will be rounded to an integer
• the DATE function returns an #VALUE! error for month numbers less than zero [0] (negative values)
• if you enter a number from one [1] to twelve [12] it will represent the corresponding month of the year
  
• if you enter a number larger than twelve [12] it will calculate the month by adding the corresponding number of months to the year.  For example, if you type DATE(2000, 25, 1), the DATE function will return a datetime value equivalent to 1^stJanuary 2002.  This is because 25 months are added to the beginning of 2000 yielding value of January 2002.
  

• day: this argument is required, and it is a number from one [1] to 31 representing the date

• this will be rounded to an integer
• the DATE function returns an #VALUE! error for day numbers which is less than zero (negative values)
• if you enter a number from one [1] to 31 it will represent the corresponding date of the month
• if you enter a number larger than the last day of the given month it will calculate the day by adding the corresponding number of days to the month.  For example, if you type DATE(2000, 1, 32), the DATE function will return a datetime value equivalent to 1^st February 2000.  This is because 32 days are added to the beginning of January 2000.

  Please see the example below:  

which will thus return the following:

It should be noted that:

• the date is generated by the DATE function using the input numbers as arguments.  The DATE function is most helpful when the year, month, and day are determined by formulae.  For example, the underlying data, may include dates in a format that are not recognised, such as YYYYMMDD.  The DATE function could be used in conjunction with other functions to transform these dates into a format that will be recognised
• DAX DATE functions always return a datetime data type, unlike Microsoft Excel, which maintains dates as a serial number.  However, you may utilise formatting to show dates as sequential numbers
• the formats dt"YYYY-MM-DD", dt"YYYY-MM-DDThh:mm:ss", or dt"YYYY-MM-DD hh:mm:ss" can also be used to provide date and datetime.  The DATE function in the expression is not required when the value is given as a literal
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Come back next week for our next post on Power Pivot in the Blog section.  In the meantime, please remember we have training in Power Pivot which you can find out more about here.  If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.