Percentage Change and Value Difference

[Meghbajaj]

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

[FreemanZ · Accepted 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: