Subtracting from previous cell in Another column

[AlfredASC]

I am trying to get the delta between when a user's certificate expires and when they purchased the renewal.

I have one column of puchase dates, and one column of expiration dates.
I need to subtract the current puchase date from the PREVIOUS expiration date.

I've tried multiple combinations with the EARLIER funtion with no success.
Below is how this would be completed in excel.

Any help someone can provide would be appreciated!

[v-yuta-msft]

@AlfredASC ,

Click Editor Query-> Transform-> Add an Index, then create a calculate column using DAX below:

Days Early or Late = 
VAR Current_Index = 'Table'[Index]
VAR Current_Purchase_Date = 'Table'[Purchase Date]
VAR Previous_Expiration_Date = CALCULATE(MAX('Table'[Expiration Date]), FILTER('Table', 'Table'[Index] = Current_Index - 1))
RETURN
DATEDIFF(Previous_Expiration_Date, Current_Purchase_Date, DAY)

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlfredASC]

Thanks @v-yuta-msft 
For some reason that solution just subtracted all of my expiration dates by 1. Maybe it's because of the way the data tables were built. The purchases are per customer. I'm guessing, since the sequence of purchases for customer A can fall between the purchases for customers B and C, the index function would work for my specific case.

[Greg_Deckler]

Should be the MAXX of Purchase Date for FILTER of the table where Purchase Date < EARLIER Purchase Date. Then you could do a MAXX for Expiration Date for the FILTER of that table filtered down to a Purchase Date equal to the previous value.

[AlfredASC · Accepted solution]

@Greg_Deckler 
Didn't quite follow that. I ended up finding a solution though.

I set up two columns, one was the sequence of the occourence:

Sequence =

COUNTROWS(

FILTER(

CALCULATETABLE(

'table name',

ALLEXCEPT('table name','table name'[Unique User])),

'table name'[Purchase Date]<EARLIER('table name'[Purchase Date]) || ('table name'[Purchase Date]=EARLIER('table name'[Purchase Date]) && 'table name'[Purchase Time] <= EARLIER('table name'[Purchase Time]))))

Then I set up sequence -1.

Using these I was able to run a "Previous Expiration" LOOKUPVALUE for the [Expiration Date], when  [Unique User | sequce] = [Unique User | sequence-1]. That look up table gave me all of the previous expirations dates that I was able to build tables and my [Purchase Date] - [Previous Expiration] measure from.