Forum Discussion
Calculate difference between two values per ID
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
Anonymous Try:
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 __Result = __Amount - __PrevAmount
RETURN
__PrevAmountBasically MTBF pattern. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
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 |