New Customers counted based on conditions

[Zosy]

Hi,

I am trying to find all the new customers that match the below criteria:
- they haven't bought anything in the past 5 years
- they never bought merchandise on a pallet
- they never bought merchandise in a red box

I have created the measure below but it's not reurning the correct counts.

All new Customers = 
var customer = SELECTEDVALUE('Invoices'[customer name])

var LastPurchaseDate = SELECTEDVALUE('Invoices'[New Invoice Date])
            
var PreviousPurchaseDate = CALCULATE(
                MAX ( 'Date'[Date]  ), 
                FILTER(ALL('Invoices'), 'Invoices'[New Invoice Date] < LastPurchaseDate && 'Invoices'[customer name] = customer))

var PreviousPurchase = IF(ISBLANK(PreviousPurchaseDate),LastPurchaseDate,PreviousPurchaseDate)

var N = CALCULATE(DISTINCTCOUNTNOBLANK('Invoices'[customer name]),
    FILTER (
        'Invoices',
        DATEDIFF('Invoices'[New invoice Date] ,LastPurchaseDate,YEAR)>5 && 'Invoices'[Pallet] <>"Pallet" || 'Invoices'[Box Colour] <> "Red")

    )

VAR R = DISTINCTCOUNTNOBLANK('Invoices'[customer name])-N

return
N

 In 2017 there were 3 customers who purchased products, but only 1 is considered a new customer because the other two purchased a red box and a pallet.
In 2018 there were 0 new customers; same 2019-2022.
In 2023 there is 1 new customer.

I have uploaded the file in dropbox. Hope this helps
New Customer.pbix 

Thank you

[Zosy]

Thank you @Anonymous  ! Your help is very much appreciated!

[Anonymous]

Hi,@Zosy 
You are welcome!

[Anonymous · Accepted solution]

Hi,@Zosy I am glad to help you.
You can refer to my test below

you could check the result in the Dax queries
like this(you need to set the startDate and the endDate)
here is the test code:

EVALUATE
VAR _date1 =
    DATE ( 2018, 12, 31 )
VAR _date2 =
    DATE ( 2023, 12, 31 )
VAR _table =
    SUMMARIZE (
        FILTER (
            ALL ( Invoices ),
            'Invoices'[Box Colour] = "Red"
                || 'Invoices'[Pallet] = "Pallet"
        ),
        'Invoices'[invoice date],
        'Invoices'[New Invoice Date],
        'Invoices'[customer name],
        'Invoices'[Box Colour],
        'Invoices'[Pallet],
        "Year", YEAR ( 'Invoices'[New Invoice Date] )
    )
VAR _table1 =
    FILTER (
        'Invoices',
        'Invoices'[New Invoice Date] >= _date1
            && 'Invoices'[New Invoice Date] <= _date2
    )
VAR _table2 =
    SELECTCOLUMNS ( _table, 'Invoices'[customer name] )
VAR _table3 =
    SELECTCOLUMNS ( _table1, 'Invoices'[New Invoice Date] )
VAR _Red_pallet =
    SELECTCOLUMNS (
        FILTER ( _table, 'Invoices'[New Invoice Date] IN _table3 ),
        'Invoices'[customer name]
    )
RETURN
    FILTER ( _table1, NOT 'Invoices'[customer name] IN _Red_pallet )

I create three measures:

_date1=MAX(‘Date’[Date])

_date2 = EDATE([_date1],-60)
// the result pre 5 years

After my test, the information obtained from the slicer must be date instead of date/time. (During the final comparison, they need to conform to the type of 'Invoices'[New Invoice Date] in the table.) Otherwise, the result may be incorrect

Here is the final result

M_result =
VAR _table =
    SUMMARIZE (
        FILTER (
            ALL ( Invoices ),
            'Invoices'[Box Colour] = "Red"
                || 'Invoices'[Pallet] = "Pallet"
        ),
        'Invoices'[invoice date],
        'Invoices'[New Invoice Date],
        'Invoices'[customer name],
        'Invoices'[Box Colour],
        'Invoices'[Pallet],
        "Year", YEAR ( 'Invoices'[New Invoice Date] )
    )
VAR _table1 =
    FILTER (
        'Invoices',
        'Invoices'[New Invoice Date] >= [_date2]
            && 'Invoices'[New Invoice Date] <= [_date1]
    )
VAR _table2 =
    SELECTCOLUMNS ( _table, 'Invoices'[customer name] )
VAR _table3 =
    SELECTCOLUMNS ( _table1, 'Invoices'[New Invoice Date] )
VAR _Red_pallet =
    SELECTCOLUMNS (
        FILTER ( _table, 'Invoices'[New Invoice Date] IN _table3 ),
        'Invoices'[customer name]
    )
VAR _table4 =
    SUMMARIZE (
        FILTER ( _table1, NOT 'Invoices'[customer name] IN _Red_pallet ),
        'Invoices'[customer name]
    )
RETURN
    COUNTAX ( _table4, 'Invoices'[customer name] )

The SUMMARIZE function has the effect of removing duplicate values

To avoid direct filtering by the slicer that would affect the effect, I removed the relationship between the two tables and returned the result directly through M_result itself. The slicer only passed the value

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.