Calculate Customer Rebate based on Tiered Cumulative SalesFYTD

[CMAC_Terry]

Hoping someone out there can help me. I want to calculate a customer rebate based on the cumulative sales multiplied by a  % when sales reach certain levels, eg:

FYTD Sales > 400K , FYTD Sales * 0 ,

FYTD Sales > 400K && FYTD Sales < 500K , FYTD Sales * 0.02

(Above only on the sales between 400K && 500K =  100K * 0.02 = 2K)

FYTD Sales >500K, FYTD Sales * 0.045 (Only on the sales above 500k)

I'm trying create the measure: tried if and switch statements, on cumulatve sales and Sales DatesYTD, but can't get it to work.

Problems I'm having are either I calculate the right figures but it won't display properly over months (to show montly calculation ), or the measure shows correctly over months but calculates incorrect figures ( on the whole sales including the first 400).

Anyone any ideas or done something similar, appreciate all the help I can get,.

Thanks 

[v-huizhn-msft]

Hi @CMAC_Terry,

Please follow the solution @Stachu posted. Please mark the reply as answer if you have resolved your issue. Please share your sample table to test if you still have problems.

Thanks,
Angelia

[Stachu · Accepted solution]

try this, I've put the input values as variables so it's easier to adjust in the future

Rebate =
VAR AmountSold = [FYTD Sales]
VAR UpperValue = 500000
VAR UpperRebate = 0.045
VAR LowerValue = 400000
VAR LowerRebate = 0.02
RETURN
    IF (
        AmountSold > UpperValue,
        ( AmountSold - UpperValue )
            * UpperRebate
            + ( UpperValue - LowerValue )
            * LowerRebate,
        IF ( AmountSold > LowerValue, ( AmountSold - LowerValue ) * LowerRebate, 0 )
    )

[CMAC_Terry]

Thanks for your help