Time intelligence functions

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. Before using any time-intelligence functions, make sure to mark one of the tables containing date column as Date Table.

In this category

Function Description
CLOSINGBALANCEMONTH Evaluates the expression at the last date of the month in the current context.
CLOSINGBALANCEQUARTER Evaluates the expression at the last date of the quarter in the current context.
CLOSINGBALANCEYEAR Evaluates the expression at the last date of the year in the current context.
DATEADD Returns 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 Returns a table that contains a column of dates that begins with a specified start date and continues until a specified end date.
DATESINPERIOD 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 Returns a table that contains a column of the dates for the month to date, in the current context.
DATESQTD Returns a table that contains a column of the dates for the quarter to date, in the current context.
DATESYTD Returns a table that contains a column of the dates for the year to date, in the current context.
ENDOFMONTH Returns the last date of the month in the current context for the specified column of dates.
ENDOFQUARTER Returns the last date of the quarter in the current context for the specified column of dates.
ENDOFYEAR Returns the last date of the year in the current context for the specified column of dates.
FIRSTDATE Returns the first date in the current context for the specified column of dates.
LASTDATE Returns the last date in the current context for the specified column of dates.
NEXTDAY 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 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 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 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 Evaluates the expression at the first date of the month in the current context.
OPENINGBALANCEQUARTER Evaluates the expression at the first date of the quarter, in the current context.
OPENINGBALANCEYEAR Evaluates the expression at the first date of the year in the current context.
PARALLELPERIOD 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 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 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 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 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 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 Returns the first date of the month in the current context for the specified column of dates.
STARTOFQUARTER Returns the first date of the quarter in the current context for the specified column of dates.
STARTOFYEAR Returns the first date of the year in the current context for the specified column of dates.
TOTALMTD Evaluates the value of the expression for the month to date, in the current context.
TOTALQTD Evaluates the value of the expression for the dates in the quarter to date, in the current context.
TOTALYTD Evaluates the year-to-date value of the expression in the current context.