Forum Discussion
How to find changes in data from several dates
Hi,
I am trying to figure out how find changes from one date to another, where the dates are in the same column. The change should only register changes from the same ID as the date prior.
| Date | ID | Amount | Change |
| 2021-09-30 | 1 | 10 | |
| 2021-09-30 | 2 | 20 | |
| 2021-08-30 | 1 | 40 | |
| 2021-08-30 | 2 | 5 |
I want the change column to show the difference from ID 1 from the last date to this date. Preferably where there can be more dates, and the column always shows the change from the date before.
Anonymous 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
3 Replies
Anonymous 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 - __PreviousThank you Greg_Deckler , this pretty much solved it.
I added some more to also filter by ID, see below
Column =VAR __Current = [Value]VAR ID2 = [ID]VAR PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])VAR Previous = MAXX(FILTER('Table',[Date]=PreviousDate && [ID]=ID2),[Value])RETURN__Current - PreviousAnonymous Awesome. Yeah, I believe the full MTBF article did something similar. It is a fairly common pattern.