Forum Discussion
Conditional Sum between 2 dates between two tables with 2 conditions
Hi All,
New user to PBI and DAX here, I'm trying to evaluate promotion programs based on daily sales data and promotion information, with 2 simplified tables below
Table 1: Daily Sales Data
| Customer | Invoice Date (dd/mm/yy format) | Sales Value |
| A | 01/01/2021 | 10 |
| A | 15/01/2021 | 15 |
| B | 20/01/2021 | 20 |
| A | 25/01/2021 | 40 |
| B | 02/02/2021 | 30 |
Table 2: Promotion Information
| Customer | Promo Start | Promo End |
| A | 01/01/2021 | 15/01/2021 |
| A | 20/01/2021 | 26/01/2021 |
| B | 01/02/2021 | 15/02/2021 |
For expected results, I want to create a calculated column in Table 2 summing sales value from Table 1 based on 2 conditions: (1) Invoice Date between Promo Start and Promo End Date & (2) Conditional sum based on customer (A/ B). It will look like below table, and the conditions must be variable (cannot be hard-coding) as well (e.g not fixed as "A" and "B" in the formula for customer and specific dates for the dates), as the real datasets are more complex with lots of customer and promotion timings.
Expected Result
| Customer | Promo Start | Promo End | Promo Sales (calculated column) |
| A | 01/01/2021 | 15/01/2021 | 25 (=10+15) |
| A | 20/01/2021 | 26/01/2021 | 40 (=40) |
| B | 01/02/2021 | 15/02/2021 | 30 (=30) |
On relationships between 2 tables, I haven't set yet as it's a many-to-many relationship (a customer can have multiple promotions and appear in many invoices). The closest I've got to the result is using the DATES BETWEEN formula, however, it still with some errors and cannot include the customer condition.
I'm new to DAX and have been struggling with this for more than 9 hours, appreciate any help!
Many thanks, guys!
