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

 

 

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.

4 Replies

  • 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.

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      AlfredASC
      Frequent Visitor
      7 years ago

      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.