Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two unrelated tables with M-2-M
Table A
AccountNum | Warehouse | Item Number | EFFECTIVE_DATE_KEY | EXPIRATION_DATE_KEY |
006724 | WH9 | ItemPQR | 20220101 | 20220930 |
006724 | WH9 | ItemPQR | 20220401 | 20221231 |
006724 | WH9 | ItemPQR | 20220501 | 20220930 |
Table B
AccountNum | Warehouse | Item Number | Quantity | DeliveryDateKey |
006724 | WH9 | ItemPQR | 4000 | 20221011 |
006724 | WH9 | ItemPQR | 5000 | 20220131 |
AccountNum, Warehouse, and Item Number can result in multiple rows per table.
I have tried LOOKUPVALUE, NATURALLEFTOUTERJOIN (to denormalize). Results are not as expected.
How do I get Quantity from Table B if TableB.DeliveryDateKey is > TableA.EffectiveDateKey and <ExpirationDateKey? Expected result is:
AccountNum | Warehouse | Item Number | EFFECTIVE_DATE_KEY | EXPIRATION_DATE_KEY | Quantity | Notes |
006724 | WH9 | ItemPQR | 20220101 | 20220930 | 5000 | 20220131 bteween 20220101 and 20220930. Hence Qty=5000 |
006724 | WH9 | ItemPQR | 20220401 | 20221231 | 4000 | 20221011 falls between 20220401 and 20221231. hence qty = 4000 |
006724 | WH9 | ItemPQR | 20220501 | 20220930 | 0 | Nothing found |
Solved! Go to Solution.
CALCULATEDCOLUMN=SUMX(FILTER(TableB,TableB[DeliveryDateKey]>TableA[EffectiveDateKey]&&TableB[DeliveryDateKey]<TableA[ExpirationDateKey]&&TableB[AccountNum]=TableA[AccountNum]&&TableB[Warehouse]=TableA[Warehouse]&&TableB[ItemNumber]=TableA[ItemNumber]),TableB[Quantity])
CALCULATEDCOLUMN=SUMX(FILTER(TableB,TableB[DeliveryDateKey]>TableA[EffectiveDateKey]&&TableB[DeliveryDateKey]<TableA[ExpirationDateKey]&&TableB[AccountNum]=TableA[AccountNum]&&TableB[Warehouse]=TableA[Warehouse]&&TableB[ItemNumber]=TableA[ItemNumber]),TableB[Quantity])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
36 | |
33 | |
18 | |
18 |