I have a table of inventory transactions and need to calculate the beginning and ending balances for a given time period, using the current on-hand balance and transaction detail. Please see the attached workbook. What is the best way to do this?
Thanks
11 answers
well for each product and the first entry you have in your data is the "On Hand" the beginning of your analysis?
that can be found easy enough - the first date by product is
Mintransdate = {fixed Product ID:Min(Transaction date)}
and the quantity on that date is {fixed Product id: sum( if [transaction date] = [mintransdate] then [product on hand quantity] end )}
Jim
