How to use the prior month value in a calculation inside a pivot table?

[stewstremel]

Hello,

I'm trying to work with a rolling inventory projection. The core issue we have is that some sites report inventory on a quarterly basis. We are generating reports on a monthly basis. We take the known orders/shipments for the month, add that to the inventory and then subtract consumption to establish a project inventory. This works if we have inventory every month, but when we don't (or when we want to project it into the future) then pivot table breaks down.

What I am hoping to find a way to do is to take the prior months projected inventory and use that for the current month starting point. Is this possible?

pivot table Image attached.

Sample Table:

sample.xlsx
	A	B	C	D	E	F	G	H
1	L5	Anchor Date Name	theDate	Monthly Inventory	Forecast	Requested Qty	Remaining Qty	Shipment Shipped Qty
2	Product A	Supply Plan Date	2020-01-01	116720	29743
3	Product A	Supply Plan Date	2020-02-01	0	91125
4	Product A	Supply Plan Date	2020-03-01	0	91125
5	Product A	Supply Plan Date	2020-04-01	318005	77732
6	Product A	Shipment Estimated Ship Date	2020-04-01					76896
7	Product A	Order Estimated Ship Date	2020-04-01				0
8	Product A	Shipment Estimated Delivery Date	2020-04-20					76896
9	Product A	Order Estimated Delivery Date	2020-04-20				0
10	Product A	Supply Plan Date	2020-05-01	0	77732
Horizontal Table

I have attempted the following calculated field but it isn't working for me. Any help is appreciated.
=if ( sum([Monthly Inventory]) > 0
, sum([Monthly Inventory]) - sum([Forecast])
, calculate(sum([Monthly Inventory]), PREVIOUSMONTH([theDate]) ) - sum([Forecast])
)

Cheers,