If you are looking to challenge your DAX skills while trying to figure out how to calculate a Cumulative Discount (or a Cumulative Commission) then this tutorial is for you.
A Cumulative discount is calculated by finding the cumulative sales in the specified discount window, and then, using the cumulative sales as the parameter to find the appropriate discount percent.
You can see in the table above that Customer 1 has a discount window from 9/1/2019 to 3/31/2020, and depending on where the cumulative sales fall in that window, the customer might enjoy a discount from 0 to 10%, 15% or 20%
The logic used in this tutorial goes like this, for every row in the invoice table:
- Try to find if the current invoice has a corresponding discount window in the discount table
- If yes, calculate Date From and Date To for that window
- Then calculate the cumulative sales for this customer in that discount window
- Then use that cumulative sales to lookup the corresponding discount % that matches the customer and the invoice rate
You can download the Power BI Model used in this tutorial here…
More details are in the video above and the DAX calculations are below:
Inv Discount Date From =
var selectedDate = SELECTEDVALUE(InvoiceSales[Invoice Date])
var selectedCustomer = SELECTEDVALUE(InvoiceSales[CustomerID])
RETURN
CALCULATE(
MAX(Discount[EffectiveFrom]),
FILTER(
Discount,
(Discount[CustomerID] = selectedCustomer ||
Discount[CustomerID] = -1
)
&& Discount[EffectiveFrom]<=selectedDate
&& Discount[EffectiveTo] >= selectedDate
)
)
Inv Discount Date To =
var selectedDate = SELECTEDVALUE(InvoiceSales[Invoice Date])
var selectedCustomer = SELECTEDVALUE(InvoiceSales[CustomerID])
RETURN
CALCULATE(
MIN(Discount[EffectiveTo]),
FILTER(
Discount,
(Discount[CustomerID] = selectedCustomer || Discount[CustomerID] = -1)
&& Discount[EffectiveFrom]<=selectedDate
&& Discount[EffectiveTo] >= selectedDate
)
)
Inv Cumulative Sales =
var selectedCustomer = SELECTEDVALUE(InvoiceSales[CustomerID])
RETURN
IF(
ISBLANK([Inv Discount Date From]) ||
ISBLANK([Inv Discount Date To]),
BLANK(),
CALCULATE(
[Gross Sales],
FILTER(
ALL(InvoiceSales),
InvoiceSales[CustomerID] = selectedCustomer &&
InvoiceSales[Invoice Date] >= [Inv Discount Date From] &&
InvoiceSales[Invoice Date] <= [Inv Discount Date To]
)
)
)
Inv Discount % =
var DateFrom = [Inv Discount Date From]
var DateTo = [Inv Discount Date To]
var CumulativeSales = [Inv Cumulative Sales]
var selectedCustomer = SELECTEDVALUE(InvoiceSales[CustomerID])
RETURN
IF(
NOT(ISBLANK(DateFrom)||ISBLANK(DateTo)),
CALCULATE(
MAX(Discount[Discount]),
FILTER(
Discount,
Discount[EffectiveFrom]=DateFrom &&
Discount[EffectiveTo]=DateTo &&
(Discount[CustomerID] = selectedCustomer ||
Discount[CustomerID] = -1
) &&
Discount[AmountFrom]<=CumulativeSales &&
Discount[AmountTo] >= CumulativeSales
)
)
)
Gross Sales = SUM(InvoiceSales[Invoice Amount])
Effective Discount % =
var d = 1- DIVIDE([Net Sales], [Gross Sales])
return IF(ISBLANK(d) || ISBLANK([Gross Sales]), BLANK(), d)
business intelligist 
Hi,
Really good tutorials, thanks a lot!
I am working through the different calculations to integrate the logic and I have a question: how to apply the rates based on the accumulation per month and not per invoice.
See you soon!
Hi Thank you for this post , I have question , My DB structure is similer to you but with small difference :
1-In Discount table each discount have unique Id and forigen key in (InvoiceSales).
2-Customer Id available only in (InvoiceSales) but not in (discount) , how we can deal with this case in looping?