DAX Function to Determine Most Recent Customer by Account and Date

This guide provides a DAX function that summarizes customer data to identify the most recent customer for each account based on the date.

Objectives

1. Use SUMMARIZE to group data by account.
2. Retrieve the most recent date for each account.
3. Return the corresponding customer information associated with that date.

DAX Function

Code Snippet

MostRecentCustomer = 
VAR RecentCustomers = 
    SUMMARIZE(
        'CustomerTable',
        'CustomerTable'[AccountID],
        "MostRecentPurchaseDate", MAX('CustomerTable'[PurchaseDate]),
        "RecentCustomer", FIRSTNONBLANK('CustomerTable'[CustomerName], 1)
    )
RETURN
    ADDCOLUMNS(
        RecentCustomers,
        "CustomerDetails",
        LOOKUPVALUE(
            'CustomerTable'[CustomerName],
            'CustomerTable'[PurchaseDate], [MostRecentPurchaseDate],
            'CustomerTable'[AccountID], [AccountID]
        )
    )

Explanation

1. SUMMARIZE Function:

   • Groups the CustomerTable by AccountID.
   • Calculates the MostRecentPurchaseDate for each account using the MAX function.
   • Uses FIRSTNONBLANK to get a placeholder for the customer name (this will be updated later).

2. ADDCOLUMNS Function:

   • Enriches the summarized table by utilizing LOOKUPVALUE to fetch the correct customer details based on the most recent purchase date.
   • Looks up the CustomerName for combinations of AccountID and PurchaseDate.

3. RETURN Statement:

   • Returns a new table that contains the account IDs, most recent purchase dates, and the corresponding customer details.

Input Validation

• Ensure the CustomerTable contains AccountID, PurchaseDate, and CustomerName columns.
• Check for non-null values in the fields utilized for summaries and lookups to avoid unexpected results.

Usage Example

Suppose you have a customer dataset structured as follows:

After applying the MostRecentCustomer function, the resulting table will be:

Conclusion

This function efficiently summarizes customer purchase data to find the most recent customer per account, leveraging DAX’s powerful aggregation and lookup capabilities. For further enhancement in DAX skills, consider exploring courses on the Enterprise DNA platform.