cancel
Showing results for
Did you mean:
Frequent Visitor

## Need help in calculating % difference compared to previous value of a measure

Hi ,

I am compltely new to powerBI and i would need your help with below request.

I have a employee level data loaded in my model and i have created a matrix table chart showing HC for every fiscal quarter.

 Fiscal Year new Qtr_Year end HC final FY19-20 6031 FY20-21 5999 FY21-22 6030

Fiscal year is calculated column based on effective date and "Qtr_Year end HC" is a measure to calculate Quarter end or tear end HC

I need to calculate % Difference in the same matrix table to show the below result.

 Fiscal Year new Qtr_Year end HC final diff %diff FY19-20 6031 FY20-21 5999 6031 -0.53% FY21-22 6030 5999 0.52%

can anyone help me urgently

1 ACCEPTED SOLUTION
Frequent Visitor

I canot do it on dates bec my Matrix table is showing Headcount for Fiscal Year text column

I used the below formulae and it worked

Previous row value FINAL =

CALCULATE (

[Qtr_Year end HC final],
FILTER (
ALL ( 'Tablename' ),
'Tablename'[Fiscal Year new]
= CALCULATE (
MAX (  'Tablename'[Fiscal Year new] ),
FILTER ( ALL ( 'Tablename'),  'Tablename'[Fiscal Year new] < SELECTEDVALUE ( 'Tablename'[Fiscal Year new] ) )

)

)

)
3 REPLIES 3
Super User

@RolinMartis , if you do not have data at date level, Create a new table with FY. new table works better for measure

with a Rank column

Year Rank = RANKX(all("Date"),"Date"[FY],,ASC,Dense)

This Year = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Year Rank]=max("Date"[Year Rank])))
Last Year = CALCULATE(sum("order"[Qty]), FILTER(ALL("Date"),"Date"[Year Rank]=max("Date"[Year Rank])-1))

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
Frequent Visitor

I canot do it on dates bec my Matrix table is showing Headcount for Fiscal Year text column

I used the below formulae and it worked

Previous row value FINAL =

CALCULATE (

[Qtr_Year end HC final],
FILTER (
ALL ( 'Tablename' ),
'Tablename'[Fiscal Year new]
= CALCULATE (
MAX (  'Tablename'[Fiscal Year new] ),
FILTER ( ALL ( 'Tablename'),  'Tablename'[Fiscal Year new] < SELECTEDVALUE ( 'Tablename'[Fiscal Year new] ) )

)

)

)
Community Support

Hi, @RolinMartis

If yes, you could accept your reply as solution.It will help other community members easily find the solution when they get the similar issue.

Best Regards,
Community Support Team _ Eason

Announcements