cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors