Hi all,
In order to track inventory level and behavior I would like to generate the following Pivot Table:
Rows: PartNumber, Measures
Columns: Month(DueDate)
Measures:
Open Inventory: Close Inventory of previous month
Incoming: sum(PurchaseOrders)
Outgoing: -sum(SalesOrders)
Close Inventory: =sum(PurchaseOrders) - sum(SalesOrders)
In a nutshell, I would like to calculate open and close inventory per month for each PartNumber.
The expected result should be something like this:
| Part Number | | Mar | Apr | May | Jul | Jul |
|---|
| PN1 | Open Inventory | Mar | 20 | 40 | -10 | 10 |
| Incoming | 80 | 60 | 0 | 100 | 100 |
| Outgoing | -60 | -40 | -50 | -80 | -50 |
| Close Inventory | 20 | 40 | -10 | 10 | 60 |
| PN2 | Open Inventory | | 20 | 60 | 140 | 140 |
| Incoming | 100 | 100 | 100 | 100 | 100 |
| Outgoing | -80 | -60 | -20 | -100 | -200 |
| Close Inventory | 20 | 60 | 140 | 140 | 40 |
| PN# | Open Inventory | | 10 | 20 | 30 | -10 |
| Incoming | 50 | 50 | 50 | 0 | 80 |
| Outgoing | -40 | -40 | -40 | -40 | -40 |
| Close Inventory | 10 | 20 | 30 | -10 | 30 |
I managed to generate it with Table (without "per Part Number") by using the following logic (and formulas):
Open Inventory: Rangesum(above(total Sum({<DueDate= {'<=$(=date(max(MRP_Date)))'}>}Quantity),1,rowno(total)))
Incoming: Sum({<DueDate= {'<=$(=date(max(MRP_Date)))'},OrderType = {'Purchase Orders'}>}Quantity)
Outgoing: Sum({<DueDate= {'<=$(=date(max(MRP_Date)))'},OrderType = {'Sales Orders'}>}Quantity)
Close Inventory: Rangesum(above(total Sum({<DueDate= {'<=$(=date(max(MRP_Date)))'}>}Quantity),0,rowno(total)))
BUT, as long as Rangesum(above()) function works fine with Table it's not working with Pivot Table as I expected (not calculating each Part Number separately).
What am I doing wrong?
Thanks in advance
