Build Relationship with Multiple Date Field (Larger and Smaller Than)

[tomcch]

Hello all,

The main table is Sales Volume Table, I would like to join it to the Sales Incentive Table to get the STAFF name by joining 1) Customer Column (this one is simple), 2) the [SALES VOLUME TABLE.DATE] Larger than [SALES INCENTIVE TABLE.FROM DATE] and 3)the [SALES VOLUME TABLE.DATE] Smaller than [SALES INCENTIVE TABLE.TO DATE].

In the below case, only the first three records of the sales volume table should show Peter as a result becasue the last record's date is September 2023 which is not within the FROM DATE and TO DATE of the Sales Incentive table.

Is it something we could do in the power bi?

Thank you so much.

SALES VOLUME TABLE

CUSTOMER	DATE
A	2023-01-02
A	2023-01-21
A	2023-01-02
A	2023-09-02

SALES INCENTIVE TABLE

CUSTOMER	STAFF	FROM DATE	TO DATE
A	PETER	2023-01-01	2023-03-01
B	TOM	2023-01-01	2023-03-01
C	DAVID	2023-03-01	2023-06-01

[v-yiruan-msft · Accepted solution]

Hi @tomcch ,

You can create a calculated column as below in the table 'Sales Volume' to get it, please find the details in the attachment.

Column = 
CALCULATE (
    MAX ( 'Sales Incentive'[STAFF] ),
    FILTER (
        'Sales Incentive',
        'Sales Incentive'[CUSTOMER] = 'Sales Volume'[CUSTOMER]
            && 'Sales Volume'[DATE] > 'Sales Incentive'[FROM DATE]
            && 'Sales Volume'[DATE] < 'Sales Incentive'[TO DATE]
    )
)

Best Regards

[Arpitb12]

Connect and load your "Sales Volume Table" and "Sales Incentive Table."
Create a relationship between these tables using the "CUSTOMER" column.
Use DAX to filter the "Sales Volume Table" based on date ranges in the "Sales Incentive Table."
Build a visualization with the "STAFF" column to display staff names for filtered sales data.

[tomcch]

Thank you.

I'm a beginner of PBI, which DAX function should I use?