This reference lists 47 DAX time intelligence functions, with frequently used functions marked 🔥. Each function includes syntax, an example and parameter notes.
| Function | Description | Syntax | Example |
|---|---|---|---|
DATEADD 🔥 |
Shifts dates by the specified interval | DATEADD(<dates>, <number_of_intervals>, <interval>) |
CALCULATE(SUM(Sales[Amount]), DATEADD(Sales[Date], -1, YEAR)) |
DATESBETWEEN 🔥 |
Returns a table of dates between two dates | DATESBETWEEN(<dates>, <start_date>, <end_date>) |
DATESBETWEEN(Sales[Date], DATE(2020,1,1), DATE(2020,12,31)) |
DATESMTD 🔥 |
Returns the dates month-to-date in the current context | DATESMTD(<dates>) |
CALCULATE(SUM(Sales[Amount]), DATESMTD(Sales[Date])) |
DATESYTD 🔥 |
Returns the dates year-to-date in the current context | DATESYTD(<dates>) |
CALCULATE(SUM(Sales[Amount]), DATESYTD(Sales[Date])) |
PARALLELPERIOD 🔥 |
Returns a period parallel to the current one — previous, next or same period | PARALLELPERIOD(<dates>, <number_of_intervals>, <interval>) |
CALCULATE(SUM(Sales[Amount]), PARALLELPERIOD(Sales[Date], -1, YEAR)) |
PREVIOUSMONTH 🔥 |
Returns a table of all dates in the previous month | PREVIOUSMONTH(<dates>) |
CALCULATE(SUM(Sales[Amount]), PREVIOUSMONTH(Sales[Date])) |
PREVIOUSYEAR 🔥 |
Returns a table of all dates in the previous year | PREVIOUSYEAR(<dates>) |
CALCULATE(SUM(Sales[Amount]), PREVIOUSYEAR(Sales[Date])) |
SAMEPERIODLASTYEAR 🔥 |
Returns the set of dates for the same relative period in the previous year | SAMEPERIODLASTYEAR(<dates>) |
CALCULATE(SUM(Sales[Amount]), SAMEPERIODLASTYEAR(Sales[Date])) |
TOTALMTD 🔥 |
Evaluates the month-to-date expression in the current context | TOTALMTD(<expression>, <dates>) |
TOTALMTD(SUM(Sales[Amount]), Sales[Date]) |
TOTALYTD 🔥 |
Evaluates the year-to-date expression in the current context | TOTALYTD(<expression>, <dates>) |
TOTALYTD(SUM(Sales[Amount]), Sales[Date]) |
CLOSINGBALANCEMONTH |
Evaluates the expression on the last day of the month in the current context | CLOSINGBALANCEMONTH(<expression>, <dates>) |
CLOSINGBALANCEMONTH(SUM(Sales[Amount]), Sales[Date]) |
CLOSINGBALANCEQUARTER |
Evaluates the expression on the last day of the quarter in the current context | CLOSINGBALANCEQUARTER(<expression>, <dates>) |
CLOSINGBALANCEQUARTER(SUM(Sales[Amount]), Sales[Date]) |
CLOSINGBALANCEWEEK |
Returns the cumulative value on the last day of the current week | CLOSINGBALANCEWEEK(<expression>, <dates>, [<filter>], [<year_end>]) |
CLOSINGBALANCEWEEK(SUM(Sales[Amount]), Sales[Date]) |
CLOSINGBALANCEYEAR |
Evaluates the expression on the last day of the year in the current context | CLOSINGBALANCEYEAR(<expression>, <dates>) |
CLOSINGBALANCEYEAR(SUM(Sales[Amount]), Sales[Date]) |
DATESDIF |
Returns the difference between two dates | DATESDIF(<start_date>, <end_date>, <unit>) |
DATESDIF(DATE(2020,1,1), DATE(2020,12,31), DAY) |
DATESINPERIOD |
Returns a table of dates over a period | DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>) |
DATESINPERIOD(Sales[Date], TODAY(), -30, DAY) |
DATESQTD |
Returns the dates quarter-to-date in the current context | DATESQTD(<dates>) |
CALCULATE(SUM(Sales[Amount]), DATESQTD(Sales[Date])) |
ENDOFMONTH |
Returns the last day of the month in the current context | ENDOFMONTH(<dates>) |
ENDOFMONTH(Sales[Date]) |
ENDOFQUARTER |
Returns the last day of the quarter in the current context | ENDOFQUARTER(<dates>) |
ENDOFQUARTER(Sales[Date]) |
ENDOFWEEK |
Returns the last day of the current week | ENDOFWEEK(<dates>, [<first_day_of_week>]) |
ENDOFWEEK(Sales[Date]) |
ENDOFYEAR |
Returns the last day of the year in the current context | ENDOFYEAR(<dates>) |
ENDOFYEAR(Sales[Date]) |
FIRSTDATE |
Returns the first date in the current context | FIRSTDATE(<dates>) |
FIRSTDATE(Sales[Date]) |
FIRSTNONBLANK |
Returns the first value for which the expression is not blank | FIRSTNONBLANK(<column>, <expression>) |
FIRSTNONBLANK(Sales[Date], SUM(Sales[Amount])) |
LASTDATE |
Returns the last date in the current context | LASTDATE(<dates>) |
LASTDATE(Sales[Date]) |
LASTDAY |
Returns the last day of the month of the given date | LASTDAY(<dates>) |
LASTDAY(Sales[Date]) |
LASTNONBLANK |
Returns the last value for which the expression is not blank | LASTNONBLANK(<column>, <expression>) |
LASTNONBLANK(Sales[Date], SUM(Sales[Amount])) |
MTD |
Returns month-to-date dates in the current context (uses DATESMTD) | MTD(<dates>) |
CALCULATE(SUM(Sales[Amount]), MTD(Sales[Date])) |
NEXTDAY |
Returns a table of all dates on the next day | NEXTDAY(<dates>) |
CALCULATE(SUM(Sales[Amount]), NEXTDAY(Sales[Date])) |
NEXTMONTH |
Returns a table of all dates in the next month | NEXTMONTH(<dates>) |
CALCULATE(SUM(Sales[Amount]), NEXTMONTH(Sales[Date])) |
NEXTQUARTER |
Returns a table of all dates in the next quarter | NEXTQUARTER(<dates>) |
CALCULATE(SUM(Sales[Amount]), NEXTQUARTER(Sales[Date])) |
NEXTWEEK |
Returns a table of all dates in the next week | NEXTWEEK(<dates>, [<first_day_of_week>]) |
NEXTWEEK(Sales[Date]) |
NEXTYEAR |
Returns a table of all dates in the next year | NEXTYEAR(<dates>) |
CALCULATE(SUM(Sales[Amount]), NEXTYEAR(Sales[Date])) |
OPENINGBALANCEMONTH |
Evaluates the expression on the first day of the month in the current context | OPENINGBALANCEMONTH(<expression>, <dates>) |
OPENINGBALANCEMONTH(SUM(Sales[Amount]), Sales[Date]) |
OPENINGBALANCEQUARTER |
Evaluates the expression on the first day of the quarter in the current context | OPENINGBALANCEQUARTER(<expression>, <dates>) |
OPENINGBALANCEQUARTER(SUM(Sales[Amount]), Sales[Date]) |
OPENINGBALANCEWEEK |
Returns the cumulative value on the first day of the current week | OPENINGBALANCEWEEK(<expression>, <dates>, [<filter>], [<year_end>]) |
OPENINGBALANCEWEEK(SUM(Sales[Amount]), Sales[Date]) |
OPENINGBALANCEYEAR |
Evaluates the expression on the first day of the year in the current context | OPENINGBALANCEYEAR(<expression>, <dates>) |
OPENINGBALANCEYEAR(SUM(Sales[Amount]), Sales[Date]) |
PREVIOUSDAY |
Returns a table of all dates on the previous day | PREVIOUSDAY(<dates>) |
CALCULATE(SUM(Sales[Amount]), PREVIOUSDAY(Sales[Date])) |
PREVIOUSQUARTER |
Returns a table of all dates in the previous quarter | PREVIOUSQUARTER(<dates>) |
CALCULATE(SUM(Sales[Amount]), PREVIOUSQUARTER(Sales[Date])) |
PREVIOUSWEEK |
Returns a table of all dates in the previous week | PREVIOUSWEEK(<dates>, [<first_day_of_week>]) |
PREVIOUSWEEK(Sales[Date]) |
QTD |
Returns quarter-to-date dates in the current context (uses DATESQTD) | QTD(<dates>) |
CALCULATE(SUM(Sales[Amount]), QTD(Sales[Date])) |
STARTOFMONTH |
Returns the first day of the month in the current context | STARTOFMONTH(<dates>) |
STARTOFMONTH(Sales[Date]) |
STARTOFQUARTER |
Returns the first day of the quarter in the current context | STARTOFQUARTER(<dates>) |
STARTOFQUARTER(Sales[Date]) |
STARTOFWEEK |
Returns the first day of the current week | STARTOFWEEK(<dates>, [<first_day_of_week>]) |
STARTOFWEEK(Sales[Date]) |
STARTOFYEAR |
Returns the first day of the year in the current context | STARTOFYEAR(<dates>) |
STARTOFYEAR(Sales[Date]) |
TOTALQTD |
Evaluates the quarter-to-date expression in the current context | TOTALQTD(<expression>, <dates>) |
TOTALQTD(SUM(Sales[Amount]), Sales[Date]) |
TOTALWTD |
Computes the week-to-date cumulative value of a measure | TOTALWTD(<expression>, [<dates>], [<filter>], [<year_end>]) |
TOTALWTD(SUM(Sales[Amount]), Sales[Date]) |
YTD |
Returns year-to-date dates in the current context (uses DATESYTD) | YTD(<dates>) |
CALCULATE(SUM(Sales[Amount]), YTD(Sales[Date])) |
Detailed parameters for frequently used functions
DATEADD
Shifts dates by the specified interval.
| Parameter | Description |
|---|---|
dates |
A column of dates. |
number_of_intervals |
The number of intervals to shift. Positive moves forward, negative backward. |
interval |
The interval unit: DAY, MONTH, QUARTER or YEAR. |
DATESBETWEEN
Returns a table of dates between two dates.
| Parameter | Description |
|---|---|
dates |
A column of dates. |
start_date |
The start date. |
end_date |
The end date. |
DATESMTD
Returns the dates month-to-date in the current context.
| Parameter | Description |
|---|---|
dates |
A column of dates. |
DATESYTD
Returns the dates year-to-date in the current context.
| Parameter | Description |
|---|---|
dates |
A column of dates. It must belong to or relate to a date table. |
PARALLELPERIOD
Returns a period parallel to the current one — previous, next or same period.
| Parameter | Description |
|---|---|
dates |
A column of dates. |
number_of_intervals |
The number of periods to shift. Positive moves forward, negative backward. |
interval |
The interval unit: MONTH, QUARTER or YEAR. |
PREVIOUSMONTH
Returns a table of all dates in the previous month.
| Parameter | Description |
|---|---|
dates |
A column of dates. Returns all dates of the previous month. |
PREVIOUSYEAR
Returns a table of all dates in the previous year.
| Parameter | Description |
|---|---|
dates |
A column of dates. Returns the dates from Jan 1 to Dec 31 of the previous year. |
SAMEPERIODLASTYEAR
Returns the set of dates for the same relative period in the previous year.
| Parameter | Description |
|---|---|
dates |
A column of dates. Returns the range in the previous year matching the current date range. |
TOTALMTD
Evaluates the month-to-date expression in the current context.
| Parameter | Description |
|---|---|
expression |
The expression whose cumulative value is computed. |
dates |
A column of dates. |
TOTALYTD
Evaluates the year-to-date expression in the current context.
| Parameter | Description |
|---|---|
expression |
The expression whose cumulative value is computed. |
dates |
A column of dates. |
From Online Toolbox (www.vba.net)