Looping with balance

[RicardoSchmidt]

Hello everyone!

Need some help to transform something I've made in VBA to turn into M.

Table1 - Production per order (one value per order)

Order	Production (tons)	Production (meters)
4952001	1230	3100
4978203	54	20
4992001	3200	9210

Table 2 - Orders with the delivery dates and quantities ordered (many values per order)

Order	Delivery date	Qtd (tons)	Qtd (meters)
4952001	Feb/10/22	800	2016
4952001	Mar/10/22	500	1260
4978203	Feb/10/22	50	18
4992001	Apr/25/22	200	576
4992001	Apr/30/22	1000	2878
4992001	Jul/15/22	750	2159
4992001	Aug/05/22	3000	8634

I need to merge them into a table with my orders and distributing the total produces into the months ordered and if in the last item it is above can bring the total or can bring the ordered, but if it's below it needs to bring whats was produced. Considering all the deliveries. The result is like below.

Order	Delivery date	Qtd (tons)	Qtd (meters)	Production (tons)	Production (meters)
4952001	Feb/10/22	800	2016	800	2016
4952001	Mar/10/22	500	1260	430	1084
4978203	Feb/10/22	50	18	50	18
4992001	Apr/25/22	200	576	200	576
4992001	Apr/30/22	1000	2878	1000	2878
4992001	Jul/15/22	750	2159	750	2159
4992001	Aug/05/22	3000	8634	1250	3597

I just splitted the total production into the delivery dates and making a balance in the end.

Is there anyway to make it in Power Query? 

Thanks

[Manoj_Nair · Accepted solution]

@RicardoSchmidt 

This can be done using DAX as shown below. I have done the "Tons" part. If the solution meets your expectation, please let me know so that I can share the final PBIX file