Forum Discussion
Solved
Percentage Change and Value Difference
Hi Everyone, I am trying to calculate Percentage Change and Value Difference group by ID in the Power BI report. Please suggest the optimal way to do it. Thanks a lot.
Original Table:
| ID | Salary | Effective Date | End Date |
| 1001 | 500 | 01/04/2018 | 25/07/2018 |
| 1001 | 700 | 26/07/2018 | 18/03/2019 |
| 1001 | 850 | 19/03/2019 | 31/12/2999 |
| 1002 | 1100 | 16/07/2019 | 31/12/2999 |
| 1003 | 1500 | 01/05/2019 | 31/12/2019 |
| 1003 | 1750 | 01/01/2020 | 31/12/2999 |
Result Table:
| ID | Salary | Effective Date | End Date | Salary Change | Percent Change |
1001 | 500 | 01/04/2018 | 25/07/2018 | 0 | 0 |
| 1001 | 700 | 26/07/2018 | 18/03/2019 | 200 | 40 |
| 1001 | 850 | 19/03/2019 | 31/12/2999 | 150 | 21.4 |
| 1002 | 1100 | 16/07/2019 | 31/12/2999 | 0 | 0 |
| 1003 | 1500 | 01/05/2019 | 31/12/2019 | 0 | 0 |
| 1003 | 1750 | 01/01/2020 | 31/12/2999 | 250 | 16.66 |
hi Meghbajaj
try to add two columns like this:
Salary Change 2 = VAR _id = [ID] VAR _date = [Effective Date] VAR _table = FILTER(TableName, TableName[ID]=_id) VAR _datepre = MAXX( FILTER(_table, TableName[Effective Date]<_date), TableName[Effective Date] ) VAR _salarypre = MINX( FILTER(_table, TableName[Effective Date]=_datepre), TableName[Salary] ) RETURN IF( _salarypre<>BLANK(), [Salary] - _salarypre) Percent Change 2 = VAR _id = [ID] VAR _date = [Effective Date] VAR _table = FILTER(TableName, TableName[ID]=_id) VAR _datepre = MAXX( FILTER(_table, TableName[Effective Date]<_date), TableName[Effective Date] ) VAR _salarypre = MINX( FILTER(_table, TableName[Effective Date]=_datepre), TableName[Salary] ) RETURN IF( _salarypre<>BLANK(), DIVIDE( [Salary] - _salarypre, _salarypre))it worked like this:
1 Reply
hi Meghbajaj
try to add two columns like this:
Salary Change 2 = VAR _id = [ID] VAR _date = [Effective Date] VAR _table = FILTER(TableName, TableName[ID]=_id) VAR _datepre = MAXX( FILTER(_table, TableName[Effective Date]<_date), TableName[Effective Date] ) VAR _salarypre = MINX( FILTER(_table, TableName[Effective Date]=_datepre), TableName[Salary] ) RETURN IF( _salarypre<>BLANK(), [Salary] - _salarypre) Percent Change 2 = VAR _id = [ID] VAR _date = [Effective Date] VAR _table = FILTER(TableName, TableName[ID]=_id) VAR _datepre = MAXX( FILTER(_table, TableName[Effective Date]<_date), TableName[Effective Date] ) VAR _salarypre = MINX( FILTER(_table, TableName[Effective Date]=_datepre), TableName[Salary] ) RETURN IF( _salarypre<>BLANK(), DIVIDE( [Salary] - _salarypre, _salarypre))it worked like this: