Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculation of Differences

Hi everyone.

 

I have been trying to calculate the difference between the Basic Pay of each Employee for each Month, whereby the "Employee" field is the same. Refer to a sample of the data at hand below:

 

Refer to a sample output below:

Thus, is there a way of achieving this within Power BI?

 

Many thanks in advance!

Need Help
  • v-xuding-msft's avatar
    v-xuding-msft
    7 years ago

    Hi Anonymous ,

    I modified the formula that you can have a try.

    Diff = 
var pay = SWITCH(TRUE(),Table1[Basic Pay] = BLANK(),0,Table1[Basic Pay])
var last = CALCULATE(SUM(Table1[Basic Pay]),FILTER(ALLEXCEPT(Table1,Table1[Employee]),Table1[Month] = EARLIER(Table1[Month])-1))
var mi = MIN(Table1[Month])
return 
IF(Table1[Month] = mi,0,pay - last)

    I have attached my sample. If it still doesn't work in your file, please point the difference between my sample and yours.

     

    Best Regards,

    Xue Ding

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

11 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous 

    Convert  [Month] to whole number if it is not already. Then place employee and month in a matrix visual. Create this measure and place it in values of the matrix the visual:

     

    MoMDiff =
VAR CurrentMonthPay_ =
    SELECTEDVALUE ( Table1[Basic Pay] )
VAR PreviousMonthPay_ =
    IF (
        SELECTEDVALUE ( Table1[Month] ) = 1,
        CurrentMonthPay_,
        CALCULATE (
            SELECTEDVALUE ( Table1[Basic Pay] ),
            FILTER (
                ALL ( Table1[Month] ),
                Table1[Month]
                    = ( SELECTEDVALUE ( Table1[Month] - 1 ) )
            )
        )
    )
RETURN
    CurrentMonthPay_ - PreviousMonthPay_

    If years are involved as well, you'll need some modification

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi AlB this is of great help thank you!

       

      One thing I've noticed is that the total of the measure is appearing as blank. Is there a reason for this please?

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

         

        yes, that's because of the SELECTEDVALUE(). At the Total there is no filter context and SELECTEDVALUE returns blank. Check it out here Please mark the response as solution if that's the case and consider kudoing if the posts are of help.

        Cheers