I have to warn you, this is NOT a level 100 or even 200 tutorial. If you decided to proceed, that you absolutely positively have to get that progressive calculation work and I will have to assume that your DAX is pretty good, otherwise you would not be even attempting to tackle this problem.
Also, this is a big topic so I simply cannot type the entire thing out, so you are going to have to watch the video and download the tutorial and go through all the steps on your own to get comfortable with the calculation.
Download the Power BI Desktop file here.
Cumulative Discount Tutorial can be found here: https://youtu.be/9hGowlw4YPc
Invoice Progressive Discount Amount =
var DateFrom = [Inv Discount Date From]
var DateTo = [Inv Discount Date To]
var InvoiceDate = InvoiceSales[Invoice Date]
var custID = InvoiceSales[CustomerID]
var selectedCustomer =
IF(
CONTAINS(
Discount,
Discount[CustomerID],
InvoiceSales[CustomerID],
Discount[EffectiveFrom],
DateFrom,
Discount[EffectiveTo],
DateTo
), InvoiceSales[CustomerID], -1)
var InvoiceNumber = InvoiceSales[Invoice Number]
RETURN
IF(
NOT(ISBLANK(DateFrom)||ISBLANK(DateTo)),
-- we need to know how much had been invoiced before
-- this current invoice had come in
var totalPriorSalesInDiscWindow =
CALCULATE(
SUM(InvoiceSales[Invoice Amount]),
FILTER(
ALL(InvoiceSales),
InvoiceSales[Invoice Date]>=DateFrom &&
InvoiceSales[Invoice Date]<=InvoiceDate &&
InvoiceSales[Invoice Number]<>InvoiceNumber &&
InvoiceSales[CustomerID] = custID
)
)
--cache the highest row in discount table in this variable so we can reuse it
var DiscountTableForTopBracket =
FILTER(
Discount,
Discount[EffectiveFrom] = DateFrom &&
Discount[EffectiveTo] = DateTo&&
Discount[CustomerID] = selectedCustomer &&
Discount[AmountFrom]<=totalPriorSalesInDiscWindow +
InvoiceSales[Invoice Amount] &&
Discount[AmountTo] >= totalPriorSalesInDiscWindow +
InvoiceSales[Invoice Amount]
)
var DiscountRateForTopBracket =
CALCULATE(
MAX(Discount[Discount]),
DiscountTableForTopBracket
)
var MinAmountForTopBracket =
CALCULATE(
MAX(Discount[AmountFrom]),
DiscountTableForTopBracket
)
var ProgressiveDiscountForPriorBuckets =
CALCULATE(
SUMX(
Discount,
(Discount[AmountTo] -
IF(
Discount[AmountFrom]>totalPriorSalesInDiscWindow,
Discount[AmountFrom], totalPriorSalesInDiscWindow)
)
*Discount[Discount]
),
FILTER(
Discount,
Discount[EffectiveFrom] = DateFrom &&
Discount[EffectiveTo] = DateTo &&
Discount[CustomerID] = selectedCustomer &&
Discount[AmountTo] < MinAmountForTopBracket &&
Discount[AmountTo] > totalPriorSalesInDiscWindow
)
)
var ProgressiveDiscount =
ProgressiveDiscountForPriorBuckets +
IF(
totalPriorSalesInDiscWindow>MinAmountForTopBracket,
InvoiceSales[Invoice Amount],
(totalPriorSalesInDiscWindow +
InvoiceSales[Invoice Amount]-MinAmountForTopBracket)
)*DiscountRateForTopBracket
return ProgressiveDiscount
)
business intelligist 