New column to retrive an earlier row date

[fr_jc]

Hi, I am hoping someone may point me in the right direction with creating a column either by DAX or power query.  I've spent a bit of time looking for an example but can't seem to find one that I can use with the conditions i have.

I am wanting to add a calculated column of the days between dates of an account which exist on different rows, or if possible just return the previous date.

As an example I am trying to replicate the last two rows in this table.  Looking at the account of a row I am wanting to return the accounts previous original date.  My table has accounts which have multiple occurences where I am wanting just the previous date seen for each row.

Account	Original Date	Subsequent Follow Up Date	Days between previous original date of Account	Previous Date
2002	3/02/2021	1/03/2021
1001	1/05/2021	15/06/2021
3003	3/05/2021	31/05/2021
1001	15/06/2021	21/06/2021	51	1/05/2021
2002	1/07/2021	5/07/2021	20	3/02/2021
2002	5/07/2021	9/07/2021	8	1/07/2021
3003	31/07/2021	5/08/2021	94	3/05/2021
4004	1/09/2021	4/09/2021

Many thanks in advance

[Greg_Deckler · Accepted solution]

@fr_jc 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

[Greg_Deckler · Accepted solution]

@fr_jc 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

[fr_jc · Accepted solution]

@Greg_Deckler 

Many thanks Greg.  The bit I was strugling with was filtering by account and with your help in that article  there I could see what i was missing.

MAXX(

FILTER(

Warranty,

Warranty[Original Date] < EARLIER(Warranty[Original Date]) &&

Warranty[Account] = EARLIER(Warranty[Account])

),

Warranty[Original Date]

)

[fr_jc]

Speedy response.  I'll check it out

Thanks Greg