How to Use the Power BI INDEX Function | Calculating YoY with Visual Calculations

In our previous post, we explored how to calculate Year-over-Year (YoY) growth using the OFFSET function, and how to combine it with PARTITIONBY to prevent reference errors when monthly data is missing.

This time, we will examine INDEX, another core function available within Visual Calculations. While OFFSET is a function that navigates to a relative row based on the current position, INDEX is a function that directly references a row at a specified sequence number. This is highly useful for analyses that require comparing against a fixed baseline data point or finding starting and ending points. In this article, we will unpack the operational mechanics of the INDEX function alongside a YoY calculation example, and summarize the critical production-grade limitations that you must understand in practice.

1. INDEX Function Overview

INDEX is a function that returns a row located at a specified 'absolute position (Index)' within a visual object (such as a matrix or a table).

INDEX(1): Refers to the data in the first row (the absolute starting point).
INDEX(12): Refers to the data in the row positioned exactly 12th.
INDEX(-1): Refers to the data in the absolute final (end) row. (Using a negative number counts the sequence backward from the bottom.)

In other words, rather than shifting a certain number of slots like OFFSET, it directly designates "which row number" to target.

2. Base-Form INDEX-Based YoY Calculation

Let us again assume an ideal architecture where the 12-month data for all years is completely and sequentially populated within the matrix without any omissions.

In this scenario, you can retrieve the same month of the previous year by obtaining the current row's sequence number and then referencing the position 12 rows prior.

input growth index colum in the power bi table

Code Explanation

ROWNUMBER(): Returns the sequence number of the current row.
CurrentRow - 12: Designates exactly 12 rows prior relative to the current position.
INDEX(): Returns the row at that specific position.
SUMX(): Retrieves the [Total Sales] from the returned row.

For example, if the current row is the 23rd row, INDEX(11) will be executed, thereby referencing the 11th row (the same month of the previous year).

3. Advanced Production-Grade INDEX YoY Calculation

1) Limitations in Actual Data Structures (The 2021 Data Omission Scenario)

The aforementioned method operates normally only when the data is flawlessly continuous. For instance, if certain monthly data is missing in the year 2021, the row index numbers assigned by ROWNUMBER() themselves will shift.

As a result:

The desired month might not even exist 12 rows prior.
A critical problem can occur where an entirely different month is referenced instead.

In short, ROWNUMBER is not a function that inherently understands dates; it merely calculates the sequence number of the rows currently displayed on the screen. Consequently, if data is missing or if the sorting criteria change, the intended results cannot be guaranteed.

2) Absolute Position Control Code via PARTITIONBY

The solution is to combine PARTITIONBY([Month]). When you divide the territories this way, the meaning of INDEX(1) changes fundamentally. This is because it no longer means the first row of the entire matrix, but rather "the first row inside the chamber of that specific month (Month)."

Code Explanation

Using PARTITIONBY([Month]) divides the data into independent groups based on each month.

For example:

January with January
February with February
March with March

Separate groups are created accordingly. Here, the syntax returns the data of the oldest year within each monthly group.

INDEX(
    1,
    ORDERBY([Year]),
    PARTITIONBY([Month])
)

For instance, if the layout is:

Year: 2021, Month: Jan
Year: 2022, Month: Jan
Year: 2023, Month: Jan

It will always return January 2021.

Caution:
Therefore, the INDEX_Safe mechanism works flawlessly specifically when the table contains exactly two calendar years (e.g., 2021, 2022), or when you intend to calculate and fix the growth rate against a specific baseline year (Base-Year).

4. Differences Between OFFSET and INDEX

Although they appear similar on the surface, their analytical purposes are distinct.

OFFSET

Preceding row relative to the current position
Subsequent row relative to the current position
Prior-year data
Previous quarter

It is optimally suited for locating relative positions like the examples above.

Conversely, INDEX is exceptionally powerful when referencing fixed positions, such as:

The first data point
The final data point
A specific sequence number
The baseline year (Base Year)

It is easier to understand if you remember that INDEX(1) in the above example represents a method for calculating the growth rate relative to a baseline year (Base Year), rather than a standard rolling YoY calculation.

5. When is it Best to Use INDEX?

INDEX is far more useful than a standard YoY calculation in analytical use cases such as:

Finding the first sales date
Finding the last sales date
Pinpointing the initial customer purchase timeline
Benchmarking against the peak performance record
Conducting Base Year comparisons
Referencing data at a specific sequence number

In short, it demonstrates its greatest strength in analyses where a fixed baseline reference point is required.

6. Critical Verification Steps for Production

The OFFSET, INDEX, and WINDOW functions within Visual Calculations are all fundamentally not date-calculating time-intelligence functions. The targets that these functions reference are strictly the row positions displayed in the current visual object.

Therefore, the following conditions are highly critical:

There must be no data omissions.
The sorting criteria must always remain identical.
The time-series data must be sequentially structured.

In environments where these conditions are not fully guaranteed, unexpected results can easily occur. If you are dealing with complex production-grade data, utilizing the FILTER + ROWS + SUMX pattern or leveraging traditional DAX modeling with a dedicated Date table remains a much more stable choice.

Wrapping up

INDEX is not a function that "shifts a certain number of rows" like OFFSET; rather, it directly references the row at a designated position.

Consequently, while OFFSET is more natural for standard Year-over-Year growth calculations, INDEX is far more intuitive and powerful for analyses requiring fixed benchmarks—such as baseline year (Base Year) comparisons, start-and-end point analyses, or referencing a specific sequence number. When utilizing Visual Calculations, the most important factor is understanding the distinction between INDEX and OFFSET and selecting the right tool for your specific analytical objective.

Search This Blog

IGLOO BI