Monthly Customer Movement Analysis in DAX

Formula for Calculating Customer Monthly Movements in DAX

Problem Statement

The task is to calculate customer movements (New, Retained, Lost) on a monthly basis based on volume.

Definitions and Assumptions

• New Customer: A customer who appears in the current month but did not appear in the previous month.
• Retained Customer: A customer who appears in both the current month and the previous month.
• Lost Customer: A customer who appeared in the previous month but does not appear in the current month.

DAX Formulas

New Customers

To calculate the number of new customers:

New Customers = 
CALCULATE (
    COUNTROWS ( Customers ),
    NOT ( 
        COUNTROWS (
            FILTER (
                ALL ( Customers ),
                Customers[Month] = EARLIER ( Customers[Month] ) - 1
                    && Customers[CustomerID] = EARLIER ( Customers[CustomerID] )
            )
        )
    )
)

Explanation

• EARLIER ( Customers[Month] ) - 1 fetches data from the previous month.
• FILTER ensures that we only consider customers who are not present in the previous month.

Retained Customers

To calculate the number of retained customers:

Retained Customers = 
CALCULATE (
    COUNTROWS ( Customers ),
    COUNTROWS (
        FILTER (
            ALL ( Customers ),
            Customers[Month] = EARLIER ( Customers[Month] ) - 1
                && Customers[CustomerID] = EARLIER ( Customers[CustomerID] )
        )
    )
)

Explanation

• The FILTER function finds customers who appear in both the current and the previous month.

Lost Customers

To calculate the number of lost customers:

Lost Customers = 
CALCULATE (
    COUNTROWS ( Customers ),
    NOT (
        COUNTROWS (
            FILTER (
                ALL ( Customers ),
                Customers[Month] = EARLIER ( Customers[Month] ) 
                    && Customers[CustomerID] = EARLIER ( Customers[CustomerID] )
            )
        )
    ),
    Customers[Month] = EARLIER ( Customers[Month] ) - 1
)

Explanation

• The FILTER function identifies customers who were present in the previous month but not in the current month.

Practical Illustration

Imagine you have a table named Customers with columns CustomerID and Month. These formulas will accurately categorize customers into new, retained, and lost for each month.

Additional Notes

To further your understanding of advanced DAX concepts, consider exploring courses and resources available on the Enterprise DNA Platform.

Conclusion

These DAX formulas offer a structured and precise method for calculating monthly customer movements. By implementing these formulas, you can monitor and analyze customer retention, acquisition, and attrition effectively.