New vs. Returning Customers DAX Pattern for E-commerce

1. Absolute (store-wide): First order date across the entire business defines new vs returning.
2. Relative (by category): First order in that product category defines new for that category.
3. Churn / win-back: Returning after a long gap (optional advanced pattern; uses similar date logic with a threshold).

Below we implement Patterns 1 and 2 on an Orders table with CustomerID, OrderDate, and Category.

Split logic into a hidden helper (internal) and a user-facing measure (external):

First Order Date (Internal) =
CALCULATE(
    MIN ( Orders[OrderDate] ),
    ALLEXCEPT ( Orders, Orders[CustomerID] )
)

New Customers =
CALCULATE(
    DISTINCTCOUNT ( Orders[CustomerID] ),
    FILTER (
        Orders,
        Orders[OrderDate] = [First Order Date (Internal)]
    )
)

Returning Customers =
CALCULATE(
    DISTINCTCOUNT ( Orders[CustomerID] ),
    FILTER (
        Orders,
        Orders[OrderDate] > [First Order Date (Internal)]
    )
)

First Order in Category =
CALCULATE(
    MIN ( Orders[OrderDate] ),
    ALLEXCEPT ( Orders, Orders[CustomerID], Orders[Category] )
)

New in Category =
VAR CurrentOrderDate = MAX ( Orders[OrderDate] )
VAR FirstInCat = [First Order in Category]
RETURN
    IF (
        CurrentOrderDate = FirstInCat,
        1,
        0
    )

• VAR CurrentOrderDate — captures the order date in the current filter context (the row being evaluated).
• VAR FirstInCat — earliest order for this customer in this category only.
• IF ( ... = FirstInCat, 1, 0 ) — flags 1 on the customer's first Electronics order even if they are returning in Clothing.

Sum or count this flag in a visual, or wrap with CALCULATE( DISTINCTCOUNT( Orders[CustomerID] ), ... ) for a category-level new customer count measure.