Time Intelligence Functions Using DAX
Data Analysis Expressions (DAX) includes 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.
DA-100 exam is retired. Exam PL-300: Microsoft Power BI Data Analyst, is available.
| Function | Description |
|---|---|
| CLOSINGBALANCEMONTH | It is used to evaluate the expression at the last date of the month in the current context. |
| CLOSINGBALANCEQUARTER | It is used to evaluate the expression at the last date of the quarter in the current context. |
| CLOSINGBALANCEYEAR | It is used to evaluate the expression at the last date of the year in the current context. |
| DATEADD | It is used to return a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context. |
| DATESBETWEEN | It returns a table that contains a column of dates that begins with a specified start date and continues until specified end date. |
| DATESINPERIOD | It returns a table that contains a column of dates that begins with a specified start date and continues for the specified number and type of date intervals. |
| DATESMTD | It returns a table that contains a column of the dates for the month to date, in the current context. |
| DATESQTD | It returns a table that contains a column of the dates for the quarter to date, in the current context. |
| DATESYTD | It returns a table that contains a column of the dates for the year to date, in the current context. |
| ENDOFMONTH | It returns the last date of the month in the current context for the specified column of dates. |
| ENDOFQUARTER | It returns the last date of the quarter in the current context for the specified column of dates. |
| ENDOFYEAR | It returns the last date of the year in the current context for the specified column of dates. |
| FIRSTDATE | It returns the first date in the current context for the specified column of dates. |
| FIRSTNONBLANK | It returns the first value in the column, column, filtered by the current context, where the expression is not blank |
| LASTDATE | It returns the last date in the current context for the specified column of dates. |
| LASTNONBLANK | It returns the last value in the column, column, filtered by the current context, where the expression is not blank. |
| NEXTDAY | It returns a table that contains a column of all dates from the next day, based on the first date specified in the dates column in the current context. |
| NEXTMONTH | It returns a table that contains a column of all dates from the next month, based on the first date in the dates column in the current context. |
| NEXTQUARTER | It returns a table that contains a column of all dates in the next quarter, based on the first date specified in the dates column, in the current context. |
| NEXTYEAR | It returns a table that contains a column of all dates in the next year, based on the first date in the dates column, in the current context. |
| OPENINGBALANCEMONTH | It is used to evaluate the expression at the first date of the month in the current context. |
| OPENINGBALANCEQUARTER | It is used to evaluate the expression at the first date of the quarter, in the current context. |
| OPENINGBALANCEYEAR | It is used to evaluate the expression at the first date of the year in the current context. |
| PARALLELPERIOD | It returns a table that contains a column of dates that represents a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward in time or back in time. |
| PREVIOUSDAY | It returns a table that contains a column of all dates representing the day that is previous to the first date in the dates column, in the current context. |
| PREVIOUSMONTH | It returns a table that contains a column of all dates from the previous month, based on the first date in the dates column, in the current context. |
| PREVIOUSQUARTER | It returns a table that contains a column of all dates from the previous quarter, based on the first date in the dates column, in the current context. |
| PREVIOUSYEAR | It returns a table that contains a column of all dates from the previous year, given the last date in the dates column, in the current context. |
| SAMEPERIODLASTYEAR | It returns a table that contains a column of dates shifted one year back in time from the dates in the specified dates column, in the current context. |
| STARTOFMONTH | It returns the first date of the month in the current context for the specified column of dates. |
| STARTOFQUARTER | It returns the first date of the quarter in the current context for the specified column of dates. |
| STARTOFYEAR | It returns the first date of the year in the current context for the specified column of dates. |
| TOTALMTD | It is used to evaluate the value of the expression for the month to date, in the current context. |
| TOTALQTD | It is used to evaluate the value of the expression for the dates in the quarter to date, in the current context. |
| TOTALYTD | It is used to evaluate the year-to-date value of the expression in the current context. |
Learn more about Time Intelligence Functions
