Customer Monthly Movement Calculation in DAX

Description

To calculate the movement of customers based on volumes on a month-by-month basis, a DAX formula can be crafted to track whether a customer is new, retained, or lost in a given month compared to the previous month.

Formula Explanation

The following DAX formulas calculate the customer movement:

1. Calculate Total Volume: First, compute the total volume for each customer.

2. Identify Returning Customers: Determine whether the customer has volumes in both the current and the previous month.

3. Identify New Customers: Identify customers who have volumes in the current month but none in the previous month.

4. Identify Lost Customers: Identify customers who had volumes in the previous month but none in the current month.

DAX Formulas

Calculate Total Volume

TotalVolume = SUM(Sales[Volume])

Identify Returning Customers

ReturningCustomers = 
VAR CurrentMonth = MAX(Sales[Date])
VAR PreviousMonth = EDATE(CurrentMonth, -1)
RETURN
CALCULATETABLE(
    VALUES(Sales[CustomerID]),
    FILTER(Sales, Sales[Date] = CurrentMonth),
    INTERSECT(
        VALUES(Sales[CustomerID]),
        CALCULATETABLE(
            VALUES(Sales[CustomerID]),
            FILTER(Sales, Sales[Date] = PreviousMonth)
        )
    )
)

Identify New Customers

NewCustomers = 
VAR CurrentMonth = MAX(Sales[Date])
VAR PreviousMonth = EDATE(CurrentMonth, -1)
RETURN
CALCULATETABLE(
    VALUES(Sales[CustomerID]),
    FILTER(Sales, Sales[Date] = CurrentMonth),
    EXCEPT(
        VALUES(Sales[CustomerID]),
        CALCULATETABLE(
            VALUES(Sales[CustomerID]),
            FILTER(Sales, Sales[Date] = PreviousMonth)
        )
    )
)

Identify Lost Customers

LostCustomers = 
VAR CurrentMonth = MAX(Sales[Date])
VAR PreviousMonth = EDATE(CurrentMonth, -1)
RETURN
CALCULATETABLE(
    VALUES(Sales[CustomerID]),
    FILTER(Sales, Sales[Date] = PreviousMonth),
    EXCEPT(
        VALUES(Sales[CustomerID]),
        CALCULATETABLE(
            VALUES(Sales[CustomerID]),
            FILTER(Sales, Sales[Date] = CurrentMonth)
        )
    )
)

Practical Example

Consider a dataset with the following sales records:

Applying the above formulas would yield:

• Returning Customers in February: C001
• New Customers in February: C003
• Lost Customers in February: C002

This approach allows for a dynamic calculation of customer movements based on volumes, providing insights into the retention, acquisition, and churn of customers over time.

For further learning and mastery of DAX, consider exploring comprehensive courses available on the Enterprise DNA Platform.