DAX Function: Customer Purchase Window

This guide outlines a DAX function that identifies customers who made a purchase within 1, 2, or 3 months following their previous purchase.

Objective

To create a measure in DAX that calculates the number of unique customers who made successful purchases within 1, 2, or 3 months from their last recorded purchase.

Necessary Imports

• This DAX code does not require special imports like some programming languages; it relies solely on the existing data model in Power BI.

Code Overview

Function Definition

Below is the DAX function for tracking customer purchases within the specified timeframe:

Purchases Within N Months = 
VAR SelectedMonths = 3   // Change this to 1 or 2 for different timeframes
RETURN
    CALCULATE(
        DISTINCTCOUNT(Sales[CustomerID]),
        FILTER(
            Sales,
            Sales[PurchaseDate] >= EDATE(
                CALCULATE(MAX(Sales[PurchaseDate]), 
                    FILTER(Sales, Sales[CustomerID] = EARLIER(Sales[CustomerID]))), 
                    SelectedMonths
                ) 
                && Sales[PurchaseDate] <= EDATE(
                    MAX(Sales[PurchaseDate]), SelectedMonths
                )
            )
        )
    )

Documentation Block

/**
 * Purchases Within N Months
 * 
 * This function calculates the number of distinct customers who made a purchase
 * within a specified number of months after their last purchase.
 * 
 * Parameters:
 *   SelectedMonths: Integer value to specify the number of months to look ahead (1, 2, or 3).
 * 
 * Returns:
 *   Integer: Count of unique customers who made subsequent purchases within the specified months.
 * 
 * Exceptions:
 *   None, but ensure that PurchaseDate and CustomerID exist in the Sales table.
 */

Input Validation

• Ensure that the SelectedMonths variable is set to a valid integer (1, 2, or 3).
• Verify that both PurchaseDate and CustomerID columns exist in the Sales table.

Code Explanation

• VAR SelectedMonths: Defines how many months ahead to search for subsequent purchases.
• CALCULATE(...): Utilizes the context to filter the Sales table.
• DISTINCTCOUNT(Sales[CustomerID]): Counts unique customers based on the filter criteria.
• FILTER(...): Applies the logic to determine if the purchase was made within the specified number of months.
• EDATE(...): Adjusts dates by adding months to the last purchase date.

Example Usage

To use this function in Power BI:

1. Place the function in the measure area of your Sales table.
2. Create a visual (e.g. card or table) that displays the measure.
3. Change the SelectedMonths variable to filter for 1, 2, or 3 months to see the distinct customer counts.

Example Scenario

• If SelectedMonths is set to 1, the measure will calculate the number of unique customers who made an additional purchase within one month of their last purchase.

Conclusion

This DAX measure allows you to effectively monitor customer purchasing behaviors and analyze customer retention strategies based on their purchase timelines. For in-depth learning, consider exploring the courses available on the Enterprise DNA Platform, which can further enhance your data analysis skills.

Feel free to adjust the measure according to your specific data model and requirements!