I want to create a charge amount based on a tiered rate structure. I found a model out there created by Victor Acquach (if you are still out there I would love to see your response) and built my model just like what he had in his. With some stuggle I am now getting a total charge but it is only going through the first tier to calculate. Here is the tiered rates that I am workin with for my example.
| Type | Usage Band | Usage From | Usage To | Rate | Date From | Date To | $Amount | |
| COMM | 1 to 6 | 0 | 600 | 4.60 | 5/1/2016 | 4/30/2017 | - | the first 600 units |
| COMM | 7 to 50 | 600 | 5000 | 5.09 | 5/1/2016 | 4/30/2017 | 27.60 | next 4400 units |
| COMM | 51 to 951 | 5000 | 99500 | 4.29 | 5/1/2016 | 4/30/2017 | 223.96 | next 94500 units |
| COMM | > 1000 | 99500 | 999999 | 3.03 | 5/1/2016 | 4/30/2017 | 4,054.05 | over 1,000,000 |
| COMM | 1 to 6 | 0 | 600 | 4.63 | 5/1/2017 | 4/30/2018 | - | |
| COMM | 7 to 50 | 600 | 5000 | 5.14 | 5/1/2017 | 4/30/2018 | 27.78 | |
| COMM | 51 to 951 | 5000 | 99500 | 4.40 | 5/1/2017 | 4/30/2018 | 226.16 | |
| COMM | > 1000 | 99500 | 999999 | 3.05 | 5/1/2017 | 4/30/2018 | 4,158.00 | |
Here is the calculated field:
Charge =
CALCULATE (
CALCULATE (
( SUM ( Summary[Qty-100CCF] ) - MAX ( RatesTable[USAGE FROM] ) )
* MAX ( RatesTable[RATE] )
+ MAX ( RatesTable[$Amount] ),
FILTER (
RatesTable,
SUM ( Summary[Qty-100CCF] ) < RatesTable[USAGE TO]
&& SUM ( Summary[Qty-100CCF] ) >= RatesTable[USAGE FROM]
),
FILTER (
RatesTable,
max(Summary[RunDate]) < RatesTable[DATE TO]
&& max(Summary[RunDate]) >= RatesTable[DATE FROM]
)
),
FILTER (
ALL ( 'Calendar' ),
Calendar[MonthID] = MAX ( Calendar[MonthID] )
&& Calendar[Date] <= MAX ( Calendar[Date] )
)
)
Any suggestiion are welcome.
