DAX Function for Total Sales of Top Salesperson in Last 3 Months

Overview

This function calculates the total sales amount for the top-ranking salesperson over the last three months from the current date, using the existing Total Sales measure. The function leverages filtering and ranking techniques to identify the top salesperson based on sales performance.

Code Snippet

Here’s the DAX code for the required functionality:

Total Sales Last 3 Months for Top Salesperson = 
VAR Last3MonthsDateRange =
    DATESINPERIOD(
        Sales Quantity[Date],
        MAX(Sales Quantity[Date]),
        -3,
        MONTH
    )

VAR TopSalesPerson =
    TOPN(
        1,
        SUMMARIZE(
            FILTER(
                Users,
                Users[UserID] <> BLANK()
            ),
            Users[UserID],
            "Total Sales", [Total Sales Measure]
        ),
        [Total Sales],
        DESC
    )

VAR TopSalesPersonID =
    SELECTEDVALUE(TopSalesPerson[UserID])

RETURN
    CALCULATE(
        [Total Sales Measure],
        FILTER(
            Sales,
            Sales[UserID] = TopSalesPersonID &&
            Sales Quantity[Date] IN Last3MonthsDateRange
        )
    )

Code Explanation

• Last3MonthsDateRange: This variable captures the date range for the last three months based on the maximum date available in the Sales Quantity[Date] column.

• TopSalesPerson: This variable creates a summary table where it filters out users with a blank UserID and calculates total sales for each salesperson. The TOPN function then ranks these users by total sales in descending order, selecting only the top salesperson.

• TopSalesPersonID: Retrieves the UserID of the top salesperson from the previously defined variable.

• RETURN statement: The final calculation filters the Sales table to get total sales metrics for the identified top salesperson within the last three months.

Considerations

• Input Validation: Ensure that the Total Sales Measure and Sales Quantity[Date] are correctly defined elsewhere in your model. Invalid or blank UserID entries will not affect the outcome as they are filtered out.

• Performance: The use of SUMMARIZE and TOPN ensures that the calculation should perform efficiently. However, performance may vary based on the model’s complexity and size.

Usage Example

To use the function, simply create a new measure in your Power BI model with the provided DAX code. It will dynamically compute the total sales for the top salesperson whenever you apply the measure in a report context.

Example:

1. Add the measure to your report.
2. Use it in a card visual or table to display the top salesperson’s sales amount for the last three months.

Conclusion

This DAX measure provides a straightforward yet powerful way to analyze performance among salespersons, allowing organizations to reward top performers effectively. For more advanced DAX techniques and learning, consider exploring the Enterprise DNA Platform's rich educational resources.