Progressive Discount (Tax or Commission) Calculation (Power BI DAX Tutorial)

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
    
)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s