Finding Daily Balances and Opening Balance from Closing Balance and Transaction data

[Renjitv]

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

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​

The output I want is like this

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.

 

[AlanSidman]

Where are you getting the opening balance of 11 for March 29, 2020

I don't see that in your sample.

 

[Renjitv]

Thanks for your reply

I want to find out opening balance from closing balance which is known to me by add or subtracting transaction quantities. For:-

This case I know closing stock of 1 July 2020 is 8 PCs then there is no transactions till 16th June 2020 so all those days stock balance was 8 PCs itself.

But on 15th June a sales happened for 8 PCS and purchase of 8 PCS to on the same day so opening balance will be 8 PCs. like this I need some kind reverse running total to find it.

I did a mistake in output shown 15 June Closing balance in my output there one duplicate line 16 PCs need to deleted.

 

[AlanSidman]

So what you are telling us is:

1. You know the current (closing balance)
2. You know the transactions to get to the closing balance
3. You do not know the opening balance and want to find it out from the data you have i 1 and 2 above.

Is this a correct understanding of your issue?

 

[Renjitv]

Yes, what you mentioned the right understanding of the issue. Thanks for your time

As I got huge number of around 5000 items to find approximately 1 Million total rows.