Ok I think I get this now…a really interesting example (and challenging one).
I would like to work up an example of this myself with some demo data and then send through a good overview of how to do this, then also create a video out of it.
In the meantime…
This is how I believe you would do this.
First you would SUM( Sum of Total Quantity )
When you use this sum within the current context of BOM item from the lookup table dimAllItems, it will always return the full total that we want.
When you place start date into the context of the calculation, you will still receive the full value every row which is good.
Then we need to calculate the cumulative total based on dates of the Required Quantity column from the Production table.
This way we can get a cumulative total and subtract this from the full total, giving the allocation and then the amount remaining to be allocated.
This is the logic.
I will have to either work up some demo data myself, or if you can send me your model in Power BI I can likely work up some images of this faster.
Let me know what you think