Forum Discussion
Calculation of Differences
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.
Community Champion
So you need the total to sum over the months per employee? Try this:
MoMDiff =
VAR CurrentMonthPay_ =
SUM ( Table1[Basic Pay] )
VAR PreviousMonthPay_ =
IF (
SELECTEDVALUE ( Table1[Month] ) = 1,
CurrentMonthPay_,
CALCULATE (
SUM ( Table1[Basic Pay] ),
FILTER (
ALL ( Table1[Month] ),
Table1[Month]
= ( SELECTEDVALUE ( Table1[Month] - 1 ) )
)
)
)
RETURN
CurrentMonthPay_ - PreviousMonthPay_
Please always show your sample data in text-tabular format in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Unfortunately, your solution does not solve my problem. The total I would like to obtain is the total of the Differences.
For example: In case of EMP1, given that his employee increased by €200 in month 2 and by €1000 in month 4, then the total difference for that employee is €1200 (as can be seen in the screenshot within my previous reply).
For ease of reference, kindly find the table that I am currently using below:
| Employee | Month | Basic Pay | MonthNo |
| EMP1 | 001 | 1,000 | 1 |
| EMP2 | 001 | 1,500 | 1 |
| EMP3 | 001 | 2,000 | 1 |
| EMP1 | 002 | 1,200 | 2 |
| EMP2 | 002 | 1,300 | 2 |
| EMP3 | 002 | 2,100 | 2 |
| EMP1 | 003 | 1,200 | 3 |
| EMP2 | 003 | 1,400 | 3 |
| EMP3 | 003 | 2,200 | 3 |
| EMP1 | 004 | 2,200 | 4 |
| EMP2 | 004 | 3,000 | 4 |
| EMP3 | 004 | 4,000 | 4 |
Apologies for not doing so earlier.
Thanks once again!
v-xuding-msftCommunity Support
Hi Anonymous ,
You could create a calculated column to implement.
Diff = var last = CALCULATE(SUM(Table1[Basic Pay]),FILTER(ALLEXCEPT(Table1,Table1[Employee]),Table1[Month] = EARLIER(Table1[Month])-1)) return IF(last = BLANK(),0,Table1[Basic Pay] - last)
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.
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
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.