Forum Discussion
Calculate difference between two values per ID
Hello everyone I currently have this type of data set and was wondering if there is a way to calculate the difference month over month, with January being the first month, per employee. Employee...
Anonymous Whoops, was troubleshooting. Yes, that can be done:
Column = VAR __Employee = [Employee ID] VAR __Date = [Date] VAR __Amount = [Amount] VAR __PrevDate = MAXX(FILTER(ALL('Table'),[Date]<__Date),[Date]) VAR __PrevAmount = MAXX(FILTER(ALL('Table'),[Employee ID] = __Employee && [Date] = __PrevDate),[Amount]) VAR __Diff = __Amount - __PrevAmount VAR __Result = IF(__PrevAmount = BLANK(), BLANK(), __Diff) RETURN __Result
Hello Greg_Deckler,
The formula has worked! I just had to switch the last part as that was just returning the previous amount as opposed to the result. I did have a question though. Is there a way to have it ignore the January value as it will have no comparison? Right now in the data it is showing the chart below, instead of a zero. Thanks for all the help!
| Employee ID | Amount | Difference | Date |
| 1 | 100 | 100 | 1/1/2022 |
| 1 | 200 | 100 | 2/1/2022 |
| 1 | 300 | 100 | 3/1/2022 |
Anonymous Whoops, was troubleshooting. Yes, that can be done:
Column =
VAR __Employee = [Employee ID]
VAR __Date = [Date]
VAR __Amount = [Amount]
VAR __PrevDate = MAXX(FILTER(ALL('Table'),[Date]<__Date),[Date])
VAR __PrevAmount = MAXX(FILTER(ALL('Table'),[Employee ID] = __Employee && [Date] = __PrevDate),[Amount])
VAR __Diff = __Amount - __PrevAmount
VAR __Result = IF(__PrevAmount = BLANK(), BLANK(), __Diff)
RETURN
__Result