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 ...
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.

AlB's avatar
AlB
Icon for Community Champion rankCommunity 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
      Icon for Community Champion rankCommunity 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 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Thanks again for your reply.

         

        I still haven't solved the problem tbh so unfortunately I can't accept it as a solution since the totals are very important. I tried adjusting the measure to include the zeros but I didn't manage.