Hi all,
i have to create a Inventory FIFO report with the folowing stucture:
Item-No, Description of item - main dimensions
Quantity,Purchase date and some more fields from the purchase documents (see attached image).
the goal of this report is to display for each item the last purchase movments , according to the stock balance.
for example, if the item "stock balance" is 550, the last purchase documents would be displaied from bottom to up, until we gat the 550 items:
Purchase Date
| Quantity |
|---|
| 01/01/2011 | 50 |
| 01/01/2009 | 300 |
| 01/01/2008 | 100 |
| 01/01/2007 | 100 |
most of the times, the quantity of the first displied purchase, sould be reduced to fit to the current "stock balance". in the previous example, the real quantity of 01/01/2007 was actually 200.
the requirement is to specify the report date, and show the balance, up to this time.
Assuming i can calculate the balance for a specific date (by summing up all movments until the report date), how can i create a pivot that will show only the last purhcase moments until the report date acording to the fifo concept explained before.
Many thanks,
Shlomo
