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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
kanalaravi
Regular Visitor

Power query Get sum of data between two tables where date1 is greater than or equal to date2

I've table that has the required quantity and another table with available Instock quantity.

 

I need Sum(Instock Qty) where TBL1.Material = Inventory.Material AND
TBL1.[Ship From] = Inventory.[Ship From] AND
TBL1.[Code date] => Inventory.[Inv Date]

 

Table1

MaterialRequired QtyShip FromReq Date
43005040957/10/2018
43004040957/19/2018
430010840957/22/2018
44005040957/15/2018
45006040957/31/2018

 

Inventory

MaterialInstock QtyShip FromInv Date
43002040956/16/2018
430010040956/17/2018
43003040956/16/2018
44004040958/2/2018
45003040956/16/2018
45002040956/16/2018

 


For every Material, Ship From, we need Sum(quantity) check whether next item has enough quantity to fulfill shipment.

Required Output

MaterialRequired QtyShip FromReq DateSum QtyRem QtyCan we FulfillNotes
43005040957/10/2018150100YESCheck whether Item, Ship from match, get sum(qty) for Req Date greater than Inv Date
43004040957/19/201810060YESFor the same item as Row1, get remaining quanitity and check whether req qty is less than rem qty
430010840957/22/201860-48NOFor the same item as Row2, get remaining quanitity and check whether req qty is less than rem qty
44005040957/15/2018Not AvailableNot AvailableNOCannot fulfill as we do not have enough Instock Qty for Item = 4400, Ship From = 4095 & Req Date is less than Inv Date
45006040957/31/201850-10NOReq date is greater than Inv Date but we do not have enough quantity to fulfill shipment
1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@kanalaravi,

 

You may take a look at the post below.

https://community.powerbi.com/t5/Desktop/Accumulative-values-from-a-count/td-p/284229

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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