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.

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.