I'm working on a simple little market tracker for a video game and need some help making the calculations work correctly. Basically video game has an item, which is made from other items- Example: Item Wood Box is made from Item Wood Plank x3 and Nails x3. I'm also keeping track of the daily price fluctuations of these items as sold on the community marketplace- example Wood Plank Monday $5, Tuesday $4.50, Wednesday $6. (This gets plotted to a simple line graph to watch the price over time.) Pretty simple basic stuff so far. A table for items and a related table that creates a new record for each day to update the price values into. Where I'm running into trouble is having it automatically do some calculations for me. I'd like to have a calculation that will tell me how much it would cost to buy the required quantity of an item at it's most recent price- so Wood Plank most recent cost is $6, quantity needed is 3 so Cost*Quantity. I'm having partial success with a calculation that looks like this:
Recipe::Amount Needed * Last(DailyPriceCheck_Ingredients::DailyPrice)
but it doesnt seem to filter down to the correct item properly and the first entry in the portal will be correct, but the rest will have the same value from the first line and not their own. What is the proper formula for something like this, or other steps I may be overlooking in the table relations?