NEXT
In a visual calculation, a column's value at the next row along an axis, or a number of rows on.
A visual calculation works on the table behind a visual: a row for each group on the visual's axes and one for each of their totals. To try one in the OAX view, give a table that shape with DEFINE TABLE ... WITH VISUAL SHAPE: its axes, their groups, the TRUE/FALSE column that marks each group's totals and the order, and with DENSIFY a name for the column that marks rows the shape fills in. Then write the calculation as a COLUMN of that table, as Power BI's own queries do. The examples below are queries of that kind, small enough to check by hand.
Group: Window and visual calculations. Compatible with DAX.
Syntax
NEXT ( <column> [, <steps>] [, <axis>] [, <orderBy>] [, <blanks>] [, <reset>] )| Parameter | Type | Description |
|---|---|---|
column | Column | A field of the visual, such as [Liters]. An expression is not allowed here: make it a visual calculation of its own and name that. |
steps | Whole number optional | How many rows on; 1 when left out. It must be 1 or more. |
axis | Axis optional | Which way to go: ROWS or COLUMNS, or both, as ROWS COLUMNS, which goes down the rows first and then across the columns; ROWPAGES for small multiples. Left out, the visual's first axis. |
orderBy | ORDERBY optional | ORDERBY ( ... ) to take the rows in another order than the axis has. |
blanks | Keyword optional | With an ORDERBY: where blank values go, DEFAULT, FIRST or LAST. |
reset | Reset optional | Where it starts again: NONE (when left out) never; HIGHESTPARENT (or 1, or the axis's top column) at each value of the top group; LOWESTPARENT (or -1) at each row's own parent; another whole number, or a column of the axis, at that level. |
Returns
Same type as the column. The column's value that many rows after the current one.
Remarks
- On the last rows, where there is nothing that far on, the result is BLANK; so it is at a reset.
- Each level of the visual is walked on its own: the quarters' rows among the quarters, the years' subtotals among the years, the grand total by itself.
- It is the visual calculation's counterpart of
OFFSET ( 1 )along an axis. - It works only in a visual calculation; in a measure, a calculated column or a plain query it is an error.
Examples
The next month, and two months on
DEFINE
TABLE Visual =
DATATABLE (
"Month", INTEGER, "Liters", INTEGER,
{ { 1, 100 }, { 2, 150 }, { 3, 120 }, { 4, 90 } }
)
WITH VISUAL SHAPE
AXIS ROWS GROUP [Month] ORDER BY [Month]
DENSIFY "Added"
COLUMN Visual[Next] = NEXT ( [Liters] )
COLUMN Visual[Two on] = NEXT ( [Liters], 2 )
COLUMN Visual[To the next] = NEXT ( [Liters] ) - [Liters]
EVALUATE
SELECTCOLUMNS (
Visual,
"Month", [Month], "Liters", [Liters],
"Next", [Next],
"Two on", [Two on],
"To the next", [To the next]
)
ORDER BY [Month]| Month | Liters | Next | Two on | To the next |
|---|---|---|---|---|
| 1 | 100 | 150 | 120 | 50 |
| 2 | 150 | 120 | 90 | -30 |
| 3 | 120 | 90 | (blank) | -30 |
| 4 | 90 | (blank) | (blank) | -90 |
The next quarter within the year
Without a reset the last quarter of 2025 looks ahead into 2026; with HIGHESTPARENT it does not.
DEFINE
TABLE Visual =
DATATABLE (
"Year", INTEGER, "Quarter", INTEGER, "Liters", INTEGER, "All years", BOOLEAN, "All quarters", BOOLEAN,
{
{ 2025, 1, 100, FALSE, FALSE }, { 2025, 2, 120, FALSE, FALSE }, { 2025, BLANK (), 220, FALSE, TRUE },
{ 2026, 1, 130, FALSE, FALSE }, { 2026, 2, 150, FALSE, FALSE }, { 2026, BLANK (), 280, FALSE, TRUE },
{ BLANK (), BLANK (), 500, TRUE, TRUE }
}
)
WITH VISUAL SHAPE
AXIS ROWS
GROUP [Year] TOTAL [All years]
GROUP [Quarter] TOTAL [All quarters]
ORDER BY [Year], [Quarter]
DENSIFY "Added"
COLUMN Visual[Next] = NEXT ( [Liters] )
COLUMN Visual[Next in the year] = NEXT ( [Liters], HIGHESTPARENT )
EVALUATE
SELECTCOLUMNS (
Visual,
"Year", [Year], "Quarter", [Quarter], "Liters", [Liters],
"Next", [Next],
"Next in the year", [Next in the year]
)
ORDER BY ISBLANK ( [Year] ), [Year], ISBLANK ( [Quarter] ), [Quarter]| Year | Quarter | Liters | Next | Next in the year |
|---|---|---|---|---|
| 2025 | 1 | 100 | 120 | 120 |
| 2025 | 2 | 120 | 130 | (blank) |
| 2025 | (blank) | 220 | 280 | (blank) |
| 2026 | 1 | 130 | 150 | 150 |
| 2026 | 2 | 150 | (blank) | (blank) |
| 2026 | (blank) | 280 | (blank) | (blank) |
| (blank) | (blank) | 500 | (blank) | (blank) |