Adding Opening and Closing Stocks to a Pivot Table based on From and To Dates

[#1]

Hi,

Long time listener, first time caller

I have been searching for a solution to my problem but I can't seem to find it on here.

I have been asked to put together a Stock Movement report for our accounting team.  I can easily create a pivot table to show them the sum of Units, Weight and Value for each transaction type by product code based on a From and To date the user specifies (stored in two variables - dDateFrom and dDateTo).  My delemer is that I want to include the opening stock at the left hand side of the expression columns and than display the closing stock figures at the far right hand side of the report. 

See attached for an example but basically; Opening, Movements, Closing by product.

I have two main tables for this report.  tInvMov (contains all the movements per day per product) and tInvByDay (contains the snap shots of the stock on hand at the end day, by product).

So, as I said, the movement side of things is working well.  I have been struggling with adding the Opening and Closing stocks to either side of the movements.  I want to use the dDateFrom value to find the appropriate tInvByDay record for the Opening values and the dDateTo to find the appropriate Closing values.

Is this even possible?  What am I missing?

Thanks in advance.

Clayton