Switch DAX rebate based on cumulative measure

Question

I am having a bit of tough time wrapping my head around this. I have a cumulative total based on hours and I need to calculate the rebate. The rule here is that after that total exceeds an amount, change the rebate percentage (10%, 15%). Regardless how I try it, I end up with the wrong total or the wrong percentage.

Here's my failed approach:

+------------+-------------+------------+
| Day        | BilledHours | LaborPrice |
+------------+-------------+------------+
| 07/01/2018 | 98          | 13000      |
+------------+-------------+------------+
| 07/02/2018 | 89          | 12000      |
+------------+-------------+------------+
| 07/03/2018 | 80          | 11000      |
+------------+-------------+------------+
| 07/04/2018 | 92          | 9000       |
+------------+-------------+------------+
| 07/05/2018 | 52          | 8000       |
+------------+-------------+------------+
| 07/06/2018 | 73          | 7000       |
+------------+-------------+------------+
| 07/07/2018 | 82          | 11000      |
+------------+-------------+------------+


Cumulative Hours = 
CALCULATE(
    SUM(Rebates[BilledHours]),
    FILTER(
        ALL(Rebates[Day]),
        Rebates[Day]<=MAX(Rebates[Day]))
        )

Rebate = 
SUMX(Rebates,
    SWITCH(
        TRUE(),
        [Cumulative Hours]<=400,Rebates[LaborPrice] * 0.10,
        [Cumulative Hours]>=401,Rebates[LaborPrice] * 0.15)
        )

How can I achieve the correct rebate and total? If I use the SWITCH before the sum, I end up with the incorrect total (since the total is always > 400).

Answer 1

First, modify your measure for Cumulative Hours as follows:

Cumulative Hours =
CALCULATE (
    SUM ( Rebates[BilledHours] ),
    FILTER ( ALL ( Rebates ), Rebates[Day] <= MAX ( Rebates[Day] ) )
)

Second, modify Rebate measure:

Rebate =
SUMX (
    Rebates,
    VAR Cumulative_Hours = [Cumulative Hours]
    RETURN
        SWITCH (
            TRUE (),
            Cumulative_Hours <= 400, Rebates[LaborPrice] * 0.1,
            Cumulative_Hours >= 401, Rebates[LaborPrice] * 0.15
        )
)

Result:

Why your formula is not working:

Create a simple test measure and visualize it:

   Wrong Rebate Hours = SUMX( Rebates, [Cumulative Hours])

It's easy to see that there is no "accumulation" - [Cumulative Hours] inside SUMX stop being cumulative, and as a result your SWITCH test is always < 400. Essentially, SUMX modifies filter context of the [Cumulative Hours] measure by introducing row context from Rebates (through "context transition" - lookup the concept on Google).