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

# Find Most Recent Payment and Corresponding Payment Amount

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

• ###### 1. Re: Find Most Recent Payment and Corresponding Payment Amount

Nathan,

I think you might be able to use another LOD calculation to get the amount of the most recent payment:

{ FIXED [Member ID]: MAX(

IF [Pay Date]=[MostRecentPaydate]

THEN [Pay Amount]

END)

}

Only get the PayAmount if it is from the Most Recent PayDate,

and then use the LOD to fix to every row of that MemberID.

1 of 1 people found this helpful
• ###### 2. Re: Find Most Recent Payment and Corresponding Payment Amount

Swaroop - this worked like a charm! Thank you very much for your help.