Hey guys,

So I'm trying to calculate purchase price variance through a calculated field. I can get the results I need on table mode using lookups, but not through a single calculation that allows me to drill through and manipulate different dates and dimensions (Product family etc) to see the variance.

Here's the logic we need:

1. If product number was bought this year, then it will look to the previous year (2015) to compare variance. If the product was bought the previous year, then we calculate the difference between the average cost of the two years. Easy so far.

2. If the product was bought the current year, but not last year, then we need to look back 2 years and compare that average cost from that year (2014).

3. If purchased all 3 years, then we only compare current year vs. 2015, ignoring 2014.

3. Calculations should be null if current year is null (Product not purchased), or if product was bought during the current year, but then not before 2014. So were only calculating this for products that were bought this year and 2015, or this and 2014, or all three years.

So in the attached workbook product A, C and E would get a variance calculation. The rest would be null. Product C would compare 2016 vs. 2015 and ignore 2014.

When I throw the variance calc in there, I should be able to remove the product code pill, and it should aggregate everything.

I'm stuck with this one. Let me know if anyone can crack this one! Thanks guys.

JR