As Chayce said, probably capturing the previous period inventory and saving the value on a numeric field on the new inventory period record would be the best solution.
You can do this with a pipeline and a couple of summary and lookup fields, something like this:
- Create Summary field to obtain Max Record ID of child records.
- Create Lookup field to this field that was just created in step 1.
- Create Summary field to obtain Max Record ID, but this time with filter where Record ID# must be less than [Lookup from step 2]
- Create Lookup field to field created on step 3.
- Summary field to obtain Max Ending Inventory, with filter where Record ID# must be equal to [Lookup from step 4]
- Create new data entry numeric field to capture Previous Ending Inventory value.
- Then create pipeline to run for example when an inventory record gets updated and the "Previous Ending Inventory" is empty, then have the pipeline query the parent record, and obtain the value from "Max Ending Inventory" and save this value on "Previous Ending Inventory" of the inventory record that triggered the pipeline.
------------------------------
Alberto Tablada
Lead Software Engineer
BlueWell Group
Florencia
+50688083124
------------------------------