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 |
Solved! Go to Solution.
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:
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:
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
119 | |
75 | |
66 | |
51 | |
49 |
User | Count |
---|---|
183 | |
101 | |
80 | |
79 | |
77 |