How to find changes in data from several dates

[Anonymous]

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. 

[Greg_Deckler · Accepted solution]

@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/3395....
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

[Anonymous]

Thank 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 - Previous

[Greg_Deckler]

@Anonymous Awesome. Yeah, I believe the full MTBF article did something similar. It is a fairly common pattern.