This reference lists 20 DAX date & time functions, with frequently used functions marked 🔥. Each function includes syntax, an example and parameter notes.
| Function | Description | Syntax | Example |
|---|---|---|---|
CALENDAR 🔥 |
Returns a table of contiguous dates | CALENDAR(<start_date>, <end_date>) |
CALENDAR(DATE(2020,1,1), DATE(2020,12,31)) |
CALENDARAUTO 🔥 |
Returns a date table automatically based on the data model | CALENDARAUTO([<fiscal_year_end_month>]) |
CALENDARAUTO() |
DATE 🔥 |
Returns a date value from year, month and day | DATE(<year>, <month>, <day>) |
DATE(2020, 12, 25) |
DATEDIFF 🔥 |
Returns the difference between two dates | DATEDIFF(<date1>, <date2>, <interval>) |
DATEDIFF(DATE(2020,1,1), DATE(2020,12,31), DAY) |
DAY 🔥 |
Returns the day of the month (a number from 1 to 31) | DAY(<date>) |
DAY("2020-12-25") |
EOMONTH 🔥 |
Returns the last day of the month before or after the given number of months | EOMONTH(<start_date>, <months>) |
EOMONTH(TODAY(), 1) |
MONTH 🔥 |
Returns the month (a number from 1 to 12) | MONTH(<datetime>) |
MONTH("2020-12-25") |
NOW 🔥 |
Returns the current date and time | NOW() |
NOW() |
TODAY 🔥 |
Returns the current date | TODAY() |
TODAY() |
YEAR 🔥 |
Returns the year (a number from 1900 to 9999) | YEAR(<datetime>) |
YEAR(TODAY()) |
DATEVALUE |
Converts a date in text format to a date value | DATEVALUE(<date_text>) |
DATEVALUE("2020-12-25") |
EDATE |
Returns the date before or after the given number of months | EDATE(<date>, <months>) |
EDATE(TODAY(), 6) |
HOUR |
Returns the hour (a number from 0 to 23) | HOUR(<datetime>) |
HOUR(NOW()) |
MINUTE |
Returns the minutes (a number from 0 to 59) | MINUTE(<datetime>) |
MINUTE(NOW()) |
SECOND |
Returns the seconds (a number from 0 to 59) | SECOND(<datetime>) |
SECOND(NOW()) |
TIME |
Returns a time value from hours, minutes and seconds | TIME(<hour>, <minute>, <second>) |
TIME(14, 30, 0) |
TIMEVALUE |
Converts a time in text format to a time value | TIMEVALUE(<time_text>) |
TIMEVALUE("14:30:00") |
WEEKDAY |
Returns the day of the week (a number from 1 to 7) | WEEKDAY(<date>, [<return_type>]) |
WEEKDAY(TODAY()) |
WEEKNUM |
Returns the week number within the year (1 to 53) | WEEKNUM(<date>, [<return_type>]) |
WEEKNUM(TODAY()) |
YEARFRAC |
Returns the fraction of a year between two dates | YEARFRAC(<start_date>, <end_date>, [<basis>]) |
YEARFRAC(DATE(2020,1,1), DATE(2020,6,30)) |
Detailed parameters for frequently used functions
CALENDAR
Returns a table of contiguous dates.
| Parameter | Description |
|---|---|
start_date |
The start date. |
end_date |
The end date. |
CALENDARAUTO
Returns a date table automatically based on the data model.
| Parameter | Description |
|---|---|
fiscal_year_end_month |
(Optional) The fiscal year end month (1-12). Defaults to January. |
DATE
Returns a date value from year, month and day.
| Parameter | Description |
|---|---|
year |
The year (may exceed 12 digits when composed). |
month |
The month (may be greater than 12). |
day |
The day (may exceed the month's day count). |
DATEDIFF
Returns the difference between two dates.
| Parameter | Description |
|---|---|
date1 |
The first date. |
date2 |
The second date. |
interval |
The interval unit: SECOND, MINUTE, HOUR, DAY, MONTH, QUARTER or YEAR. |
DAY
Returns the day of the month (a number from 1 to 31).
| Parameter | Description |
|---|---|
date |
A date value or a date in text format. |
EOMONTH
Returns the last day of the month before or after the given number of months.
| Parameter | Description |
|---|---|
start_date |
The start date. |
months |
The number of months to add. |
MONTH
Returns the month (a number from 1 to 12).
| Parameter | Description |
|---|---|
datetime |
A date value or a date in text format. |
NOW
Returns the current date and time.
| Parameter | Description |
|---|
TODAY
Returns the current date.
| Parameter | Description |
|---|
YEAR
Returns the year (a number from 1900 to 9999).
| Parameter | Description |
|---|---|
datetime |
A date value or a date in text format. |
From Online Toolbox (www.vba.net)