Share via

Power BI: DAX: Time Intelligence Functions

  • Article

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.

CLOSINGBALANCEMONTH

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.

Syntax

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.

Example

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

CLOSINGBALANCEQUARTER

Evaluates the expression at the last date of the quarter 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.

Syntax

CLOSINGBALANCEQUARTER(<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 quarter in the current context.

Example

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])

↑ Back to top

CLOSINGBALANCEYEAR

Evaluates the expression at the last date of the year 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.

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.

Syntax

CLOSINGBALANCEYEAR(<expression>,<dates>[,<filter>][,<year_end_date>])

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.

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.

Example

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])

↑ Back to top

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.

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.

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.

Syntax

DATEADD(<dates>,<number_of_intervals>,<interval>)

dates. A column that contains dates.

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.

Example: Shifting a Set of Dates

The following formula calculates dates that are one year before the dates in the current context.

=DATEADD(DateTime[DateKey],-1,year)

↑ Back to top

DATESBETWEEN

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.

Syntax

DATESBETWEEN(<dates>,<start_date>,<end_date>)

dates. A reference to a date/time column.

start_date. A date expression.

end_date. A date expression.

Returns a table containing a single column of date values. 

Example

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)
  ))

↑ Back to top

DATESINPERIOD

Returns a table that contains a column of dates that begins with the start_date and continues for the specified number_of_intervals.

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.

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 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 appear in the values of the underlying table column.

Syntax

DATESINPERIOD(<dates>,<start_date>,<number_of_intervals>,<interval>)

dates. A column that contains dates.

start_date. A date expression.

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.

Example

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))

↑ Back to top

DATESMTD

Returns a table that contains a column of the dates for the month to date, 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.

Syntax

DATESMTD(<dates>)

dates. A column that contains dates.

Returns a table containing a single column of date values.

Example

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]))

↑ Back to top

DATESQTD

Returns a table that contains a column of the dates for the quarter to date, 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.

Syntax

DATESQTD(<dates>)

dates. A column that contains dates.

Returns a table containing a single column of date values.

Example

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]))

↑ Back to top

DATESYTD

Returns a table that contains a column of the dates for the year to date, 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.

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.

Syntax

DATESYTD(<dates> [,<year_end_date>])

dates. A column that contains dates.

year_end_date. (optional) A literal string with a date that defines the year-end date. The default is December 31.

Returns a table containing a single column of date values.

Example

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]))

↑ Back to top

ENDOFMONTH

Returns the last date of the month in the current context for the specified column of dates.

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.

Syntax

ENDOFMONTH(<dates>)

dates. A column that contains dates.

Returns a table containing a single column and single row with a date value.

Example

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])

↑ Back to top

ENDOFQUARTER

Returns the last date of the quarter in the current context for the specified column of dates.

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.

Syntax

ENDOFQUARTER(<dates>)

dates. A column that contains dates.

Returns a table containing a single column and single row with a date value.

Example

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])

↑ Back to top

ENDOFYEAR

Returns the last date of the year in the current context for the specified column of dates.

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.

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.

Syntax

ENDOFYEAR(<dates> [,<year_end_date>])

dates. A column that contains dates.

year_end_date. (optional) A literal string with a date that defines the year-end date. The default is December 31.

Returns a table containing a single column and single row with a date value.

Example

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"

↑ Back to top

FIRSTDATE

Returns the first date in the current context for the specified column of dates.

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.

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.

Syntax

FIRSTDATE(<dates>)

dates. A column that contains dates.

Returns a table containing a single column and single row with a date value.

Example

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])

↑ Back to top

FIRSTNONBLANK

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.

Note: Constraints on Boolean expressions are described under CALCULATE.

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.

Note: To understand more about how context affects the results of formulas, see Context in DAX.

Syntax

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.

↑ Back to top

LASTDATE

Returns the last date in the current context for the specified column of dates.

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.

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.

Syntax

LASTDATE(<dates>)

dates. A column that contains dates.

Returns a table containing a single column and single row with a date value.

Example

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])

↑ Back to top

LASTNONBLANK

Returns the last 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.

Note: Constraints on Boolean expressions are described under CALCULATE.

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.

Note: To understand more about how context affects the results of formulas, see Context in DAX.

Syntax

LASTNONBLANK(<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 last value.

↑ Back to top

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.

Note: To understand more about how context affects the results of formulas, see Context in DAX.

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.

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.

Syntax

NEXTDAY(<dates>) 

 dates. A column containing dates.

 Returns a table containing a single column of date values.

Example

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]))

↑ Back to top

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.

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.

Syntax

NEXTMONTH(<dates>) 

 dates. A column containing dates.

 Returns a table containing a single column of date values.

Example

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]))

↑ Back to top

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.

