DAX Function for Total Sales of Top Salesperson (Last 3 Months)

Overview

This DAX function calculates the total sales amount for the top-ranking salesperson over the last three months using an existing Total Sales measure. It utilizes filtering on dates to limit the data scope to the last three months and ranks salespeople based on their performance.

Function Definition

TopSalespersonLast3Months = 
VAR Last3Months =
    CALCULATE (
        MAX ( Sales[Date] ),
        PREVIOUSMONTH ( TODAY () )
    )
VAR TopSalesperson =
    TOPN (
        1,
        SUMMARIZE (
            SalesPerson,
            SalesPerson[Name],
            "TotalSales", [Total Sales]
        ),
        [TotalSales], 
        DESC
    )
RETURN
    CALCULATE (
        [Total Sales], 
        SalesPerson[Name] IN TopSalesperson,
        Sales[Date] >= EDATE ( TODAY(), -3 )
    )

Explanation of Code Sections

1. Last 3 Months Calculation

   • The Last3Months variable calculates the maximum date within the previous three months of data.

2. Top Salesperson Identification

   • The TopSalesperson variable employs the TOPN function, ranking the salespeople based on their total sales amount. Here, SUMMARIZE aggregates the sales data by each salesperson.

3. Total Sales Calculation

   • The CALCULATE function is utilized to extract the total sales for the identified top salesperson while filtering the data to include only sales from the last three months.

Input Validation

• Ensure that the Sales Quantity[Date] column in your fact table has been correctly set as a date data type to avoid any calculations errors.

Comments

• Each significant step has been documented through comments in the code to enhance readability and maintainability.

Code Usage Example

Assuming you have the following measures and tables:

• A measure named [Total Sales] in a Sales context.
• A SalesPerson table containing the names of the salespersons.

You can leverage the function TopSalespersonLast3Months in your report visualizations to display the total sales value of the top salesperson dynamically over the past three months.

Considerations

• Performance: The use of TOPN should be efficient if the dataset is properly indexed and limited to relevant dimensions.
• Scalability: The function can scale as long as you add additional salespersons to the SalesPerson table.

Conclusion

This structured approach allows for a clear and efficient calculation of total sales for the top-ranking salesperson over the last three months, adhering to best DAX practices. To further enhance your DAX skills, consider exploring courses on the Enterprise DNA Platform that focus on DAX and data modeling techniques.