DAX Formula to Split a Month in Tiered Commission

[Auto_queen]

I'm fairly new to Dax and I'm not sure this is possible, but I figured I would ask. 

This is for a Sales Commission. The GP total factors into the tiered structure, but they only get commission on one type of GP (GP for Commission). 
These are the tiers: 

Running Total = 

• $0 - $100,000 gets 10%
• 100,001 - 200,000 gets 15%
• 200,001 - 310,000 gets 20%
• and 310,001 and above gets 25%

This is the formula I wrote for the commission amount and it seems to be working well: 

Commission Amount = SWITCH(TRUE,

[Running Total] <= 100000, [GP for Commission] * .10,

[Running Total]  > 10000 && [Running Total] <= 200000, [GP for Commission] * .15,

[Running Total]  > 200000 && [Running Total] <= 310000, [GP for Commission] * .20,

[Running Total]  > 310000, [GP for Commission] * .25)

The issue is that when a person reaches the next tier for the first time, instead of multiplying the full month at the next tier, they split the month. So, for example, in the chart below, in June this person reached the next tier because their running total is $108,085.77, so instead of the GP for Commission amount being multiplied by .15, they would split the $10,802 in half and have 5,401 multiplied by .10 and 5,401 multiplied by .15. Next month if the person is in the same tier level, the full amount would be multiplied at .15.  Is there a way to do this in Dax? 

 

[Jihwan_Kim · Accepted solution]

Hi,

I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file. All measures are in the attached file.

I hope the below can provide some ideas on how to create a solution for your dataset.

[Auto_queen]

Worked perfectly. Thank you! Now that I see the formula you wrote it makes sense to me. I appreciate your help!