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

[Dominok123]

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

[PattemManohar · Accepted solution]

@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"))

[PattemManohar · Accepted solution]

@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"))

[Dominok123]

Legend

[HotChilli · Accepted solution]

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.

[PattemManohar]

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