topic Re: New column to retrive an earlier row date in DAX Commands and Tips

New column to retrive an earlier row date

fr_jc — Tue, 21 Sep 2021 10:22:20 GMT

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

Re: New column to retrive an earlier row date

Greg_Deckler — Tue, 21 Sep 2021 10:23:14 GMT

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/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

Re: New column to retrive an earlier row date

fr_jc — Tue, 21 Sep 2021 10:26:01 GMT

Speedy response. I'll check it out

Thanks Greg

Re: New column to retrive an earlier row date

fr_jc — Wed, 22 Sep 2021 04:44:19 GMT

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]

)