5 Replies Latest reply on Mar 30, 2016 11:52 AM by Joel Hughes

# Latest value calculation by customer id and date

From a set of customer data, I'm looking to find the last purchase in 2015 and the last purchase in 2016 and find the difference between the two. For example, in the attached workbook customer 5687 made two purchases in 2015 (\$114.11 in February and \$100.98 in August) and one purchase in 2016 (\$82.88 in March). What I'm looking to accomplish in this example is to calculate \$100.98 - \$82.88, and for that same calculation to happen across the entire dataset. In some cases, customers will only have purchases in 2015, and in other cases customers will only have purchases in 2016.

Any help would be appreciated. Thank you!

Joel

• ###### 1. Re: Latest value calculation by customer id and date

Hi Joel,

In the attached workbook I have put together a few different ways this could be calculated depending on how you want the missing values to be handled. I also set up a calculation to identify records that do not have values in both years if you want to filter those out. The LOD version would be useful if you wanted to take the average of the differences in max customer purchases. The calculations selecting 2016 and 2015 are currently locked but these could be driven by a parameter or set to automatically updated by referencing off of the most recent date in the data set {MAX([Date])}.

Thanks,

Kent

1 of 1 people found this helpful
• ###### 2. Re: Latest value calculation by customer id and date

Hi, Kent.

I appreciate your reply--thank you! I think I should clarify that I am looking for the difference between the latest 2015 and 2016 amounts, not the difference between the 2015 and 2016 maximum amounts. So the approximate -31 difference (when comparing maximum amounts) for customer 5687 would instead be an approximate +18 (when comparing latest amounts).

• ###### 3. Re: Latest value calculation by customer id and date

Sorry for the mix up the same type of calculations can be used to do this. I will redo the example and post it.

• ###### 4. Re: Latest value calculation by customer id and date

Hi Joel,

I have updated the calculations to find the most recent date inside of each year and compare those values.

Thanks,

Kent

1 of 1 people found this helpful
• ###### 5. Re: Latest value calculation by customer id and date

Thank you, Kent! This is just what I was looking to do. I appreciate your help,

Joel