How do you calculate percent change in Power BI?

---

The percent change in values between one period and another period is calculated as:

Percent change = (Value₂ – Value₁) / Value₁ * 100

For example, suppose a company makes 85 sales one month, then makes 94 sales the next month.

We can use the following formula to calculate the percent change in sales from one month to the next:

• Percent change = (Value₂ – Value₁) / Value₁ * 100
• Percent change = (94 – 85) / 85 * 100
• Percent change = 10.59%

This tells us that sales grew by 10.59% from the first month to the second month.

To calculate percent change in Power BI, you can use the following syntax in DAX:

Percent Change =
VAR _max =
    MAXX ( FILTER ( 'my_data', [Index] < EARLIER ( [Index] ) ), [Index] )
VAR _value =
    MAXX ( FILTER ( 'my_data', [Index] = _max ), [Sales] )
RETURN
    DIVIDE ( [Sales] - _value, _value )

This particular formula calculates the percentage change between rows in the Sales column of the table named my_data.

This formula assumes that you have created an Index column that ranges from 1 to N.

The following example shows how to use this formula in practice.

Example: How to Calculate Percent Change in Power BI

Suppose we have the following table in Power BI named my_data that shows the sales made by a certain company during 10 consecutive sales periods:

Suppose we would like to calculate the percentage change in sales between each consecutive period.

Before we do so, we need to first add an index column to the table.

To do so, click the Home tab along the top ribbon, then click the Transform data icon:

This will bring up the Power Query Editor.

Next, click the Add Column tab, then click the dropdown arrow next to Index Column and choose whether or not you’d like the index values to start at 0 or 1:

We’ll click From 1 so that the following index column is added:

Once you exit out of the Power Query Editor, a message box will appear that asks if you’d like to apply your changes.

Once you click Yes, the index column will be added to the table.

Lastly, to add a new column that shows the percentage change in sales between each consecutive period, click the Table tools tab, then click the New column icon:

Then type the following formula into the formula bar:

Percent Change =
VAR _max =
    MAXX ( FILTER ( 'my_data', [Index] < EARLIER ( [Index] ) ), [Index] )
VAR _value =
    MAXX ( FILTER ( 'my_data', [Index] = _max ), [Sales] )
RETURN
    DIVIDE ( [Sales] - _value, _value )

A new column named Percent Change will be created that shows the percentage change in sales between each consecutive period:

From the output we can see:

• Sales increased by 10.59% from period 1 to period 2.
• Sales increased by 4.25% from period 2 to period 3.
• Sales decreased by 1.02% from period 3 to period 4.

And so on.

Additional Resources

The following tutorials explain how to perform other common tasks in Power BI: