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

17 January 2023

*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:

**DATE(year,
month, day)**

**year**:

- 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 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^{st}January 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**.*