Calculating Daily Fish Costs in the Inventory Cube

Before you can calculate the daily production cost of fishcake types, you must first determine the daily cost of each individual fish type, taking account of the prices paid for each DaysOld batch and the amount of fish used from each batch.

Recall that in Using DB Functions to Move Data Between Cubes you created the following statements in the Inventory rule to calculate the values of Quantity in Stock - Kgs, Purchase Cost, and Average Purchase Price/Kg for DaysOld batch 1:

['1','Quantity in Stock - Kgs']= N:DB('Purchase',!FishType,'Total Markets',!Date,'Quantity
Purchased - Kgs');['1','Purchase Cost'] = N:DB('Purchase',!FishType,'Total
Markets',!Date,'Purchase Cost - USD');['1','Average Purchase Price/Kg']=
N: ['Purchase Cost'] \ ['Quantity in Stock - Kgs']; 

As each day passes, each DaysOld batch of fish ages by one day, and any unused fish is moved into the next DaysOld batch. To calculate the value of the unused fish each day, you need to multiply the quantity of unused fish by the Average Purchase Price/Kg for the relevant DaysOld batch.

At this point, though, you only have a statement in the rule for the Inventory cube to calculate the Average Purchase Price/Kg for DaysOld batch 1. You need to create a statement that causes the Average Purchase Price/Kg value to "travel" with the batch as it ages. For example, the value of Average Purchase Price/Kg for DaysOld batch 1 on Apr. 30 should "travel" to become the value of Average Purchase Price/Kg for DaysOld batch 2 on May 1. Adding the following statement to the rule for the Inventory cube yields the desired result.

['Average Purchase Price/Kg'] = IF(DIMIX('Date',!Date)=1,0, DB('Inventory',!FishType,
DIMNM('Date', DIMIX('Date', !Date)-1), DIMNM('DaysOld',DIMIX('DaysOld',!DaysOld)
-1), 'Average Purchase Price/Kg'));

An IF statement is required because on the first day of the year there is no fish that is more than one day old and because the formula references the value of Average Purchase Price/Kg for fish on the previous day, which is undefined for the first day of the year.

Recall that the rule for the Inventory cube already includes the statement ['1','Average Purchase Price/Kg'] = N: ['Purchase Cost'] \ ['Quantity in Stock - Kgs'], which calculates the value of Average Purchase Price/Kg for fish in DaysOld batch 1, even on the first day of the year.

The following steps illustrate how to add the statement to calculate Average Purchase Price/Kg to the Inventory rule.