Forum Discussion
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 |
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"))
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
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"))
Legend
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.
