I’m doing your standard Sales analysis (think AdventureWorks DW), but the twist I’m dealing with is that each of my Products has a recipe (ProductKey, IngredientKey, Weight), but the Ingredient is a slowly changing dimension (because the price changes over time). The “recipe” table is static.

So I could calculate the ingredient cost per unit pretty easily, like this:

Per Unit Cost = SUMX('RecipeDetails',[Ingredient Cost] / RELATED('Product'[Yield]))

My problem is that since the IngredientCost will come from the Purchases table (related to Ingredient), how would I retrieve the price for a given week? (Assuming Purchases is (IngredientKey, DateKey, Units, UnitPurchasePrice) ). Can I use FILTER() to retrieve the cost related to the (IngredientKey, DateKey) in the current evaluation context? or do I have to create new versions of the Recipe (ProductKey, IngredientKey) all the time?