Measure Formula for New Customers

Creating a measure formula for new customers involves calculating the number of distinct customers in a certain month and year who made their first purchase within the same period. The below DAX measure is a great way of achieving this without the use of calculated columns:

DAX Code

// Creating a measure for New Customers
NewCustomers :=
CALCULATE (
    DISTINCTCOUNT ( Sales[CustomerID] ), // Counting unique customers
    FILTER ( // Filtering the Sales table
        ALL ( Sales ), // Considering all rows irrespective of an existing filter 
        Sales[CustomerID] // Sales records for a Certain Customer   
        IN CALCULATETABLE ( // Creating a derived table 
            VALUES ( Sales[CustomerID] ), // Unique Customers
            FILTER ( 
                ALL ( Sales[Date] ), // Taking all dates ignoring the existing filter
                Sales[Date] <= MAX ( Sales[Date]) // Taking dates less than or equal to the maximum date which is the considered period (month and year context)
            ),
            Sales[SalesAmount] // Sales must be greater than zero
        )
    )
)

Code Explained

• The CALCULATETABLE function creates a derived table of unique customer who made their first purchase in the period context and reducing it by the condition that SalesAmount should be greater than zero
• The inner FILTER function removes the existing context on the Date column and takes dates less or equal to the maximum date
• The outer FILTER function on ALL Sales gets sales records for each Customer in derived table
• DISTINCTCOUNT counts the number of unique customers

Usage

This measure, NewCustomers, can be used in a report that provides monthly sales data, filtering on the month and year.

Unit testing

It's imperative that you validate the measure by testing it in different scenarios. Change the context by applying filters on different months/years of data. It should provide the number of new customers against the applied month/year context.

Further Learning You may take advantage of online resources and training courses on Enterprise DNA Platform to understand more about the DAX functions used and also the underlying principles of calculated tables, calculated columns, and context in Power BI.