I have inventory data so that I have Inventory Amount, Usage Amount, and Add Amount for the current month.
I am able to calculate an on-hand value for the current month easily; Inventory-Usage+Add.
For future months I have only Usage Amount and Add Amount.
What kind of measures do I need to create when I want to calculate On hand value over time for the future?
So the current month's on-hand value is starting inventory amount for the next month which I use to calculate the on-hand value.
e.g.
| Month | InventoryValue | NeedAmount | AddAmount | OnHand |
| 03-2023 | 100 | 50 | 20 | 100-50+20=70 |
| 04-2023 | | 30 | 20 | 70-30+20=60 |
The idea is to create a report that shows the current inventory value and ending on-hand value based on the need and add transactions placed for the future.
I have 'Date' table and all three facts are placed on own tables (inventoryamount, needs, adds).
