Hi. I have two data set "Movements" and "Closing stock" . From this data I want to calculate End of the day closing quantity. Movements data gives daily(day level) sales and purchases of a product and is sum from Col J to Col AK. And closing stock gives only month end closing stock. In order to calculate daily closing stock , we have to add movement of first day of the month to the closing stock of last day. For eg : If closing stock as on 31st Jan 2023 is 1000 , (which becomes opening stock for 1st feb 2023) and movement data for 1st Feb is 200 , then EOD closing quantity would be 1000+200=1200. Now this would become opening stock for 2nd Feb. so if Movement on 3rd Feb is 500 then closing stock quantity for 3rd Feb would be 1200+500= 1700 and so on for next day. This should be done on plant and material level.
