New customers per service unit

[josimarjr]

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.

[v-cgao-msft]

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

[josimarjr]

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

[josimarjr]

Hello,

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

[Ajendra]

Hello,

Try this once!

[Wilson_]

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?)