New vs. Returning Customers DAX Pattern for E-commerce

Three Scenarios

Absolute (store-wide): First order date across the entire business defines new vs returning.
Relative (by category): First order in that product category defines new for that category.
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.

Pattern 1: Absolute New vs Returning

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

Internal — First Order Date per Customer

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

External — New Customers Count

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

External — Returning Customers Count

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

First Order Date (Internal) uses ALLEXCEPT to ignore report date filters but keep the current customer. New Customers counts distinct customers whose order in context equals that first date; Returning Customers counts orders after that date.

Pattern 2: Relative (By Category)

First Order in Category

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

New in Category — Line by Line

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.

Three Scenarios

Pattern 1: Absolute New vs Returning

Pattern 2: Relative (By Category)

Download Free Power BI Resource