Downloads
DAXRanking and windowsAdvanced

INDEX

Returns the row at a given position in an ordered list. Position 1 is the first row, and a negative number counts from the end.

Syntax
INDEX(<position>, [<relation>], [<orderBy>], [<blanks>])

Returns: A table holding that row. Used inside CALCULATE or another function that takes a table.

What it does

INDEX picks one row from an ordered list by its position. Position 1 is the first row, which is useful for the first period in a selection. A negative position counts from the end, so -1 is the last row, which is useful for the latest date with data. Use it when a figure must come from a fixed place in the sorted list rather than from the current row. Check that your version supports it.

Syntax and arguments

Example

Sales on the latest day in the current selection, shown as a card.

Latest Day Sales =
CALCULATE (
    [Total Sales],
    INDEX (
        -1,
        ALLSELECTED ( 'Date'[Date] ),
        ORDERBY ( 'Date'[Date] )
    )
)

The list of days in the selection is sorted by date, the last day is picked, and its sales are returned. The card then shows the most recent day with the selected filters.

Example: a second use

Sales on the first day in the selection, to compare the opening day with the close.

First Day Sales =
CALCULATE (
    [Total Sales],
    INDEX (
        1,
        ALLSELECTED ( 'Date'[Date] ),
        ORDERBY ( 'Date'[Date] )
    )
)

The same pattern with 1 in place of -1 picks the earliest day. Put both figures on one page to show the change across the selection.

Things to watch

In business terms

Opening and closing balances for a selected period, the latest posting date in a ledger extract, and the first and last values of a series in a close review.

Related functions

All DAX functions · View as Markdown · DAX fundamentals