Forum Discussion

RolinMartis's avatar
RolinMartis
Helper II
5 years ago
Solved

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

General Comment
  • RolinMartis's avatar
    RolinMartis
    5 years ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

    • RolinMartis's avatar
      RolinMartis
      Helper II
      5 years ago

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

                  )

          )

      )
      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        5 years ago

        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