Complex discount calculation ( time period,customer,products)

DAX DAX Calculations
11

Hi @Roger,

Welcome to the Forum!

I’ve marked your Calendar table as a Date table (you’ll find that option on the Table Tools ribbon).
As a result all automatic generated hidden date tables get removed and some of your calculations broke because you leveraged that hidden table, easy fix just remove the suffix .[Date] and it’s sorted.

Created a few measures

Sales Top customers = 
CALCULATE( SUM(SALESDATA[sales]),
    FILTER( ALL( SALESDATA ),
        SALESDATA[customer] IN {1, 2, 3} &&
        SALESDATA[product] IN {1, 2, 3}
    ),
    DATESBETWEEN('CALENDAR'[Date], DATE(2020,9,01), DATE(2021,03,31)),
    VALUES( CUSTOMERS[CUSTOMER-id] ),
    VALUES( PRODUCTS[PRODUCT-id] )
)

and

Sales Other customers = 
CALCULATE( SUM(SALESDATA[sales]),
    FILTER( ALL( SALESDATA ),
        NOT( SALESDATA[customer] ) IN {1, 2, 3} &&
        NOT( SALESDATA[product] ) IN {1, 2, 3}
    ),
    DATESBETWEEN('CALENDAR'[Date], DATE(2020,9,01), DATE(2021,03,31)),
    VALUES( CUSTOMERS[CUSTOMER-id] ),
    VALUES( PRODUCTS[PRODUCT-id] )
)

.

Added a Discount table, that is a supporting table so it has no relationships in your model

image

Created another measure

Top Customers Discount Amount = 
VAR myCust = 
    IF( VALUES( CUSTOMERS[CUSTOMER-id] ) IN { 1, 2, 3},
        FORMAT( SELECTEDVALUE( CUSTOMERS[CUSTOMER-id] ), "@"),
        "Other"
    )
RETURN

CALCULATE( MAX( Discount[Discount] ),
    FILTER(
        VALUES( CUSTOMERS[CUSTOMER-id] ),
            COUNTROWS(
                FILTER( Discount,
                    myCust = Discount[Customer] &&
                    [Sales Top customers] >= Discount[LBound] &&
                    [Sales Top customers] < Discount[UBound]
                )
            ) > 0
    )
) * [Sales Top customers]

This matches your expected results

image
image763×177 5.59 KB

Here’s your sample file.eDNA - discount rate calc.pbix (1.5 MB)
I hope this is helpful.
.

For your reference @Greg did a post on the dynamic segmentation technique, you’ll find that here

2 Likes