New Customers Calculation in DAX

Explanation of the NewCustomers Code Snippet

The provided code snippet is written in DAX (Data Analysis Expressions), which is primarily used in Power BI, SQL Server Analysis Services, and Power Pivot in Excel. Below is a breakdown of the code:

Objective

The code is designed to calculate the list of new customers for the current month, isolating those who did not purchase in the previous month.

Code Breakdown

Variables

VAR CurrentMonth = MAX(Sales[Date])
VAR PreviousMonth = EDATE(CurrentMonth, -1)

1. CurrentMonth: This variable calculates the maximum date in the Sales[Date] column, which represents the latest date in the dataset, thereby identifying the current month.
2. PreviousMonth: This variable uses the EDATE function to subtract one month from the CurrentMonth date, yielding the date for the previous month.

Return Statement

The RETURN statement encapsulates the main calculation, which is structured as follows:

RETURN
CALCULATETABLE(
    VALUES(Sales[CustomerID]),
    FILTER(Sales, Sales[Date] = CurrentMonth),
    EXCEPT(
        VALUES(Sales[CustomerID]),
        CALCULATETABLE(
            VALUES(Sales[CustomerID]),
            FILTER(Sales, Sales[Date] = PreviousMonth)
        )
    )
)

1. CALCULATETABLE: This function modifies a table expression in the context of new filters; it is used here to adjust the context for our calculations.
2. VALUES(Sales[CustomerID]): Extracts a list of unique customer IDs from the Sales table.
3. FILTER(Sales, Sales[Date] = CurrentMonth): Filters the Sales table to include only records with dates in the CurrentMonth.
4. EXCEPT: This function returns a table (or a set) from the first table that doesn’t match with the second table. The general structure is EXCEPT(Table1, Table2).

Internal CALCULATETABLE

CALCULATETABLE(
    VALUES(Sales[CustomerID]),
    FILTER(Sales, Sales[Date] = PreviousMonth)
)

This nested CALCULATETABLE filters the Sales table to include only records with dates in the PreviousMonth, then extracts a list of unique customer IDs.

Combined Logic

1. VALUES(Sales[CustomerID]) with FILTER(Sales, Sales[Date] = CurrentMonth): Produces a list of customers who made purchases in the current month.
2. EXCEPT: Subtracts the list of customers who made purchases in the previous month from the list of customers who made purchases in the current month.

Result

The result of the EXCEPT function is a list of CustomerIDs who made purchases in the current month but did not make any purchases in the previous month — effectively identifying new customers for the current month.

Conclusion

This code is an efficient way to dynamically identify new customers month-over-month. It harnesses DAX's capabilities to handle context and perform set-based operations effectively.

For a deeper understanding of DAX and its functions, courses available on the Enterprise DNA platform can be exceptionally helpful.