Calculate Salesperson's Commission Tier

[Aggro]

I have a table_a contains salesperson's commission data and is grouped by month. I want to find out it's commission tier in table_b, and below is the tables' info. I want to show the salesperson's sales tier and commission rate in table_a. The period from table_a, should be equal or greater than the effective date in table_b because we may change the commission rate.

The salesperson's needs to meet order counts and total sales to earn the tier.

table_a

Sales personal	Period	Order Counts	Total Sales	Expected Tier	Expected Commission Rate
John	9/30/2023	101	5001	1	2.00%
John	10/31/2023	251	12499	1	2.00%
Amy	8/31/2023	300	15000	2	3.00%
Amy	9/30/2023	400	25000	2	3.00%
Amy	8/31/2023	500	29000	3	4.00%
Alex	10/31/2023	599	32000	3	4.00%
Bradon	10/31/2023	650	33000	4	5.00%

table_b

Effective Date	Sales Tier	Order Counts	Total Sales	Commission Rate
7/1/2023	1	100	5,000	2.00%
7/1/2023	2	250	12,500	3.00%
7/1/2023	3	450	22,500	4.00%
7/1/2023	4	600	30,000	5.00%

[parry2k · Accepted solution]

@Aggro you can add the calculated column using following DAX expression and use the same logic to get the Commission Rate:

Tier 1 = 
VAR __Period = TableA[Period]
VAR __Orders = TableA[Order Counts]
VAR __Sales = TableA[Total Sales]
RETURN
CALCULATE ( 
    MAX ( TableB[Sales Tier] ),
    TableB[Effective Date] <= __Period,
    TableB[Order Counts] <= __Orders,
    TableB[Total Sales] <= __Sales
)

[JorgePBI]

This might get you started and get the juices flowing

Sales Tier and Commission Rate = 
VAR CurrentMonth = MAX('Table_A'[Period])  
RETURN
CALCULATE(
    SELECTEDVALUE('Table_B'[Commission Tier], "No Tier Defined"),
    FILTER(
        'Table_B',
        'Table_B'[Effective Date] <= CurrentMonth
        && 'Table_A'[Total Sales] >= 'Table_B'[Minimum Sales]
        && 'Table_A'[Order Count] >= 'Table_B'[Minimum Order Count]
    )
)