Data Analysis Expressions (DAX) includes time intelligence functions to support the needs of Business Intelligence analysis by enabling you to manipulate data using time periods, including days, months, quarters, and years, and then build and compare calculations over those periods. (For community resources, see the DAX Resource Center.)
The time intelligence functions are sometimes categorized with the Date and Time Functions.
Read more about the following time intelligence functions:
Original article (before wiki edits) was written by Jeannine Takaki and JuanPablo Jofre and formatted by Mary Browning, Microsoft SQL Server Technical Writers.
Evaluates the expression at the last date of the month in the current context.
Note: To understand more about how context affects the results of formulas, see Context in DAX.
The dates argument can be any of the following:
A reference to a date/time column.
A table expression that returns a single column of date/time values.
A Boolean expression that defines a single-column table of date/time values.
Note: Constraints on Boolean expressions are described under CALCULATE.
Note: The filter expression has restrictions described under CALCULATE.
CLOSINGBALANCEMONTH(<expression>,<dates>[,<filter>])
expression. An expression that returns a scalar value.
dates. A column that contains dates.
filter. (optional) An expression that specifies a filter to apply to the current context.
Returns a scalar value that represents the expression evaluated at the last date of the month in the current context.
The following sample formula creates a measure that calculates the 'Month End Inventory Value' of the product inventory.
To see how this works, create a PivotTable and add the fields, CalendarYear, MonthNumberOfYear and DayNumberOfMonth, to the Row Labels area of the PivotTable. Then add a measure, named Month End Inventory Value, using the formula defined in the code section, to the Values area of the PivotTable.
=CLOSINGBALANCEMONTH(SUMX(ProductInventory,ProductInventory[UnitCost] * ProductInventory[UnitsBalance]),DateTime[DateKey])
↑ Back to top
Evaluates the expression at the last date of the quarter in the current context.
CLOSINGBALANCEQUARTER(<expression>,<dates>[,<filter>])
Returns a scalar value that represents the expression evaluated at the last date of the quarter in the current context.
The following sample formula creates a measure that calculates the 'Quarter End Inventory Value' of the product inventory.
To see how this works, create a PivotTable and add the fields, CalendarYear, CalendarQuarter and MonthNumberOfYear, to the Row Labels area of the PivotTable. Then add a measure, named Quarter End Inventory Value, using the formula defined in the code section, to the Values area of the PivotTable.
=CLOSINGBALANCEQUARTER(SUMX(ProductInventory,ProductInventory[UnitCost] * ProductInventory[UnitsBalance]),DateTime[DateKey])
Evaluates the expression at the last date of the year in the current context.
The year_end_date parameter is a string literal of a date, in the same locale as the locale of the client where the workbook was created. The year portion of the date is ignored.
CLOSINGBALANCEYEAR(<expression>,<dates>[,<filter>][,<year_end_date>])
year_end_date. (optional) A literal string with a date that defines the year-end date. The default is December 31.
Returns a scalar value that represents the expression evaluated at the last date of the year in the current context.
The following sample formula creates a measure that calculates the 'Year End Inventory Value' of the product inventory.
To see how this works, create a PivotTable and add the field, CalendarYear, to the Row Labels area of the PivotTable. Then add a measure, named Year End Inventory Value, using the formula defined in the code section, to the Values area of the PivotTable.
=CLOSINGBALANCEYEAR(SUMX(ProductInventory,ProductInventory[UnitCost] * ProductInventory[UnitsBalance]),DateTime[DateKey])
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.
If the number specified for number_of_intervals is positive, the dates in dates are moved forward in time; if the number is negative, the dates in dates are shifted back in time.
The interval parameter is an enumeration, not a set of strings; therefore values should not be enclosed in quotation marks. Also, the values: year, quarter, month, day should be spelled in full when using them.
The result table includes only dates that exist in the dates column.
DATEADD(<dates>,<number_of_intervals>,<interval>)
number_of_intervals. An integer that specifies the number of intervals to add to or subtract from the dates.
interval. The interval by which to shift the dates. The value for interval can be one of the following: year, quarter, month, day
Returns a table containing a single column of date values.
The following formula calculates dates that are one year before the dates in the current context.
=DATEADD(DateTime[DateKey],-1,year)
Returns a table that contains a column of dates that begins with the start_date and continues until the end_date.
If start_date is a blank date value, then start_date will be the earliest value in the dates column.
If end_date is a blank date value, then end_date will be the latest value in the dates column.
The dates used as the start_date and end_date are inclusive: that is, if the sales occurred on September 1 and you use September 1 as the start date, sales on September 1 are counted.
Note: The DATESBETWEEN function is provided for working with custom date ranges. If you are working with common date intervals such as months, quarters, and years, we recommend that you use the appropriate function, such as DATESINPERIOD.
DATESBETWEEN(<dates>,<start_date>,<end_date>)
dates. A reference to a date/time column.
start_date. A date expression.
end_date. A date expression.
The following sample formula creates a measure that calculates the 'Summer 2003 sales' for the Internet sales.
To see how this works, create a PivotTable and add the field, CalendarYear, to the Row Labels area of the PivotTable. Then add a measure, named Summer 2003 Sales, using the formula as defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESBETWEEN(DateTime[DateKey], DATE(2003,6,1), DATE(2003,8,31) ))
Returns a table that contains a column of dates that begins with the start_date and continues for the specified number_of_intervals.
If the number specified for number_of_intervals is positive, the dates are moved forward in time; if the number is negative, the dates are shifted back in time.
The result table includes only dates that appear in the values of the underlying table column.
DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>)
The following formula returns the Internet sales for the 21 days prior to August 24, 2003.
= CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESINPERIOD(DateTime[DateKey], DATE(2003,08,24),-21,day))
Returns a table that contains a column of the dates for the month to date, in the current context.
DATESMTD(<dates>)
The following sample formula creates a measure that calculates the 'Month To Date Total' for the Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear, MonthNumberOfYear and DayNumberOfMonth, to the Row Labels area of the PivotTable. Then add a measure, named Month To Date Total, using the formula defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESMTD(DateTime[DateKey]))
Returns a table that contains a column of the dates for the quarter to date, in the current context.
DATESQTD(<dates>)
The following sample formula creates a measure that calculates the 'Quarterly Running Total' of the internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear, CalendarQuarter and MonthNumberOfYear to the Row Labels area of the PivotTable. Then add a measure, named Quarterly Running Total, using the formula defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESQTD(DateTime[DateKey]))
Returns a table that contains a column of the dates for the year to date, in the current context.
DATESYTD(<dates> [,<year_end_date>])
The following sample formula creates a measure that calculates the 'Running Total' for the Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear and CalendarQuarter, to the Row Labels area of the PivotTable. Then add a measure named Running Total, using the formula defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), DATESYTD(DateTime[DateKey]))
Returns the last date of the month in the current context for the specified column of dates.
ENDOFMONTH(<dates>)
Returns a table containing a single column and single row with a date value.
The following sample formula creates a measure that returns the end of the month, for the current context.
To see how this works, create a PivotTable and add the fields CalendarYear and MonthNumberOfYear to the Row Labels area of the PivotTable. Then add a measure, named EndOfMonth, using the formula defined in the code section, to the Values area of the PivotTable.
=ENDOFMONTH(DateTime[DateKey])
Returns the last date of the quarter in the current context for the specified column of dates.
ENDOFQUARTER(<dates>)
The following sample formula creates a measure that returns the end of the quarter, for the current context.
To see how this works, create a PivotTable and add the fields CalendarYear and MonthNumberOfYear to the Row Labels area of the PivotTable. Then add a measure, named EndOfQuarter, using the formula defined in the code section, to the Values area of the PivotTable.
=ENDOFQUARTER(DateTime[DateKey])
Returns the last date of the year in the current context for the specified column of dates.
ENDOFYEAR(<dates> [,<year_end_date>])
The following sample formula creates a measure that returns the end of the fiscal year that ends on June 30, for the current context.
To see how this works, create a PivotTable and add the field CalendarYear to the Row Labels area of the PivotTable. Then add a measure, named EndOfFiscalYear, using the formula defined in the code section, to the Values area of the PivotTable.
=ENDOFYEAR(DateTime[DateKey],"06/30/2004"
Returns the first date in the current context for the specified column of dates.
When the current context is a single date, the date returned by the FIRSTDATE and LASTDATE functions will be equal.
Technically, the return value is a table that contains a single column and single value. Therefore, this function can be used as an argument to any function that requires a table in its arguments. Also, the returned value can be used whenever a date value is required.
FIRSTDATE(<dates>)
The following sample formula creates a measure that obtains the first date when a sale was made in the Internet sales channel for the current context.
To see how this works, create a PivotTable and add the field CalendarYear to the Row Labels area of the PivotTable. Then add a measure, named FirstSaleDate, using the formula defined in the code section, to the Values area of the PivotTable.
=FIRSTDATE('InternetSales_USD'[SaleDateKey])
Returns the first value in the column, column, filtered by the current context, where the expression is not blank.
The column argument can be any of the following:
A reference to any column.
A table with a single column.
A Boolean expression that defines a single-column table.
This function is typically used to return the first value of a column for which the expression is not blank. For example, you could get the last value for which there were sales of a product.
FIRSTNONBLANK(<column>,<expression>)
column. A column expression.
expression. An expression evaluated for blanks for each value of column.
Returns a table containing a single column and single row with the computed first value.
Returns the last date in the current context for the specified column of dates.
LASTDATE(<dates>)
The following sample formula creates a measure that obtains the last date, for the current context, when a sale was made in the Internet sales channel.
To see how this works, create a PivotTable and add the field CalendarYear to the Row Labels area of the PivotTable. Then add a measure, named LastSaleDate, using the formula defined in the code section, to the Values area of the PivotTable.
=LASTDATE('InternetSales_USD'[SaleDateKey])
Returns the last value in the column, column, filtered by the current context, where the expression is not blank.
This function is typically used to return the last value of a column for which the expression is not blank. For example, you could get the last value for which there were sales of a product.
LASTNONBLANK(<column>,<expression>)
Returns a table containing a single column and single row with the computed last value.
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.
This function returns all dates from the next day to the first date in the input parameter. For example, if the first date in the dates argument refers to June 10, 2009; then this function returns all dates equal to June 11, 2009.
NEXTDAY(<dates>)
dates. A column containing dates.
The following sample formula creates a measure that calculates the 'next day sales' of the internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear and MonthNumberOfYear, to the Row Labels area of the PivotTable. Then add a measure, named Next Day Sales, using the formula defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), NEXTDAY('DateTime'[DateKey]))
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.
NEXTMONTH(<dates>)
The following sample formula creates a measure that calculates the 'next month sales' for the Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear and MonthNumberOfYear, to the Row Labels area of the PivotTable. Then add a measure, named Next Month Sales, using the formula defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), NEXTMONTH('DateTime'[DateKey]))
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.
This function returns all dates in the next quarter, based on the first date in the input parameter. For example, if the first date in the dates column refers to June 10, 2009, this function returns all dates for the quarter July to September, 2009.
NEXTQUARTER(<dates>)
The following sample formula creates a measure that calculates the 'next quarter sales' for the Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear and CalendarQuarter, to the Row Labels area of the PivotTable. Then add a measure, named Next Quarter Sales, using the formula defined in the code section to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), NEXTQUARTER('DateTime'[DateKey]))
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.
This function returns all dates in the next year, based on the first date in the input column. For example, if the first date in the dates column refers to the year 2007, this function returns all dates for the year 2008.
NEXTYEAR(<dates>[,<year_end_date>])
The following sample formula creates a measure that calculates the 'next year sales' for the Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear and CalendarQuarter, to the Row Labels area of the PivotTable. Then add a measure, named Next Year Sales, using the formula defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), NEXTYEAR('DateTime'[DateKey]))
Evaluates the expression at the first date of the month in the current context.
OPENINGBALANCEMONTH(<expression>,<dates>[,<filter>])
Returns a scalar value that represents the expression evaluated at the first date of the month in the current context.
The following sample formula creates a measure that calculates the 'Month Start Inventory Value' of the product inventory.
To see how this works, create a PivotTable and add the fields, CalendarYear, MonthNumberOfYear and DayNumberOfMonth, to the Row Labels area of the PivotTable. Then add a measure, named Month Start Inventory Value, using the formula defined in the code section, to the Values area of the PivotTable.
=OPENINGBALANCEMONTH(SUMX(ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey])
Evaluates the expression at the first date of the quarter, in the current context.
OPENINGBALANCEQUARTER(<expression>,<dates>[,<filter>])
Returns a scalar value that represents the expression evaluated at the first date of the quarter in the current context.
The following sample formula creates a measure that calculates the 'Quarter Start Inventory Value' of the product inventory.
To see how this works, create a PivotTable and add the fields, CalendarYear, CalendarQuarter and MonthNumberOfYear, to the Row Labels area of the PivotTable. Then add a measure, named Quarter Start Inventory Value, using the formula defined in the code section, to the Values area of the PivotTable.
=OPENINGBALANCEQUARTER(SUMX(ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey])
Evaluates the expression at the first date of the year in the current context.
OPENINGBALANCEYEAR(<expression>,<dates>[,<filter>][,<year_end_date>])
Returns a scalar value that represents the expression evaluated at the first date of the year in the current context.
The following sample formula creates a measure that calculates the 'Year Start Inventory Value' of the product inventory.
To see how this works, create a PivotTable and add the field, CalendarYear, to the Row Labels area of the PivotTable. Then add a measure, named Year Start Inventory Value, using the formula defined in the code section, to the Values area of the PivotTable.
=OPENINGBALANCEYEAR(SUMX(ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey])
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.
This function takes the current set of dates in the column specified by dates, shifts the first date and the last date the specified number of intervals, and then returns all contiguous dates between the two shifted dates. If the interval is a partial range of month, quarter, or year then any partial months in the result are also filled out to complete the entire interval.
The interval parameter is an enumeration, not a set of strings; therefore values should not be enclosed in quotation marks. Also, the values: year, quarter, month should be spelled in full when using them.
The PARALLELPERIOD function is similar to the DATEADD function except that PARALLELPERIOD always returns full periods at the given granularity level instead of the partial periods that DATEADD returns. For example, if you have a selection of dates that starts at June 10 and finishes at June 21 of the same year, and you want to shift that selection forward by one month then the PARALLELPERIOD function will return all dates from the next month (July 1 to July 31); however, if DATEADD is used instead, then the result will include only dates from July 10 to July 21.
If the dates in the current context do not form a contiguous interval, the function returns an error.
PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)
interval. The interval by which to shift the dates. The value for interval can be one of the following: year, quarter, month.
The following sample formula creates a measure that calculates the previous year sales for Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear and CalendarQuarter, to the Row Labels area of the PivotTable. Then add a measure, named Previous Year Sales, using the formula defined in the code section, to the Values area of the PivotTable.
Note: The above example uses the table DateTime from the DAX sample workbook. For more information about samples, see Get Sample Data for PowerPivot in the TechNet Library.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PARALLELPERIOD(DateTime[DateKey],-1,year))
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.
This function determines the first date in the input parameter, and then returns all dates corresponding to the day previous to that first date. For example, if the first date in the dates argument refers to June 10, 2009; this function returns all dates equal to June 9, 2009.
PREVIOUSDAY(<dates>)
The following sample formula creates a measure that calculates the 'previous day sales' for the Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear and MonthNumberOfYear, to the Row Labels area of the PivotTable. Then add a measure, named Previous Day Sales, using the formula defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSDAY('DateTime'[DateKey]))
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.
This function returns all dates from the previous month, using the first date in the column used as input. For example, if the first date in the dates argument refers to June 10, 2009, this function returns all dates for the month of May, 2009.
PREVIOUSMONTH(<dates>)
The following sample formula creates a measure that calculates the 'previous month sales' for the Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear and MonthNumberOfYear, to the Row Labels area of the PivotTable. Then add a measure, named Previous Month Sales, using the formula defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSMONTH('DateTime'[DateKey]))
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.
This function returns all dates from the previous quarter, using the first date in the input column. For example, if the first date in the dates argument refers to June 10, 2009, this function returns all dates for the quarter January to March, 2009.
PREVIOUSQUARTER(<dates>)
The following sample formula creates a measure that calculates the 'previous quarter sales' for Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear and CalendarQuarter, to the Row Labels area of the PivotTable. Then add a measure, named Previous Quarter Sales, using the formula defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSQUARTER('DateTime'[DateKey]))
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.
This function returns all dates from the previous year given the latest date in the input parameter. For example, if the latest date in the dates argument refers to the year 2009, then this function returns all dates for the year of 2008, up to the specified year_end_date.
PREVIOUSYEAR(<dates>[,<year_end_date>])
The following sample formula creates a measure that calculates the previous year sales for the Internet sales.
=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSYEAR('DateTime'[DateKey]))
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.
The dates returned are the same as the dates returned by this equivalent formula:
DATEADD(dates, -1, year)
SAMEPERIODLASTYEAR(<dates>)
Returns a single-column table of date values.
The following sample formula creates a measure that calculates the previous year sales of the Reseller sales.
To see how this works, create a PivotTable and add the fields, CalendarYear to the Row Labels area of the PivotTable. Then add a measure, named Previous Year Sales, using the formula defined in the code section, to the Values area of the PivotTable.
=CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime[DateKey]))
Returns the first date of the month in the current context for the specified column of dates.
STARTOFMONTH(<dates>)
The following sample formula creates a measure that returns the start of the month, for the current context.
To see how this works, create a PivotTable and add the fields CalendarYear and MonthNumberOfYear to the Row Labels area of the PivotTable. Then add a measure, named StartOfMonth, using the formula defined in the code section, to the Values area of the PivotTable.
=STARTOFMONTH(DateTime[DateKey])
Returns the first date of the quarter in the current context for the specified column of dates.
STARTOFQUARTER(<dates>)
The following sample formula creates a measure that returns the start of the quarter, for the current context.
To see how this works, create a PivotTable and add the fields CalendarYear and MonthNumberOfYear to the Row Labels area of the PivotTable. Then add a measure, named StartOfQuarter, using the formula defined in the code section, to the Values area of the PivotTable.
=STARTOFQUARTER(DateTime[DateKey])
Returns the first date of the year in the current context for the specified column of dates.
STARTOFYEAR(<dates> [,<year_end_date>])
The following sample formula creates a measure that returns the start of the fiscal year that ends on June 30, for the current context.
To see how this works, create a PivotTable and add the field CalendarYear to the Row Labels area of the PivotTable. Then add a measure, named StartOfFiscalYear, using the formula defined in the code section, to the Values area of the PivotTable.
=STARTOFYEAR(DateTime[DateKey],"06/30/2004")
Evaluates the value of the expression for the month to date, in the current context.
TOTALMTD(<expression>,<dates>[,<filter>])
Returns a scalar value that represents the expression evaluated for the dates in the current month-to-date, given the dates in dates.
The following sample formula creates a measure that calculates the 'month running total' or 'month running sum' for the Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear, MonthNumberOfYear and DayNumberOfMonth, to the Row Labels area of the PivotTable. Then add a measure, named Month-to-date Total, using the formula defined in the code section, to the Values area of the PivotTable.
=TOTALMTD(SUM(InternetSales_USD[SalesAmount_USD]), DateTime[DateKey])
Evaluates the value of the expression for the dates in the quarter to date, in the current context.
TOTALQTD(<expression>,<dates>[,<filter>])
Returns a scalar value that represents the expression evaluated for the dates in the current quarter to date, given the dates in dates.
The following sample formula creates a measure that calculates the 'quarter running total' or 'quarter running sum' for the Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear, CalendarQuarter and MonthNumberOfYear, to the Row Labels area of the PivotTable. Then add a measure, named Quarter-to-date Total, using the formula defined in the code section, to the Values area of the PivotTable.
=TOTALQTD(SUM(InternetSales_USD[SalesAmount_USD]),DateTime[DateKey])
Evaluates the year-to-date value of the expression in the current context.
TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])
Returns a scalar value that represents the expression evaluated for the current year-to-date dates.
The following sample formula creates a measure that calculates the 'year running total' or 'year running sum' for the Internet sales.
To see how this works, create a PivotTable and add the fields, CalendarYear, CalendarQuarter, and MonthNumberOfYear, to the Row Labels area of the PivotTable. Then add a measure, named Year-to-date Total, using the formula defined in the code section, to the Values area of the PivotTable.
=TOTALYTD(SUM(InternetSales_USD[SalesAmount_USD]),DateTime[DateKey])