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 ID | Amount | Date |
| 1 | 100 | 1/1/2022 |
| 1 | 200 | 2/1/2022 |
| 1 | 300 | 3/1/2022 |
| 2 | 500 | 1/1/2022 |
| 2 | 600 | 2/1/2022 |
| 2 | 700 | 3/1/2022 |
| 3 | 150 | 1/1/2022 |
| 3 | 300 | 2/1/2022 |
| 3 | 450 | 3/1/2022 |
So that information would look something like this.
| Employee ID | Amount | Difference | Date |
| 1 | 100 | 0 | 1/1/2022 |
| 1 | 200 | 100 | 2/1/2022 |
| 1 | 300 | 100 | 3/1/2022 |
| 2 | 500 | 0 | 1/1/2022 |
| 2 | 600 | 100 | 2/1/2022 |
| 2 | 700 | 100 | 3/1/2022 |
| 3 | 150 | 0 | 1/1/2022 |
| 3 | 300 | 150 | 2/1/2022 |
| 3 | 450 | 150 | 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
3 Replies
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 - __PreviousHello 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