DAX Function for Counting Customers with Purchases Within 1-3 Months

This guide provides a DAX function to count customers who made purchases within 1-3 months of their previous purchase date while ensuring that only one order per month is counted.

Code Overview

The following code calculates the required customer count by leveraging DAX functions like CALCULATE, FILTER, and DISTINCT.

DAX Code

Count_Customers_1_To_3_Months = 
VAR PurchasesTable = 
    ADDCOLUMNS(
        Sales,  // Assuming Sales is the fact table with orders.
        "PurchaseMonth", MONTH(Sales[OrderDate]),
        "PurchaseYear", YEAR(Sales[OrderDate])
    )
VAR UniquePurchases = 
    SUMMARIZE(
        PurchasesTable,
        Sales[CustomerID],
        Sales[OrderDate],
        "YearMonth", FORMAT(Sales[OrderDate], "YYYY-MM")
    )
RETURN
    COUNTROWS(
        FILTER(
            UniquePurchases,
            COUNTROWS(
                FILTER(
                    UniquePurchases,
                    UniquePurchases[CustomerID] = EARLIER(UniquePurchases[CustomerID]) &&
                    UniquePurchases[OrderDate] >= EDATE(EARLIER(UniquePurchases[OrderDate]), 1) &&
                    UniquePurchases[OrderDate] <= EDATE(EARLIER(UniquePurchases[OrderDate]), 3)
                )
            ) > 0
        )
    )

Explanation

Key Components

1. PurchasesTable:

   • Uses ADDCOLUMNS to enrich the Sales table with month and year information for each purchase.

2. UniquePurchases:

   • Utilizes SUMMARIZE to generate a unique combination of CustomerID and OrderDate, creating a Year-Month format for each purchase.

3. Counting Logic:

   • FILTER checks if there are any other purchases by the same customer within the calculated date range (1-3 months).
   • EARLIER function retrieves the current row context to compare with other purchases made by the customer.

Input Validation

• Ensure that the Sales table contains valid OrderDate and CustomerID.
• Handle cases where there are no purchases or where dates might be invalid to avoid errors.

Usage Example

In Power BI, you can create a measure using the above DAX code. Assuming you have a table named Sales, which contains the purchase records with CustomerID and OrderDate.

1. Open the Power BI desktop.
2. Navigate to the Modeling tab.
3. Click on "New Measure".
4. Paste the provided DAX code into the measure editor.
5. Name the measure Count_Customers_1_To_3_Months.
6. Use this measure in your visualizations to display the count of customers who made repeat purchases within the specified timeframe.

Conclusion

This DAX function efficiently counts the number of distinct customers who made purchases within 1-3 months of their prior purchase, ensuring that only one purchase per month is accounted for in the count. For further learning on DAX and Power BI, consider exploring courses available on the Enterprise DNA Platform for advanced analytics and best practices.