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
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
Material | Required Qty | Ship From | Req Date |
4300 | 50 | 4095 | 7/10/2018 |
4300 | 40 | 4095 | 7/19/2018 |
4300 | 108 | 4095 | 7/22/2018 |
4400 | 50 | 4095 | 7/15/2018 |
4500 | 60 | 4095 | 7/31/2018 |
Inventory
Material | Instock Qty | Ship From | Inv Date |
4300 | 20 | 4095 | 6/16/2018 |
4300 | 100 | 4095 | 6/17/2018 |
4300 | 30 | 4095 | 6/16/2018 |
4400 | 40 | 4095 | 8/2/2018 |
4500 | 30 | 4095 | 6/16/2018 |
4500 | 20 | 4095 | 6/16/2018 |
For every Material, Ship From, we need Sum(quantity) check whether next item has enough quantity to fulfill shipment.
Required Output
Material | Required Qty | Ship From | Req Date | Sum Qty | Rem Qty | Can we Fulfill | Notes |
4300 | 50 | 4095 | 7/10/2018 | 150 | 100 | YES | Check whether Item, Ship from match, get sum(qty) for Req Date greater than Inv Date |
4300 | 40 | 4095 | 7/19/2018 | 100 | 60 | YES | For the same item as Row1, get remaining quanitity and check whether req qty is less than rem qty |
4300 | 108 | 4095 | 7/22/2018 | 60 | -48 | NO | For the same item as Row2, get remaining quanitity and check whether req qty is less than rem qty |
4400 | 50 | 4095 | 7/15/2018 | Not Available | Not Available | NO | Cannot fulfill as we do not have enough Instock Qty for Item = 4400, Ship From = 4095 & Req Date is less than Inv Date |
4500 | 60 | 4095 | 7/31/2018 | 50 | -10 | NO | Req date is greater than Inv Date but we do not have enough quantity to fulfill shipment |
You may take a look at the post below.
https://community.powerbi.com/t5/Desktop/Accumulative-values-from-a-count/td-p/284229
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |