Forum Discussion

AlfredASC's avatar
AlfredASC
Frequent Visitor
7 years ago
Solved

Subtracting from previous cell in Another column

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 subtrac...
Need Help
  • AlfredASC's avatar
    AlfredASC
    7 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago
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's avatar
AlfredASC
Frequent Visitor
7 years ago

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.