RANGE

Article
04/25/2024

Applies to: Calculated column Calculated table Measure Visual calculation

Returns an interval of rows within the given axis, relative to the current row. This interval will be comprised of either the last <step> rows before the current one, or the first <step> rows after the current one.

Syntax

RANGE ( <step>[, <includeCurrent>][, <axis>][, <blanks>][, <reset>] )

Parameters

Term	Definition
step	The number of rows before (negative value) or after (positive value) the current row to include in the range. Must be a constant value. - If negative, the window will contain the last -step rows before the current row. - Otherwise, the window will contain the first step rows after the current row.
includeCurrent	(Optional) A logical value specifying whether or not to include the current row in the range. Default value is True.
axis	(Optional) An axis reference, the direction along which the interval will be created.
blanks	(Optional) An enumeration that defines how to handle blank values when sorting. This parameter is reserved for future use.
reset	(Optional) Indicates if the calculation resets, and at which level of the visual shape's column hierarchy. Accepted values are: NONE, LOWESTPARENT, HIGHESTPARENT, or an integer. The behavior depends on the integer sign: - If zero or omitted, the calculation does not reset. Equivalent to NONE. - If positive, the integer identifies the column starting from the highest, independent of grain. HIGHESTPARENT is equivalent to 1. - If negative, the integer identifies the column starting from the lowest, relative to the current grain. LOWESTPARENT is equivalent to -1.

Return value

An interval of data rows.

Remarks

This function can be used in visual calculations only.

The <includeCurrent>, <axis>, <blanks> and <reset> parameters can be omitted.

Example 1

Given a table that summarizes the total sales for each product category and month, the following DAX query adds a column with the total sales in the last 6 months:

TotalSalesLast6Months = CALCULATE(SUM([SalesAmount]), RANGE(-5, Rows))

The screenshot below shows the visual matrix and the visual calculation expression:

DAX visual calculation

Example 2

Given the same table, the following DAX query adds a column with the total sales in the following 12 months, not including the current month:

TotalSalesFollowingYear = CALCULATE(SUM([SalesAmount]), RANGE(12, FALSE, Rows, KEEP))