I have three tables:
Calendar Table:
Date Month & Year
| 01/08/2023 | Aug-23 |
| 01/09/2023 | Sep-23 |
| 01/10/2023 | Oct-23 |
| 01/11/2023 | Nov-23 |
| 01/12/2023 | Dec-23 |
Order Data
Item Pieces Delivery Month
| Item 1 | 125 | 01/09/2023 |
| Item 1 | 75 | 01/09/2023 |
| Item 2 | 200 | 01/08/2023 |
Stock Data
Item Quantity Available Run Rate
This measure needs to calculate at the start of the month how many pieces we have (stock data) + Any deliveries (Order Data) for that month minus the Run Rate for that month (Stock Data).
For example:
Item 1: the quantity is 72, there are no deliveries that month, the deliveries are next month in September, and the run rate is 5 So let's say the first date in the calendar table is 01 August 2023 the matrix will look something like this:
Item 1:
August 23 - Stock Date + Deliveries (72 + 0) = 72
September 23 - Stock Data + Deliveries - Run Rate (72 + (125 + 75) - 5) = 267
October 23 - Stock Data + Deliveries - Run Rate (267 + 0 - 5) = 262
Item 2:
August 23 - Stock Date + Deliveries (670+ 833) = 1503
September 23 - Stock Data + Deliveries - Run Rate (1503 + 0 - 100) = 1403
October 23 - Stock Data + Deliveries - Run Rate ( 1403 + 0 - 100) = 1303
I have tried a 1000 ways to make this work but I can't seem to get it to work
