New Customer measure

[NickProp28]

Dear Community,

Good day!

I have this measure which used to identify which clients are new based on the parameter (6 month) and also month & year of their ETD.

New Customers = //Cus
VAR CustomerTM = VALUES(Customer[Client])
VAR PriorCustomer = CALCULATETABLE(VALUES(Customer[Client]),
FILTER(ALL('Customer'),
'Customer'[ETD] > Min('Customer'[ETD] ) - Parameter[Parameter Value] && 'Customer'[ETD] < MIN('Customer'[ETD])))
return
COUNTROWS(
EXCEPT(CustomerTM,PriorCustomer))

My raw data:

There are no problems with the coding until the 'branch' slicer is added.

Based on my raw data, Nike should consider new customers for SIN branch in January, but for TYO branch in February.

But this code as a result, NIKE only had new customers on January, is there any part that I was missed?

Attached with pbix: https://drive.google.com/file/d/1mvW9opym6Ot2AlyZy0m0kl2rDeB4P2Wv/view?usp=sharing
Greatly appreciated any helps provided! Thank you!

[NickolajJessen]

I'm not really sure what result you are looing for, but i gave it a shot:

Please locate you file here:
https://drive.google.com/drive/folders/1AjePk7NUXAloXHr42BEa2lDOr5dQr2Wg

[NickProp28]

Dear @NickolajJessen ,

Appreciate your response, but the code doesn't work since my raw data do not have any March /April data

Thank you

[NickolajJessen]

Then i don't understand what your desired output is. The measure i provided shows the customers with entries within 180 days (parameter value) of the EOM of the selected date.

[NickProp28]

Hi @NickolajJessen ,

I consider new customers to be those that haven’t purchased anything in the last 180 days (Parmeter).

Expected outcome based on my raw data.

• When filter 'SIN' and ETD (2022, Jan) 

New customer only 'NIKE' (1/1/2022) - No past purchased last 180 day

• When filter 'SIN' and ETD (2022, Feb) 

New customer list 
LEEGASPKG (5/2/2022) - No past purchased last 180 day

PRIMANPET (2/2/2022) - No past purchased last 180 day

SABOXPKG (4/2/2022) - No past purchased last 180 day

SCCCPRKUL (5/2/2022) - Last purchased was 13/2/2021 , but already more than 180 days . So is consider new customer.

• When filter 'TYO' and ETD (2022, Jan)

No new customer

• When filter 'TYO' and ETD (2022, Feb)

New customer list 

NIKE - No past purchased last 180 day (Differentiate by branches, even there have purchased on SIN branch ,Jan) 

LEEGASPKG - No past purchased last 180 day

I hope I have clarified your doubts.

Thank you so much!

[NickolajJessen]

Ahh, we can just on my current path.
If it finds entries in the past 180 days, it should return blank, as it not counted as a new customer. If it doesn't find any entries in the past 180 we can do a countrows on the customer table. (Or a distinctcount, if we just want it to return 1)
I reuploaded the file and it produces your provided results:
https://drive.google.com/drive/folders/1AjePk7NUXAloXHr42BEa2lDOr5dQr2Wg

[latimeria · Accepted solution]

Hi @NickProp28 ,

Try  replacing filter(all...) with 

FILTER(ALLEXCEPT('Customer', 'Customer'[Branch]).

This will remove all filters but Branch and should be what you are looking for.

My favorite solution, using time intelligence : 

New Customers2 = 
//Cus
VAR CustomerTM = VALUES( 'Customer'[Client] )
VAR PriorCustomer =
	CALCULATETABLE(
		VALUES( 'Customer'[Client] ),
		ALLEXCEPT('Customer', 'Customer'[Branch]),
		DATESINPERIOD('Customer'[ETD].[Date], MIN('Customer'[ETD]) - [Parameter Value], [Parameter Value],DAY)
	)
RETURN
	COUNTROWS( EXCEPT( CustomerTM, PriorCustomer ) )