topic Re: Counting New Customers based on conditions in DAX Commands and Tips

Counting New Customers based on conditions

Zosy — Mon, 24 Jun 2024 15:37:25 GMT

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

Re: Counting New Customers based on conditions

Jihwan_Kim — Mon, 24 Jun 2024 17:35:35 GMT

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 )

Re: Counting New Customers based on conditions

Zosy — Tue, 25 Jun 2024 08:35:48 GMT

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.

Re: Counting New Customers based on conditions

Jihwan_Kim — Wed, 26 Jun 2024 02:56:59 GMT

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 )

Re: Counting New Customers based on conditions

Zosy — Wed, 26 Jun 2024 09:36:31 GMT

Thank you Jihwan_Kim ! Your help is very much appreciated!