Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Meghbajaj
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:

IDSalaryEffective DateEnd Date
100150001/04/201825/07/2018
100170026/07/201818/03/2019
100185019/03/201931/12/2999
1002110016/07/201931/12/2999
1003150001/05/201931/12/2019
1003175001/01/202031/12/2999

 

Result Table:

IDSalaryEffective DateEnd DateSalary ChangePercent Change

1001

50001/04/201825/07/201800
100170026/07/201818/03/201920040
100185019/03/201931/12/299915021.4
1002110016/07/201931/12/299900
1003150001/05/201931/12/201900
1003175001/01/202031/12/299925016.66

 

 

1 ACCEPTED SOLUTION
FreemanZ
Super User
Super User

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:

FreemanZ_0-1672980102431.png

View solution in original post

1 REPLY 1
FreemanZ
Super User
Super User

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:

FreemanZ_0-1672980102431.png

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.