Note: To understand more about how context affects the results of formulas, see Context in DAX.

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.

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. 

Syntax

NEXTQUARTER(<dates>) 

 dates. A column containing dates.

 Returns a table containing a single column of date values.

Example

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]))

↑ Back to top

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.

Note: To understand more about how context affects the results of formulas, see Context in DAX.

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.

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.

 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.

Syntax

NEXTYEAR(<dates>[,<year_end_date>])

dates. A column containing dates.

year_end_date. (optional) A literal string with a date that defines the year-end date. The default is December 31.

 Returns a table containing a single column of date values.

Example

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]))

↑ Back to top

OPENINGBALANCEMONTH

 Evaluates the expression at the first 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. 

Syntax

OPENINGBALANCEMONTH(<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 first date of the month in the current context.

Example

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])

↑ Back to top

OPENINGBALANCEQUARTER

 Evaluates the expression at the first date of the quarter, 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. 

Syntax

OPENINGBALANCEQUARTER(<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 first date of the quarter in the current context.

Example

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])

↑ Back to top

OPENINGBALANCEYEAR

 Evaluates the expression at the first date of the year 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.

 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.

Syntax

OPENINGBALANCEYEAR(<expression>,<dates>[,<filter>][,<year_end_date>])

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.

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 first date of the year in the current context.

Example

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])

↑ Back to top

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.

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.

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. 

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 should be spelled in full when using them.

The result table includes only dates that appear in the values of the underlying table column.

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.

Syntax

PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)

dates. A column that contains dates.

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.

Returns a table containing a single column of date values.

Example

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))

↑ Back to top

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.

Note: To understand more about how context affects the results of formulas, see Context in DAX.

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.

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. 

Syntax

PREVIOUSDAY(<dates>)

dates. A column containing dates.

Returns a table containing a single column of date values.

Example

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]))

↑ Back to top

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.

Note: To understand more about how context affects the results of formulas, see Context in DAX.

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.

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. 

Syntax

PREVIOUSMONTH(<dates>)

dates. A column containing dates.

Returns a table containing a single column of date values.

Example

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]))

↑ Back to top

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.

Note: To understand more about how context affects the results of formulas, see Context in DAX.

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.

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. 

Syntax

PREVIOUSQUARTER(<dates>)

dates. A column containing dates.

Returns a table containing a single column of date values.

Example

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]))

↑ Back to top

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.

Note: To understand more about how context affects the results of formulas, see Context in DAX.

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.

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.

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. 

Syntax

PREVIOUSYEAR(<dates>[,<year_end_date>])

dates. A column containing dates.

year_end_date. (optional) A literal string with a date that defines the year-end date. The default is December 31.

Returns a table containing a single column of date values.

Example

The following sample formula creates a measure that calculates the previous 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 Previous Year Sales, using the formula defined in the code section, to the Values area of the PivotTable.

=CALCULATE(SUM(InternetSales_USD[SalesAmount_USD]), PREVIOUSYEAR('DateTime'[DateKey]))

↑ Back to top

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.

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. 

The dates returned are the same as the dates returned by this equivalent formula:

DATEADD(dates, -1, year)

Syntax

SAMEPERIODLASTYEAR(<dates>)

dates. A column containing dates.

Returns a single-column table of date values.

Example

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]))

↑ Back to top

STARTOFMONTH

 Returns the first date of the month in the current context for the specified column of dates.

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. 

Syntax

STARTOFMONTH(<dates>)

dates. A column containing dates.

Returns a table containing a single column and single row with a date value.

Example

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])

↑ Back to top

STARTOFQUARTER

 Returns the first date of the quarter in the current context for the specified column of dates.

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. 

Syntax

STARTOFQUARTER(<dates>)

dates. A column containing dates.

Returns a table containing a single column and single row with a date value.

Example

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])

↑ Back to top

STARTOFYEAR

 Returns the first date of the year in the current context for the specified column of dates.

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.

 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.

Syntax

STARTOFYEAR(<dates> [,<year_end_date>])

dates. A column that contains dates.

year_end_date. (optional) A literal string with a date that defines the year-end date. The default is December 31.

Returns a table containing a single column and single row with a date value.

Example

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")

↑ Back to top

TOTALMTD

 Evaluates the value of the expression for the month to date, 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. 

Syntax

TOTALMTD(<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 for the dates in the current month-to-date, given the dates in dates.

Example

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])

↑ Back to top

TOTALQTD

 Evaluates the value of the expression for the dates in the quarter to date, 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. 

Syntax

TOTALQTD(<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 for the dates in the current quarter to date, given the dates in dates.

Example

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])

↑ Back to top

TOTALYTD

 Evaluates the year-to-date value of the expression 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.

 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.

Syntax

TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

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.

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 for the current year-to-date dates.

Example

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])

↑ Back to top

See Also