Calculating Year-over-Year (YoY) Growth Using SUMX + FILTER in the New Power BI Visual Calculations

When building production dashboards with Power BI, one of the most frequently implemented core metrics is Year-over-Year (YoY) growth.

Traditionally, achieving this required combining complex DAX functions such as CALCULATE, SAMEPERIODLASTYEAR, and ALL. In particular, if the data model's relationships or Date Table configurations were not absolutely flawless, incorrect values would often surface—causing significant headaches for beginners and intermediate users alike.

However, the introduction of the new 'Visual Calculation' feature in Power BI has completely changed the game. Instead of navigating through the entirety of a complex data model, users can now intuitively perform calculations by treating the visualization table (the visual) currently displayed on the screen as its own independent database.

In this post, without relying on intricate time intelligence functions, we will dissect the step-by-step Year-over-Year (YoY) growth formula used in production using only the new Visual Calculations and the SUMX function.

1. Example Data

f_Sales Table

A basic table exists, composed of Year, Month, and Sales as shown below.

Next, we create a single measure:

Total Sales = SUM(‘f_Sales’ [Sales])

Following this, a Matrix visualization is generated as shown below.

2. Creating a New Visual Calculation

With the visual selected, enter edit mode by clicking [New Visual Calculation] from the top ribbon menu.

[Link] – Creating a New Visual Calculation (To be updated later)

2. Year-over-Year (YoY) Visual Calculation Code

Once the expression edit window becomes active, input the code exactly as designed. Instead of filtering the entire data model, this code navigates through the rows inside the current visual to locate the prior year's data.

3. Thorough Analysis of Core Code (Operation Mechanism)

Let us break down how the written code executes internally into 4 distinct phases. Understanding this mechanism allows you to easily adapt it to other metrics.

1) Storing the Reference Prior Year and Current Month

VAR PY = [Year] - 1
VAR CM = [Month]

Based on the year and month of the row currently being evaluated, this establishes the coordinates to locate the identical month of the previous year. For example, if the current row is as follows:

Year	Month
2023	Oct

The variables will store the following values:

• PY = 2022
• CM = Oct

In short, subsequent evaluations will search for "2022 October data."

2) Using ROWS to Find Matching Prior Year Data Within the 'Entire Visual'

FILTER( ROWS, [Year] = PY && [Month] = CM )

• ROWS, when utilized within a Visual Calculation, represents the exact dataset displayed in the current Matrix or Table visualization.
• This means that instead of scanning the entire underlying data model, the exploration is targeted strictly at the result set (Visual Shape) generated by the visualization engine.
• In traditional measures, you had to control the filter context using ALL(), REMOVEFILTERS(), or CALCULATE. In contrast, Visual Calculations allow you to compute directly against the data displayed on the screen. This aspect represents the most significant characteristic of Visual Calculations.

3) Extracting the Prior Year Sales (SalesPY) with SUMX

SUMX(
   FILTER(
       ROWS,
       [Year] = PY &&
       [Month] = CM
   ),
   [Total Sales]
)

Many users frequently ask at this stage: "Why use SUMX when we are only looking for a single row of prior year data?"

The reason is that the output returned by the FILTER function is always a Table. Within a Visual Calculation, an iterator function (X-Function) such as SUMX, MAXX, or MINX is absolutely mandatory to read a scalar value out of a filtered table result. Therefore, SUMX is deployed here not for the primary purpose of calculating an aggregate sum, but as a mechanism to extract a value from the filtered row.

For instance, if the current row is:

Year	Month	Sales
2023	Oct	78,751

Then FILTER will locate the following row:

Year	Month	Sales
2022	Oct	36,769

Subsequently, SUMX reads the Total Sales value from that specific row and stores it securely inside the SalesPY variable.

4) Safe Division and Formatting

DIVIDE(Sales - SalesPY, SalesPY)

• Mathematically, this mirrors the standard formula: (Current Sales - Prior Year Sales) ÷ Prior Year Sales.
• In your dataset, certain rows—such as the earliest data from 2021—may lack prior year (2020) sales. Dividing via a standard forward slash (/) in these instances leads to division-by-zero errors (NaN or Infinity). The DIVIDE function automatically intercepts these edge cases and substitutes them with a blank (BLANK), making it an indispensable tool for production environments.

5) Displaying Results Only When Prior Year Data Exists

IF(
   NOT ISBLANK(SalesPY),
   Result
)

It is critical to evaluate this condition based on the prior year sales (SalesPY) rather than current sales (Sales). If prior year data does not exist, a growth rate cannot be mathematically computed. Consequently, the first year of data is naturally displayed as a blank.

4. Verifying the Results

Once the formula entry is complete, the Growth column is appended while seamlessly preserving the existing Matrix structure. The Year-over-Year growth rate for each month is calculated automatically, with the visual calculation results reflecting in real time. The greatest advantage of Visual Calculations is the ability to intuitively implement YoY analysis without dense CALCULATE or Time Intelligence setups.

5. Production Tips and Conclusion

This pattern is not restricted to revenue growth alone; it can be deployed immediately across core enterprise dashboards to calculate key performance indicators (KPIs) by simply copying the logic and modifying the column references:

• Customer Analytics: Growth rate of new visitors and sign-ups compared to the same month of the previous year.
• Logistics/Distribution: Variations and shifts in order volume and return rates relative to the prior year.
• Marketing: Year-over-Year growth analysis of Return on Ad Spend (ROAS) by category or geographic region.

Initially, because this deviates from the traditional CALCULATE mental model of DAX, it may feel unfamiliar. However, if you anchor your perspective on the fact that "the matrix table rendered on the visual screen is its own independent database (ROWS)," you will find solving complex internal visualization calculations to be far easier and significantly more intuitive moving forward.

Wrapping up

Visual Calculations represent a highly innovative feature because they step away from the traditional DAX philosophy centered on filter context, turning the current screen output into the direct target of evaluation.

In particular, grasping the synergy between ROWS and SUMX unlocks the potential to build a diverse array of analytical metrics beyond Year-over-Year growth, including Month-over-Month (MoM), Share of Total, and Running Totals.

When deploying Visual Calculations in the future, you only need to remember this single principle:

"ROWS is the core object that enables you to handle the visual currently displayed on the screen exactly like a database."

Mastering this single viewpoint allows you to deliver highly intuitive analytics without the baggage of complex DAX.

<Other posts on the blog>

• • What Should You Put Where the Table Belongs in the New Visual Calculations? – A Complete Understanding of the ROWS() Function
• • How to Automatically Change Units Based on KPIs (ISINSCOPE + Dynamic Format String)
• • Why Do Pros Choose REMOVEFILTERS Over ALL?
• • Perfect Implementation of Dynamic Line Chart Highlighting (Slicers + Error Bars)