Power BI calculate tiered commission, commission threshold changes over time

Question

I have sales records, if the sales involves Party B, Party A has to split revenue with Party B for all sales >$10000. This split is set by the threshold below, and the boss is free to update the threshold anytime. For example, below shows a threshold change effective on 1 Jan 2025.

Effective date	revenue threshold	Party A Split%	Party B Split%
1-1-2024	10000	50%	50%
1-1-2024	20000	80%	20%
1-1-2025	10000	50%	50%
1-1-2025	20000	70%	30%
1-1-2025	30000	90%	10%

How to get below resulting calculated columns "Party A Split $" and "Party B Split $" in Power BI?

Sales_date	Invoice#	Party-B-involved?	revenue	Party A Split $	Party B Split $
6-6-2024	001	Y	800	800 (No split, no threshold met)	0
6-6-2024	002	Y	10000	5000 (50%)	5000 (50%)
6-6-2024	003	Y	40000	32000 (80%)	8000 (20%)
7-7-2025	004	Y	800	800 (No split, no threshold met)	0
7-7-2025	005	Y	10000	5000 (50%)	5000 (50%)
7-7-2025	006	Y	40000	36000 (90%)	4000 (10%)

Answer 1

you can try this

Column=
VAR _date =
    MAXX (
        FILTER ( 'Table', 'Table'[Effective date] <= 'Table (2)'[Sales_date] ),
        'Table'[Effective date]
    )
VAR _rt =
    MAXX (
        FILTER (
            'Table',
            'Table'[Effective date] = _date
                && 'Table'[revenue threshold] <= 'Table (2)'[revenue]
        ),
        'Table'[revenue threshold]
    )
VAR _split =
    MAXX (
        FILTER (
            'Table',
            'Table'[Effective date] = _date
                && 'Table'[revenue threshold] = _rt
        ),
        'Table'[Party A Split%]
    )
RETURN
    IF ( ISBLANK ( _rt ), 'Table (2)'[revenue], 'Table (2)'[revenue] * _split )

Column2=
VAR _date =
    MAXX (
        FILTER ( 'Table', 'Table'[Effective date] <= 'Table (2)'[Sales_date] ),
        'Table'[Effective date]
    )
VAR _rt =
    MAXX (
        FILTER (
            'Table',
            'Table'[Effective date] = _date
                && 'Table'[revenue threshold] <= 'Table (2)'[revenue]
        ),
        'Table'[revenue threshold]
    )
VAR _split =
    MAXX (
        FILTER (
            'Table',
            'Table'[Effective date] = _date
                && 'Table'[revenue threshold] = _rt
        ),
        'Table'[Party B Split%]
    )
RETURN
    IF ( ISBLANK ( _rt ), 0, 'Table (2)'[revenue] * _split )