Inventory Tracking & Monitoring - Pivot Table

[aviadbein]

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