Inventory Table in DAX/Power Pivot

[oliviermulder]

Dear All,

Right now I´m modeling my business and i thought it was a good idea to use the power pivot functions to help me. Everything is working great except for one very essential table in my model. namely the inventories.

the invertory table has 11 columns with the following information
row identifier: A unique number for every row
Period code : a number identifiing the period (for instance 201302 for feb 2013)
Previous Period: a number identifiing the period before (for instance 201301 for jan 2013)
Product: a uniqe code for every product
Start inventory: This should be the end inventory amount of the previous period
Start Value: This should be the end inventory value of the previous period
Production amount: Imported from another table
Production Value: Imported from another table
Sales Amount: Imported from another table
Sales Value: Start inventory value + Production Value - Ending inveroty Value
End inventory amount: Start inventory amount + Production amount - Sales amount
End inventory Value: (Starting value+Production Value)/(Start amount+ Production amount)* End inventory amount.

The main issue I face is getting the starting values in every period as the ending value is a function of the starting value excel keeps complaining that it is an circular reference (even though it is not because it is filtered to a different period) the function I use is the following

=CALCULATE(SUM([Ending_Inventory]),
all(FAC_INV_Finished_Product),
FAC_INV_Finished_Product[Period]=earlier(FAC_INV_Finished_Product[PreviousPeriod]),
FAC_INV_Finished_Product[Product]=EARLIER(FAC_INV_Finished_Product[Product])
)


In "normal excel" it possible to do this with sumifs without excel complaining about circular references

does anybody know how to solve this issue?