Help with load for converting inventory transaction to inventory month end reporting

[#1]

Hi,

Please see below what I am trying to acheive.

Anyone that can lead me in the right direction for how to set up the load script for this?

Thanks in advance..

Item	Transaction Date		Sequence	Invy. Trns. ID		Quantity		Transaction Type	Inventory After Transaction
Item A	07.11.2010	07:40	1	CMT127350	1	2.9500	ML	Receipt	2.9500	ML
Item A	07.11.2010	07:46	1	NMT057376	1	2.9500	ML	Issue	0.0000	ML
Item A	21.12.2010	03:04	1	CMT172763	1	4.4500	ML	Receipt	4.4500	ML
Item A	21.12.2010	03:11	1	NMT070847	1	2.0500	ML	Issue	2.4000	ML
Item A	20.01.2011	07:36	1	CMT201977	1	1.0000	ML	Issue	1.4000	ML
Item A	20.01.2011	07:36	2	CMT201990	1	1.0000	ML	Issue	0.4000	ML
Item A	20.01.2011	07:39	1	CMT202104	1	0.4000	ML	Issue	0.0000	ML
		Desired End Result:
			Item	Month End date	Inventory
			Item A	30.11.2010	0
			Item A	31.12.2010	2,4
			Item A	28.01.2011	0
		Need to find the latest date for the month and use last inventory after transaction before month end
		For the current month period I would like the updated inventory figure for today until the month end is reached

[hector · Accepted solution]

Hi, i've made this test

Check it out, and tell us if this works for you.

Rgds

[Anonymous]

Hello

I have the same issue, but instead of trying to get the inventory at Month End Date I try to get it for every date between first and last Transaction Date.

I have a Master Calendar with all possible dates available (excluding weekend etc)

If you can guide me through this issue, that would be great.

Thanks and regards