cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RolinMartis
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 newQtr_Year end HC final
FY19-206031
FY20-215999
FY21-226030

 

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 newQtr_Year end HC finaldiff%diff
FY19-206031  
FY20-2159996031-0.53%
FY21-22603059990.52%

 

can anyone help me urgently

1 ACCEPTED SOLUTION

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] ) )

            )

    )

)

View solution in original post

3 REPLIES 3
amitchandak
Super User
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 ])

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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!

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] ) )

            )

    )

)

View solution in original post

Hi, @RolinMartis 

Could you please tell me whether your problem has been solved?

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


Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors