Forum Discussion
Dax help : advanced date allocation between tables
Hi,
I would like to generate "Fulfill date" in table 1. Table 1 is the sales order by its site, sales order, line and SKUs.
It lists down the date that customers would like to deliver. So in table 1, the earliest date for each order line will be my priority to fulfill orders. And table 2 is the estimation "shipped date" of each batch of shipment. The scenario is that I would like to base on the priority of customer order (table 1) to map the "Ship date" (table 2) from the earliest to the latest in "Fulfill date."
Help needed for Power BI experts. Thanks.
Table 1:
| Customer | Site | Sales Order | Line | SKU | Date | qty | Aggregated_Qty | Fulfill date | Remarks |
| A | US | A123 | D1 | JJ | 2022-06-10 | 2000 | 2250 | 2022-07-10 | Priority 1 order : first date of Ship Date |
| B | US | B125 | D3 | JJ | 2022-06-21 | 200 | 2250 | 2022-07-21 | Priority 2 order : second date of Ship Date |
| B | US | B125 | D3 | JJ | 2022-07-21 | 50 | 2250 | 2022-08-21 | Priority 3 order : third date of Ship Date |
Table 2:
| Batch | Site | SKU | Ship Date | Qty |
| 1 | US | JJ | 2022-07-10 | 2000 |
| 2 | US | JJ | 2022-07-21 | 200 |
| 3 | US | JJ | 2022-08-21 | 50 |
table 1
add colnum
Batch = rankx(filter(table 1,Site=eariler(Site) && SKU = eariler(SKU), Date,Date,asc)
add colnum
index = Batch & SKU & Site
table 2
add colnum
index = Batch & SKU & Site
link 2 index colnum
3 Replies
vapid128Solution Specialist
table 1
add colnum
Batch = rankx(filter(table 1,Site=eariler(Site) && SKU = eariler(SKU), Date,Date,asc)
add colnum
index = Batch & SKU & Site
table 2
add colnum
index = Batch & SKU & Site
link 2 index colnum
vapid128 thanks. You save my day.
- tamerj1
Community Champion
Hi Anonymous
sorry but I did not understand what are the expected results
