Hi Friends,
I am facing an issue to daily stock balances and opening stock for a given day for an item where I know only closing stock and daily transacted quantities.
My transaction data is like below
Closing stock data is here
The output I want is like this
Appreciate your help.
I am facing an issue to daily stock balances and opening stock for a given day for an item where I know only closing stock and daily transacted quantities.
My transaction data is like below
Item Code | Quantity | UOM | Movement | Transaction Date |
XXX29681 | -4 | EA | Damage Write off | 29-Mar-20 |
XXX29681 | -7 | EA | Sales | 16-Apr-20 |
XXX29681 | 8 | EA | Return from Customer | 04-Jun-20 |
XXX29681 | 8 | EA | Purchase | 15-Jun-20 |
XXX29681 | -8 | EA | Sales | 15-Jun-20 |
Closing stock data is here
XXX29681 | 8 | EA | Closing Balance | 01-Jul-20 |
Balance Remarks | Date | Balance Qty |
Opening Balance | 29-Mar-20 | 11 |
Closing Balance | 29-Mar-20 | 7 |
Closing Balance | 16-Apr-20 | 0 |
Closing Balance | 04-Jun-20 | 8 |
Closing Balance | 15-Jun-20 | 16 |
Closing Balance | 15-Jun-20 | 8 |
Closing Balance | 01-Jul-20 | 8 |
Appreciate your help.