Calculated Column to Retrieve previous date where value was different (Filtered by criteria)

[CRanjan12]

Hi all,

I am attempting to find previous last_run date where the position value was different in a records table based on the ID and product being the same. So following the table below, when the position value column was last different and have that shown in a table as a calculated column.

id	position	product	last_run	Desired Column - Previous Position	Desired Column - Previous date
0012000000bkPfwAAE	Bottom	Product A	01-Sep-24	Middle	01-Jun-24
0012000000bkPfwAAE	Middle	Product A	15-Aug-24	Top	01-May-24
0012000000bkPfwAAE	Middle	Product A	01-Aug-24	Top	01-May-24
0012000000bkPfwAAE	Middle	Product A	15-Jul-24	Top	01-May-24
0012000000bkPfwAAE	Middle	Product A	09-Jul-24	Top	01-May-24
0012000000bkPfwAAE	Middle	Product A	15-Jun-24	Top	01-May-24
0012000000bkPfwAAE	Middle	Product A	01-Jun-24	Top	01-May-24
0012000000bkPfwAAE	Top	Product A	01-May-24	Middle	01-Feb-24
0012000000bkPfwAAE	Top	Product A	01-Apr-24	Middle	01-Feb-24
0012000000bkPfwAAE	Top	Product A	01-Mar-24	Middle	01-Feb-24
0012000000bkPfwAAE	Middle	Product A	01-Feb-24	Middle	01-Nov-23
0012000000bkPfwAAE	Middle	Product A	01-Jan-24	Middle	01-Nov-23
0012000000bkPfwAAE	Middle	Product A	01-Dec-23	Middle	01-Nov-23
0012000000bkPfwAAE	Middle	Product A	01-Nov-23	Middle	01-Nov-23
0012000000bkPfwAAE	Bottom	Product B	01-Sep-24	Middle	01-Jun-24
0012000000bkPfwAAE	Middle	Product B	15-Aug-24	Top	01-May-24
0012000000bkPfwAAE	Middle	Product B	01-Aug-24	Top	01-May-24
0012000000bkPfwAAE	Middle	Product B	15-Jul-24	Top	01-May-24
0012000000bkPfwAAE	Middle	Product B	09-Jul-24	Top	01-May-24
0012000000bkPfwAAE	Middle	Product B	15-Jun-24	Top	01-May-24
0012000000bkPfwAAE	Middle	Product B	01-Jun-24	Top	01-May-24
0012000000bkPfwAAE	Top	Product B	01-May-24	Middle	01-Feb-24
0012000000bkPfwAAE	Top	Product B	01-Apr-24	Middle	01-Feb-24
0012000000bkPfwAAE	Top	Product B	01-Mar-24	Middle	01-Feb-24
0012000000bkPfwAAE	Middle	Product B	01-Feb-24	Middle	01-Nov-23
0012000000bkPfwAAE	Middle	Product B	01-Jan-24	Middle	01-Nov-23
0012000000bkPfwAAE	Middle	Product B	01-Dec-23	Middle	01-Nov-23
0012000000bkPfwAAE	Middle	Product B	01-Nov-23	Middle	01-Nov-23

I have attempted using MAX function in a calculated column but I dont think I am getting the variables right. 

Would be great to get your assistance 🙂

Thank you

[Greg_Deckler]

@CRanjan12 See my article on Mean Time Between Failure (MTBF) which uses EARLIER. In this case, you would just need to stop at PreviousDate variable (see below). And of course include your ID and Product name in your filter.

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 ) * 1.