2 Replies Latest reply on Mar 3, 2017 2:16 PM by Nathan Schneider

    Find Most Recent Payment and Corresponding Payment Amount

    Nathan Schneider

      I have a set of sustaining member data, and I would like to extract from it a table of unique members by ID, determine what their most recent payment date was, and find the payment amount corresponding to the most recent payment date. But, I am stuck.

       

      Here is a breakdown of the data and assumptions:

      1. Each member has a Join Date
      2. Each member pays monthly, but can sometimes miss a payment or change their contribution amount at any time
      3. Each member is set to auto renew on the anniversary of their join date. However, if they miss a payment, their renewal date is delayed by the number of months that they missed a payment in their current 12 month cycle
      4. Their renewal date (Next Pmt Cycle Start) is determined by their most recent payment date plus pmts remaining (number of months) + 1 month
        • So if someone missed a payment and their most recent payment date was February 1, 2017 and they have six payments remaining, then their renewal date would be Feb 1 + 6 months + 1 month = September 1, 2017

       

      I've been able to figure out a most recent payment date and next payment cycle start using level of detail calculations. However, I cannot figure out how to pull the most recent payment amount (e.g. the payment amount corresponding to the most recent payment date). This would be a "vlookup" type calculation, but I can't figure out how to do that in Tableau. The most recent payment could be lower or higher than prior payments, so I can't simply use a min or max function.

       

      Here is a snapshot of some sample data, and below that the desired output. The column in gray is the calculation I need to determine.

       

       

      Sorry for the lengthy post. I suppose the title is a summary of what I'm looking for!

       

      Thanks,

       

      Nate