Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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