cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculating Percentage Difference between Current and Previous Row only while the ID is the same

Hey guys,

I am struggling with trying and getting the third column '% Difference' (in the table below) working in Power BI.

Basically I need to work out the percent difference of the current row of 'Value' when compared to the previous row in the 'Value' column. Furthermore, I can't have two different ID's being compared, hence why there is a 0 when the ID changes.

Any help would be much appreciated.

Table1:

 ID Value % Difference 1 50 0 1 52 4 1 50 -4 1 55 10 2 36 0 2 40 10.5 3 60 0 3 66 20 3 62 -6.2

2 ACCEPTED SOLUTIONS
Community Champion

@Dominok123  Add a new column i.e. Index Field in "Power Query Editor"

Then add a new column in DAX as below

```% Difference =
VAR _CurrVal = Test228RowDiff[Value]
VAR _PrevID = LOOKUPVALUE(Test228RowDiff[ID],Test228RowDiff[Index],Test228RowDiff[Index]-1)
VAR _PrevVal = LOOKUPVALUE(Test228RowDiff[Value],Test228RowDiff[Index],Test228RowDiff[Index]-1,Test228RowDiff[ID],Test228RowDiff[ID])
RETURN IF(Test228RowDiff[ID] = _PrevID,FORMAT((_CurrVal-_PrevVal)/_CurrVal,"Percent"),FORMAT(0,"Percent"))```

Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion

Super User

Hi, I was just playing about with the problem when the solution was posted.  It's a good solution but i need to point out that the percentage calculation is wrong and there's a rogue non-existent table in the formula.

I also added the index column - because DAX engine has to know how to find the previous row.

For comparison, another DAX formula to get the previous value is

```previous value = CALCULATE(FIRSTNONBLANK(Table2[Value], 1),            FILTER(Table2, Table2[ID] = EARLIER(Table2[ID]) &&
Table2[Index] = EARLIER(Table2[Index]) - 1))```

and the percentage calculation has to have the previous value as the denominator.

4 REPLIES 4
Community Champion

@Dominok123  Add a new column i.e. Index Field in "Power Query Editor"

Then add a new column in DAX as below

```% Difference =
VAR _CurrVal = Test228RowDiff[Value]
VAR _PrevID = LOOKUPVALUE(Test228RowDiff[ID],Test228RowDiff[Index],Test228RowDiff[Index]-1)
VAR _PrevVal = LOOKUPVALUE(Test228RowDiff[Value],Test228RowDiff[Index],Test228RowDiff[Index]-1,Test228RowDiff[ID],Test228RowDiff[ID])
RETURN IF(Test228RowDiff[ID] = _PrevID,FORMAT((_CurrVal-_PrevVal)/_CurrVal,"Percent"),FORMAT(0,"Percent"))```

Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion

Frequent Visitor

Legend

Super User

Hi, I was just playing about with the problem when the solution was posted.  It's a good solution but i need to point out that the percentage calculation is wrong and there's a rogue non-existent table in the formula.

I also added the index column - because DAX engine has to know how to find the previous row.

For comparison, another DAX formula to get the previous value is

```previous value = CALCULATE(FIRSTNONBLANK(Table2[Value], 1),            FILTER(Table2, Table2[ID] = EARLIER(Table2[ID]) &&
Table2[Index] = EARLIER(Table2[Index]) - 1))```

and the percentage calculation has to have the previous value as the denominator.

Community Champion

Thanks@HotChilli  yes the index is pointing to wrong table, because of Lazy Intellisense option. Updated the table name now.

Did I answer your question? Mark my post as a solution!

Proud to be a PBI Community Champion

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors