Complex discount calculation ( time period,customer,products)

DAX DAX Calculations
21

Hi @Roger

I have used a slightly different approach. Solution will fulfill only the required request and will need tweaks for any other scenarios. Please find details below.

  1. Created a new Column in Calendar Table. This is to Bound Periods into Sets i.e. from Sep to Mar (Next Year).

DiscountPeriod = if('CALENDAR'[MonthOfYear] in {4,5,6,7,8},BLANK(),if('CALENDAR'[MonthOfYear] in {1,2,3},"DY" & 'CALENDAR'[Year],"DY" & 'CALENDAR'[Year] + 1))

  1. To calculate sales for only specific products and Cumulative value, I have used below measures.

TotalSales_Top3Product = CALCULATE([total Sales],PRODUCTS[PRODUCT-id] in {1,2,3})

> CumulativeAmount =
> IF (ISBLANK ( MAX ( 'CALENDAR'[DiscountPeriod] ) )  || ISBLANK ( [TotalSales_Top3Product] ), BLANK (),
>     CALCULATE ([TotalSales_Top3Product],
>         FILTER (ALL ( 'CALENDAR' ),'CALENDAR'[Date] <= MAX ( 'CALENDAR'[Date] )
>                 && 'CALENDAR'[DiscountPeriod] = MAX ( 'CALENDAR'[DiscountPeriod] ))))
  1. Final Step to calculate Discount
Cumulative Discount = 
var myCust = convert(SELECTEDVALUE( CUSTOMERS[CUSTOMER-id] ),STRING)
Var CummValue = [CumulativeAmount]
Var CummLM = if(isblank(max('CALENDAR'[DiscountPeriod])),BLANK(),CALCULATE([CumulativeAmount],PREVIOUSMONTH('CALENDAR'[Date])))
Var ThisMonthDiscount = FILTER( Discount,Discount[Customer] = myCust && Discount[LBound] < CummValue && Discount[UBound] > CummValue)
Var LMDiscount = FILTER( Discount,Discount[Customer] = myCust && Discount[LBound] < CummLM && Discount[UBound] > CummLM)
Var ThisMonthLB = CALCULATE(max(Discount[LBound]),ThisMonthDiscount)
Var ThisMonthUB = CALCULATE(max(Discount[UBound]),ThisMonthDiscount)
Var ThisMonthDR = CALCULATE(max(Discount[Discount]),ThisMonthDiscount)
Var LastMonthLB = CALCULATE(max(Discount[LBound]),LMDiscount)
Var LastMonthUB = CALCULATE(max(Discount[UBound]),LMDiscount)
Var LastMonthDR = CALCULATE(max(Discount[Discount]),LMDiscount)

return
if(ISBLANK(CummLM),(CummValue - ThisMonthLB) * ThisMonthDR,if(ThisMonthDR = LastMonthDR,[TotalSales_Top3Product] * ThisMonthDR,((CummValue - ThisMonthLB) * ThisMonthDR)  + (ThisMonthLB - CummLM) * LastMonthDR))

Final Output

image

Attached the Solution Ankit - discount rate calc.pbix (1.5 MB)

Few things.

  1. Currently it will work for Single Customer. If required considering all Customer, then will require changes.
  2. In case you want to show Totals also, then there are measures with suffix “Total”.

Thanks
Ankit J

1 Like