Hello community!
I have a problem summing the result of two tables. The first table has the sold quantities. The second table has the forecasted quantities. Both tables are linked to a calendar table (not represented). The third table has cut-off dates for both products.
Exported:
| Product | Date | Quantity |
| A | 1/1/2022 | 10 |
| A | 2/1/2022 | 10 |
| A | 3/1/2022 | 10 |
| B | 1/1/2022 | 5 |
| B | 2/1/2022 | 5 |
| B | 3/1/2022 | 5 |
Forecast
| Product | Date | Quantity |
| A | 1/1/2022 | 20 |
| A | 2/1/2022 | 20 |
| A | 3/1/2022 | 20 |
| A | 4/1/2022 | 20 |
| B | 1/1/2022 | 15 |
| B | 2/1/2022 | 15 |
| B | 3/1/2022 | 15 |
| B | 4/1/2022 | 15 |
Cut Off Dates
| Product | CutOffDate |
| A | 2/1/2022 |
| B | 3/1/2022 |
The first goal is to filter both tables with the cut off date, getting from the first table and the product A & B:
| Product | Date | Quantity |
| A | 1/1/2022 | 10 |
| A | 2/1/2022 | 10 |
| B | 1/1/2022 | 5 |
| B | 2/1/2022 | 5 |
| B | 3/1/2022 | 5 |
Those dates are <=2/1/2022 for product A (The cut off date for product A) and <=3/1/2022 for product B (The cut off date for product B).
After that I need the same for table 2, but considering the dates after the cut off date:
| Product | Date | Quantity |
| A | 3/1/2022 | 20 |
| A | 4/1/2022 | 20 |
| B | 4/1/2022 | 15 |
Next, I need to mix both tables to obtain:
| Product | Date | Quantity |
| A | 1/1/2022 | 10 |
| A | 2/1/2022 | 10 |
| A | 3/1/2022 | 20 |
| A | 4/1/2022 | 20 |
| B | 1/1/2022 | 5 |
| B | 2/1/2022 | 5 |
| B | 3/1/2022 | 5 |
| B | 4/1/2022 | 15 |
Finally, my goal is to have the following result:
| Date | Quantity |
| 1/1/2022 | 15 |
| 2/1/2022 | 15 |
| 3/1/2022 | 25 |
| 4/1/2022 | 35 |
I tried to do it using max for the dates, but I can't keep the Product context filter, causing that my table is only filtered by my maximum cut off date (3/1/2022)
Thanks in advance!
