Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
RicardoSchmidt
Regular Visitor

Looping with balance

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)

OrderProduction (tons)Production (meters)
495200112303100
49782035420
499200132009210

 

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

 

OrderDelivery dateQtd (tons)Qtd (meters)
4952001Feb/10/228002016
4952001Mar/10/225001260
4978203Feb/10/225018
4992001Apr/25/22200576
4992001Apr/30/2210002878
4992001Jul/15/227502159
4992001Aug/05/2230008634

 

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.

 

OrderDelivery dateQtd (tons)Qtd (meters)Production (tons)Production (meters)
4952001Feb/10/2280020168002016
4952001Mar/10/2250012604301084
4978203Feb/10/2250185018
4992001Apr/25/22200576200576
4992001Apr/30/221000287810002878
4992001Jul/15/2275021597502159
4992001Aug/05/223000863412503597

 

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

1 ACCEPTED SOLUTION
Manoj_Nair
Solution Supplier
Solution Supplier

@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

Manoj_Nair_0-1669934469093.png

 

View solution in original post

1 REPLY 1
Manoj_Nair
Solution Supplier
Solution Supplier

@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

Manoj_Nair_0-1669934469093.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors