Power Pivot Question

[hitdave85]

Hello,

I have created a table (Query1) that contains 4 years (YearPull --> ex: 2017) of data. The table also contains product codes (Product_Code). Here are the measures I have created so far:

Sum of Final Units:=SUM(Query1[Final Units])

Sum of Final Units Year 1:=CALCULATE([Sum of Final Units],FILTER(Query1,Query1[YearPull]=[min year]))
Sum of Final Units Year 2:=CALCULATE([Sum of Final Units],FILTER(Query1,Query1[YearPull]=[min year]+1))
Sum of Final Units Year 3:=CALCULATE([Sum of Final Units],FILTER(Query1,Query1[YearPull]=[min year]+2))
Sum of Final Units Year 4:=CALCULATE([Sum of Final Units],FILTER(Query1,Query1[YearPull]=[min year]+3))

Sum of Year 1-2 Weights:=IF(OR(ISBLANK([Sum of Final Units Year 1]),ISBLANK([Sum of Final Units Year 2])),0,[Sum of Final Units Year 2])
Sum of Year 2-3 Weights:=IF(OR(ISBLANK([Sum of Final Units Year 2]),ISBLANK([Sum of Final Units Year 3])),0,[Sum of Final Units Year 3])
Sum of Year 3-4 Weights:=IF(OR(ISBLANK([Sum of Final Units Year 3]),ISBLANK([Sum of Final Units Year 4])),0,[Sum of Final Units Year 4])

For the 'Sum of Year X-Y Weights' measures, here is what I'm trying to achieve. For the 'Sum of Year 1-2 Weights, I want to sum up the units sold in year 2, but only for products that existed in both year 1 and year 2. What I'm finding is that when I create a pivot table and use these measures in aggregate, I'm not getting the correct totals. However, if I pull in the Product_Code into the rows, then I'm getting the correct totals if I manually add up the columns (the pivot table totals are still incorrect). I realize that one way around this is to add additional columns to my table, but since I have a large data set, I'm trying to achieve this via measures if possible.

Any ideas would be greatly appreciated!
David