Power BI: DAX: Date and Time Functions
The Date and Time Functions in Data Analysis Expressions (DAX) are similar to date and time functions in Microsoft Excel. However, DAX functions are based on the datetime data types used by Microsoft SQL Server. DAX also includes a set of time intelligence functions that enable you to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods.
Because there are many Time Intelligence Functions, we have divided them into a separate article:
Browse the rest of the Date and Time Functions on this page:
- DATE
- DATEVALUE
- DAY
- EDATE
- EOMONTH
- HOUR
- MINUTE
- MONTH
- NOW
- SECOND
- TIME
- TIMEVALUE
- TODAY
- WEEKDAY
- WEEKNUM
- YEAR
- YEARFRAC
Original article (before wiki edits) was written by Jeannine Takaki and JuanPablo Jofre and formatted by Mary Browning, Microsoft SQL Server Technical Writers.
DATE
The DATE function takes the integers that are input as arguments, and generates the corresponding date. The DATE function is most useful in situations where the year, month, and day are supplied by formulas. For example, the underlying data might contain dates in a format that is not recognized as a date, such as YYYYMMDD. You can use the DATE function in conjunction with other functions to convert the dates to a number that can be recognized as a date.
In contrast to Microsoft Excel, which stores dates as a serial number, PowerPivot date functions always return a datetime data type. However, you can use formatting to display dates as serial numbers if you want.
Syntax
DATE(<year>, <month>, <day>)
Example: The following formula returns the date July 8, 2009.
=DATE(2009,7,8)
Parameters
year. A number representing the year. The value of the year argument can include one to four digits. The year argument is interpreted according to the date system used by your computer. Dates beginning with March 1, 1900 are supported. If you enter a number that has decimal places, the number is rounded. For values greater than 9999 or less than zero (negative values), the function returns a #VALUE! error. If the year value is between 0 and 1899, the value is added to 1900 to produce the final value.
Note: You should use four digits for the year argument whenever possible to prevent unwanted results. For example, using 07 returns 1907 as the year value.
Example 1: If the value that you enter for the year argument is between 0 (zero) and 1899 (inclusive), that value is added to 1900 to calculate the year. The following formula returns January 2, 1908: (1900+08).
=DATE(08,1,2)
Example 2: If year is between 1900 and 9999 (inclusive), that value is used as the year. The following formula returns January 2, 2008.
=DATE(2008,1,2)
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.
If you enter a negative integer, the following computation occurs: the date is calculated subtracting the value of month from year. For example, if you have DATE( 2008, -6, 15), the function returns a datetime value equivalent to June 15th of 2007, because when 6 months are subtracted from the beginning of 2008 it yields a value of June 2007.
Example 1: If month is greater than 12, month adds that number of months to the first month in the year specified. The following formula returns the date February 2, 2009.
=DATE(2008,14,2)
Example 2: If the month value is less than 1, the DATE function subtracts the magnitude of that number of months, plus 1, from the first month in the year specified. The following formula returns September 2, 2007.
=DATE(2008,-3,2)
day. 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.
Example 1: If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. The following formula returns the date February 4, 2008.
=DATE(2008,1,35)
Example 2: If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified. The following formula returns December 16, 2007.
=DATE(2008,1,-15)
Example: Returning a Simple Date
The following formula returns the date July 8, 2009:
=DATE(2009,7,8)
Examples: Years Before 1899
If the value that you enter for the year argument is between 0 (zero) and 1899 (inclusive), that value is added to 1900 to calculate the year. The following formula returns January 2, 1908: (1900+08).
=DATE(08,1,2)
If the value that you enter for the year argument is between 0 (zero) and 1899 (inclusive), that value is added to 1900 to calculate the year. The following formula returns January 2, 3700: (1900+1800).
=DATE(1800,1,2)
Example: Years After 1899
If year is between 1900 and 9999 (inclusive), that value is used as the year. The following formula returns January 2, 2008:
=DATE(2008,1,2)
Example: Working with Months
If month is greater than 12, month adds that number of months to the first month in the year specified. The following formula returns the date February 2, 2009:
=DATE(2008,14,2)
If the month value is less than 1, the DATE function subtracts the magnitude of that number of months, plus 1, from the first month in the year specified. The following formula returns September 2, 2007:
=DATE(2008,-3,2)
Example: Working with Days
If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. The following formula returns the date February 4, 2008:
=DATE(2008,1,35)
If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified. The following formula returns December 16, 2007:
=DATE(2008,1,-15)
DATEVALUE
The DATEVALUE function uses the locale and date/time settings of the client computer to understand the text value when performing the conversion. If the current date/time settings represent dates in the format of Month/Day/Year, then the string, "1/8/2009", would be converted to a datetime value equivalent to January 8th of 2009. However, if the current date and time settings represent dates in the format of Day/Month/Year, the same string would be converted as a datetime value equivalent to August 1st of 2009.
If the year portion of the date_text argument is omitted, the DATEVALUE function uses the current year from your computer's built-in clock. Time information in the date_text argument is ignored.
Syntax
DATEVALUE(date_text)
Returns a date in datetime format.
Example
The following example returns a different datetime value depending on your computer's locale and settings for how dates and times are presented.
- In date/time settings where the day precedes the month, the example returns a datetime value corresponding to January 8th of 2009.
- In date/time settings where the month precedes the day, the example returns a datetime value corresponding to August 1st of 2009.
Example code:
=DATEVALUE("8/1/2009")
DAY
Returns the day of the month, a number from 1 to 31. The DAY function takes as an argument the date of the day you are trying to find. Dates can be provided to the function by using another date function, by using an expression that returns a date, or by typing a date in a datetime format. You can also type a date in one of the accepted string formats for dates.
Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri, the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.
When the date argument is a text representation of the date, the day function uses the locale and date/time settings of the client computer to understand the text value in order to perform the conversion. If the current date/time settings represent dates in the format of Month/Day/Year, then the string, "1/8/2009", is interpreted as a datetime value equivalent to January 8th of 2009, and the function returns 8. However, if the current date/time settings represent dates in the format of Day/Month/Year, the same string would be interpreted as a datetime value equivalent to August 1st of 2009, and the function returns 1.
Syntax
DAY(<date>)
Returns a number indicating the day of the month.
Example: Getting the Day from a Date Column
The following formula returns the day from the date in the column, [Birthdate].
=DAY([Birthdate])
Example: Getting the Day from a String Date
The following formulas return the day, 4, using dates that have been supplied as strings in an accepted text format.
=DAY("3-4-1007")
=DAY("March 4 2007")
Example: Using a Day Value as a Condition
The following expression returns the day that each sales order was placed, and flags the row as a promotional sale item if the order was placed on the 10th of the month.
=IF( DAY([SalesDate])=10,"promotion","")
EDATE
Returns the date that is the indicated number of months before or after the start date. Use EDATE to calculate maturity dates or due dates that fall on the same day of the month as the date of issue. In contrast to Microsoft Excel, which stores dates as sequential serial numbers, DAX works with dates in a datetime format. Dates stored in other formats are converted implicitly.
If start_date is not a valid date, EDATE returns an error. Make sure that the column reference or date that you supply as the first argument is a date. If months is not an integer, it is truncated.
When the date argument is a text representation of the date, the EDATE function uses the locale and date time settings of the client computer to understand the text value in order to perform the conversion. If the current date time settings represent a date in the format of Month/Day/Year, then the following string "1/8/2009" is interpreted as a datetime value equivalent to January 8th of 2009. However, if the current date time settings represent a date in the format of Day/Month/Year, the same string would be interpreted as a datetime value equivalent to August 1st of 2009.
If the requested date is past the last day of the corresponding month, then the last day of the month is returned. For example, the following functions: EDATE("2009-01-29", 1), EDATE("2009-01-30", 1), EDATE("2009-01-31", 1) return February 28th of 2009; that corresponds to one month after the start date.
Syntax
EDATE(<start_date>, <months>)
Returns a date (datetime).
Example
The following example returns the date three months after the order date, which is stored in the column [TransactionDate].
=EDATE([TransactionDate],3)
EOMONTH
Returns the date in datetime format of the last day of the month, before or after a specified number of months. Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. In contrast to Microsoft Excel, which stores dates as sequential serial numbers, DAX works with dates in a datetime format. The EOMONTH function can accept dates in other formats, with the following restrictions:
If start_date is not a valid date, EOMONTH returns an error. If start_date is a numeric value that is not in a datetime format, EOMONTH will convert the number to a date. To avoid unexpected results, convert the number to a datetime format before using the EOMONTH function. If start_date plus months yields an invalid date, EOMONTH returns an error. Dates before March 1st of 1900 and after December 31st of 9999 are invalid.
When the date argument is a text representation of the date, the EDATE function uses the locale and date time settings, of the client computer, to understand the text value in order to perform the conversion. If current date time settings represent a date in the format of Month/Day/Year, then the following string "1/8/2009" is interpreted as a datetime value equivalent to January 8th of 2009. However, if the current date time settings represent a date in the format of Day/Month/Year, the same string would be interpreted as a datetime value equivalent to August 1st of 2009.
Syntax
EOMONTH(<start_date>, <months>)
Returns a date (datetime).
Example
The following expression returns May 31, 2008, because the months argument is rounded to 2.
=EOMONTH("March 3, 2008",1.5)
HOUR
Returns the hour as a number from 0 (12:00 A.M.) to 23 (11:00 P.M.). The HOUR function takes as argument the time that contains the hour you want to find. You can supply the time by using a date/time function, an expression that returns a datetime, or by typing the value directly in one of the accepted time formats. Times can also be entered as any accepted text representation of a time.
When the datetime argument is a text representation of the date and time, the function uses the locale and date/time settings of the client computer to understand the text value in order to perform the conversion. Most countries in the world use the colon (:) as the time separator and any input text using colons as time separators will parse correctly. Review your locale settings to understand your results.
Syntax
HOUR(<datetime>)
Returns a number from 0 to 23.
Examples
The following example returns the hour from the TransactionTime column of a table named Orders.
=HOUR('Orders'[TransactionTime])
The following example returns 15, meaning the hour corresponding to 3 PM in a 24-hour clock. The text value is automatically parsed and converted to a date/time value.
=HOUR("March 3, 2008 3:00 PM")
MINUTE
Returns the minute as a number from 0 to 59, given a date and time value. In contrast to Microsoft Excel, which stores dates and times in a serial numeric format, DAX uses a datetime data type for dates and times. You can provide the datetime value to the MINUTE function by referencing a column that stores dates and times, by using a date/time function, or by using an expression that returns a date and time.
When the datetime argument is a text representation of the date and time, the function uses the locale and date/time settings of the client computer to understand the text value in order to perform the conversion. Most countries use the colon (:) as the time separator and any input text using colons as time separators will parse correctly. Verify your locale settings to understand your results.
Syntax
MINUTE(<datetime>)
Returns a number from 0 to 59.
Examples
The following example returns the minute from the value stored in the TransactionTime column of the Orders table.
=MINUTE(Orders[TransactionTime])
The following example returns 45, which is the number of minutes in the time 1:45 PM.
=MINUTE("March 23, 2008 1:45 PM")
MONTH
Returns the month as a number from 1 (January) to 12 (December). In contrast to Microsoft Excel, which stores dates as serial numbers, DAX uses a datetime format when working with dates. You can enter the date used as argument to the MONTH function by typing an accepted datetime format, by providing a reference to a column that contains dates, or by using an expression that returns a date.
Values returned by the YEAR, MONTH and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date is Hijri, the returned values for the YEAR, MONTH and DAY functions will be values associated with the equivalent Gregorian date.
When the date argument is a text representation of the date, the function uses the locale and date time settings of the client computer to understand the text value in order to perform the conversion. If the current date time settings represent a date in the format of Month/Day/Year, then the following string "1/8/2009" is interpreted as a datetime value equivalent to January 8th of 2009, and the function yields a result of 1. However, if the current date time settings represent a date in the format of Day/Month/Year, then the same string would be interpreted as a datetime value equivalent to August 1st of 2009, and the function yields a result of 8.
If the text representation of the date cannot be correctly converted to a datetime value, the function returns an error.
Syntax
MONTH(<datetime>)
Returns a number from 1 to 12 .
Examples
The following expression returns 3, which is the integer corresponding to March, the month in the date argument.
=MONTH("March 3, 2008 3:45 PM")
The following expression returns the month from the date in the TransactionDate column of the Orders table.
=MONTH(Orders[TransactionDate])
NOW
Returns the current date and time in datetime format. The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet. In contrast to Microsoft Excel, which stores dates and times as serial numbers, DAX uses a datetime format to work with dates. Dates that are not in this format are implicitly converted when you use dates and times in a formula.
The result of the NOW function changes only when the column that contains the formula is refreshed. It is not updated continuously. The TODAY function returns the same date but is not precise with regard to time; the time returned is always 12:00:00 AM and only the date is updated.
Syntax
NOW()
Returns a date (datetime).
Example
The following example returns the current date and time plus 3.5 days:
=NOW()+3.5
SECOND
Returns the seconds of a time value, as a number from 0 to 59.
Syntax
SECOND(<time>)
Returns a number from 0 to 59.
Examples
The following formula returns the number of seconds in the time contained in the TransactionTime column of a table named Orders.
=SECOND('Orders'[TransactionTime])
The following formula returns 3, which is the number of seconds in the time represented by the value, March 3, 2008 12:00:03.
=SECOND("March 3, 2008 12:00:03")
TIME
Converts hours, minutes, and seconds given as numbers to a time in datetime format. In contrast to Microsoft Excel, which stores dates and times as serial numbers, DAX works with date and time values in a datetime format. Numbers in other formats are implicitly converted when you use a date/time value in a DAX function. If you need to use serial numbers, you can use formatting to change the way that the numbers are displayed.
Time values are a portion of a date value, and in the serial number system are represented by a decimal number. Therefore, the datetime value 12:00 PM is equivalent to 0.5, because it is half of a day.
You can supply the arguments to the TIME function as values that you type directly, as the result of another expression, or by a reference to a column that contains a numeric value. The following restrictions apply:
- Any value for hours that is greater than 23 will be divided by 24 and the remainder will be treated as the hour value.
- Any value for minutes that is greater than 59 will be converted to hours and minutes.
- Any value for seconds that is greater than 59 will be converted to hours, minutes, and seconds.
- For minutes or seconds, a value greater than 24 hours will be divided by 24 and the reminder will be treated as the hour value. A value in excess of 24 hours does not alter the date portion.
To improve readability of the time values returned by this function, we recommend that you format the column or PivotTable cell that contains the results of the formula by using one of the time formats provided by Microsoft Excel.
Syntax
TIME(hour, minute, second)
Returns a time (datetime).
Examples
The following examples both return the time, 3:00 AM:
=TIME(27,0,0)
=TIME(3,0,0)
The following examples both return the time, 12:30 PM:
=TIME(0,750,0)
=TIME(12,30,0)
The following example creates a time based on the values in the columns, intHours, intMinutes, intSeconds:
=TIME([intHours],[intMinutes],[intSeconds])
TIMEVALUE
Converts a time in text format to a time in datetime format. Time values are a portion of a date value and represented by a decimal number. For example, 12:00 PM is represented as 0.5 because it is half of a day.
When the time_text argument is a text representation of the date and time, the function uses the locale and date/time settings of the client computer to understand the text value in order to perform the conversion. Most countries in the world use the colon (:) as the time separator, and any input text using colons as time separators will parse correctly. Review your locale settings to understand your results.
Syntax
TIMEVALUE(time_text)
Returns a date (datetime).
Example
=TIMEVALUE("20:45:30")
TODAY
Returns the current date. The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals.
Note: If the TODAY function does not update the date when you expect it to, you might need to change the settings that control when the column or workbook is refreshed.
Syntax
TODAY()
Returns a date (datetime).
Example
If you know that someone was born in 1963, you might use the following formula to find that person's age as of this year's birthday:
=YEAR(TODAY())-1963
This formula uses the TODAY function as an argument for the YEAR function to obtain the current year, and then subtracts 1963, returning the person's age.
WEEKDAY
Returns a number from 1 to 7 identifying the day of the week of a date. By default the day ranges from 1 (Sunday) to 7 (Saturday). In contrast to Microsoft Excel, which stores dates as serial numbers, DAX works with dates and times in a datetime format. If you need to display dates as serial numbers, you can use the formatting options in Excel. You can also type dates in an accepted text representation of a date, but to avoid unexpected results, it is best to convert the text date to a datetime format first.
When the date argument is a text representation of the date, the function uses the locale and date/time settings of the client computer to understand the text value in order to perform the conversion. If the current date/time settings represent dates in the format of Month/Day/Year, then the string, "1/8/2009", is interpreted as a datetime value equivalent to January 8th of 2009. However, if the current date/time settings represent dates in the format of Day/Month/Year, then the same string would be interpreted as a datetime value equivalent to August 1st of 2009.
Syntax
WEEKDAY(<date>, <return_type>)
Returns a number from 1 to 7.
Example
The following example gets the date from the [HireDate] column, adds 1, and displays the weekday corresponding to that date. Because the return_type argument has been omitted, the default format is used, in which 1 is Sunday and 7 is Saturday. If the result is 4, the day would be Wednesday.
=WEEKDAY([HireDate]+1)
WEEKNUM
Returns the week number for the given date and year according to the return_type value. The week number indicates where the week falls numerically within a year. In contrast to Microsoft Excel, which stores dates as serial numbers, DAX uses a datetime data type to work with dates and times. If the source data is in a different format, DAX implicitly converts the data to datetime to perform calculations.
By default, the WEEKNUM function uses a calendar convention in which the week containing January 1 is considered to be the first week of the year. However, the ISO 8601 calendar standard, widely used in Europe, defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are different from the ISO 8601 definition.
Syntax
WEEKNUM(<date>, <return_type>)
Returns a number.
Examples
The following example returns the week number of the date February 14, 2010.
=WEEKNUM("Feb 14, 2010", 2)
The following example returns the week number of the date stored in the column, HireDate, from the table, Employees.
=WEEKNUM('Employees'[HireDate])
YEAR
Returns the year of a date as a four digit integer in the range 1900-9999. In contrast to Microsoft Excel, which stores dates as serial numbers, DAX uses a datetime data type to work with dates and times.
Dates should be entered by using the DATE function, or as results of other formulas or functions. You can also enter dates in accepted text representations of a date, such as March 3, 2003, or Mar-3-2003. Values returned by the YEAR, MONTH, and DAY functions will be Gregorian values regardless of the display format for the supplied date value. For example, if the display format of the supplied date uses the Hijri calendar, the returned values for the YEAR, MONTH, and DAY functions will be values associated with the equivalent Gregorian date.
When the date argument is a text representation of the date, the function uses the locale and date time settings of the client computer to understand the text value in order to perform the conversion. Errors may arise if the format of strings is incompatible with the current locale settings. For example, if your locale defines dates to be formatted as month/day/year, and the date is provided as day/month/year, then 25/1/2009 will not be interpreted as January 25th of 2009 but as an invalid date.
Syntax
YEAR(<date>)
Returns an integer in the range 1900-9999.
Example
The following example returns 2003.
=YEAR("March 2003")
Example: Date as Result of Expression
The following example returns the year for today's date.
=YEAR(TODAY())
YEARFRAC
Calculates the fraction of the year represented by the number of whole days between two dates. Use the YEARFRAC worksheet function to identify the proportion of a whole year's benefits or obligations to assign to a specific term. In contrast to Microsoft Excel, which stores dates as serial numbers, DAX uses a datetime format to work with dates and times. If you need to view dates as serial numbers, you can use the formatting options in Excel.
If start_date or end_date are not valid dates, YEARFRAC returns an error. If basis < 0 or if basis > 4, YEARFRAC returns an error.
Syntax
YEARFRAC(<start_date>, <end_date>, <basis>)
Returns a decimal number. The internal data type is a signed IEEE 64-bit (8-byte) double-precision floating-point number.
Examples
The following example returns the fraction of a year represented by the difference between the dates in the two columns, TransactionDate and ShippingDate:
=YEARFRAC(Orders[TransactionDate],Orders[ShippingDate])
The following example returns the fraction of a year represented by the difference between the dates, January 1 and March 1:
=YEARFRAC("Jan 1 2007","Mar 1 2007")
Use four-digit years whenever possible, to avoid getting unexpected results. When the year is truncated, the current year is assumed. When the date is or omitted, the first date of the month is assumed.
The second argument, basis, has also been omitted. Therefore, the year fraction is calculated according to the US (NASD) 30/360 standard.
See Also
External Links