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.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-xuding-msft this is excellent thank you!

 

However, during testing, I noticed that for a particular employee who had a Basic Pay figure in Months 2 and 4 but not in Month 3, the difference between Month 4 and Month 3 was not calculated despite there being an increase in Basic Pay.

 

For ease of reference, refer to the screenshot below:

Is there a reason for this please?

 

Many thanks once again!

v-xuding-msft's avatar
v-xuding-msft
Icon for Community Support rankCommunity Support
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.