cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## 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

1 ACCEPTED SOLUTION
Super User

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:

Super User

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:

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors