Counting New Customers 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 returning the correct counts.

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 Customers.pbix 

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

 


Thank you

[Zosy]

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

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

All new Customers V2: =
VAR _customerlistcurrentyear =
    VALUES ( Invoices[customer name] )
VAR _customerlistpreviousfiveyears =
    CALCULATETABLE (
        VALUES ( Invoices[customer name] ),
        WINDOW ( -5, REL, -1, REL, ALL ( 'Date'[Year] ), ORDERBY ( 'Date'[Year], ASC ) )
    )
VAR _condition =
    CALCULATETABLE (
        VALUES ( Invoices[customer name] ),
        'Invoices'[Pallet] = "Pallet"
            || 'Invoices'[Box Colour] = "Red"
    )
VAR _newcustomerlist =
    EXCEPT ( _customerlistcurrentyear, _customerlistpreviousfiveyears )
VAR _withoutcondition =
    EXCEPT ( _newcustomerlist, _condition )
RETURN
    COUNTROWS ( _withoutcondition )

[Zosy]

Hi @Jihwan_Kim ,

The measure works great, thank you! I stumbled across another issue with it. If the user selects the year from the slicer on the page , the count is not accurate anymore? Do you know if there is a way to fix this?


Your help is very much appreciated.

[Jihwan_Kim · Accepted solution]

Hi,

Please try something like below.

All new Customers V2: =
VAR _customerlistcurrentyear =
    VALUES ( Invoices[customer name] )
VAR _customerlistpreviousfiveyears =
    CALCULATETABLE (
        VALUES ( Invoices[customer name] ),
        WINDOW ( -5, REL, -1, REL, ALL ( 'Date'[Year] ), ORDERBY ( 'Date'[Year], ASC ) ),
        REMOVEFILTERS ( 'Date' )
    )
VAR _condition =
    CALCULATETABLE (
        VALUES ( Invoices[customer name] ),
        WINDOW ( -5, REL, 0, REL, ALL ( 'Date'[Year] ), ORDERBY ( 'Date'[Year], ASC ) ),
        REMOVEFILTERS ( 'Date' ),
        'Invoices'[Pallet] = "Pallet"
            || 'Invoices'[Box Colour] = "Red"
    )
VAR _newcustomerlist =
    EXCEPT ( _customerlistcurrentyear, _customerlistpreviousfiveyears )
VAR _withoutcondition =
    EXCEPT ( _newcustomerlist, _condition )
RETURN
    COUNTROWS ( _withoutcondition )