topic Re: New customers per service unit in DAX Commands and Tips

New customers per service unit

josimarjr — Tue, 02 May 2023 18:24:44 GMT

I need to create a measure to identify new customers by service unit.

I made the measurement below, but in some cases the customer returns as new, but the customer has already bought it at another store in the group.

I need that when filtering the store, it is checked if the customer has already purchased from another store in the group. If the customer has a purchase in another store of the same group, do not consider it as new.

Filters used for return:
- Date of Purchase
- Service Unit

Qty_New_Customers =
COUNTROWS(
FILTER(
ADDCOLUMNS(VALUES(PURCHASES[Customer Code]),
"New clients",
CALCULATE(COUNTROWS(PURCHASE),FILTER(ALL(PURCHASE_DATE),PURCHASE_DATE[Purchase_Date]<MIN(PURCHASE_DATE[Purchase_Date])))),
[New Customers] = 0))

Ex: When filtering the month of April/2023 and unit B, the measure returns customer 1002732 as new (First Purchase), but in fact the customer had already purchased from another unit in the group in February/2023.

Customer_Code	Purchase_Code	Purchase_Date	Service_Unit
1002732	32620104	07/02/2023	Unit A
1002732	32620104	08/02/2023	Unit A
1002732	32620104	10/04/2023	Unit B

Thank you very much.

Re: New customers per service unit

Wilson_ — Wed, 03 May 2023 01:33:07 GMT

Hello,

Hard to know for sure this will work since you've shared nothing about your data model (that cannot be inferred from your measure), but what if you add a second filter parameter "REMOVEFILTERS ( Service_Unit )" to your "New Clients" CALCULATE function?

If that doesn't work, please share a pbix.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

Re: New customers per service unit

josimarjr — Wed, 03 May 2023 17:58:08 GMT

Hello,

all mentioned columns are in the same table or have relationship.

Re: New customers per service unit

Ajendra — Wed, 03 May 2023 20:10:03 GMT

Hello,

Try this once!

Re: New customers per service unit

Anonymous — Thu, 04 May 2023 02:39:46 GMT

Hi josimarjr ,

Please create a new measure.

New_Customers_Number = VAR _mindate = CALCULATE ( MIN ( 'PURCHASE_DATE'[Purchase_Date] ), ALL ( 'PURCHASE'[Service_Unit] ) ) VAR _table_1 = CALCULATETABLE ( VALUES ( 'PURCHASE'[Customer_Code] ), FILTER ( ALL ( 'PURCHASE_DATE' ), 'PURCHASE_DATE'[Purchase_Date] < _mindate ) ) VAR _table_2 = CALCULATETABLE ( VALUES ( 'PURCHASE'[Customer_Code] ), ALLSELECTED () ) VAR _result = COUNTROWS ( EXCEPT ( _table_2, _table_1 ) ) + 0 RETURN _result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data in the Power BI Forum

Re: New customers per service unit

josimarjr — Mon, 08 May 2023 12:33:06 GMT

Hello,

Thank you very much for his input, but it still didn't work out.

Using your example to meet my need, the customer should be considered as new on 07/02/2023 (date of first purchase).

I think there's just one small detail missing.

Customer_Code	Purchase_Date	Service_Unit	New_Customers_Number
1002732	07/02/2023 00:00	Unit A	1
1002732	08/02/2023 00:00	Unit A	0
1002733	15/04/2023 00:00	Unit A	1

Thank you very much