DAX Function to Calculate Total Sales for Top Salesperson in Last 3 Months

In this guide, we'll develop a DAX function that computes the total sales amount for the top-ranking salesperson over the last three months. Below you will find the complete solution, including necessary components like the imports, code snippets, and an example of how to use the function.

Function Overview

This DAX function will:

• Calculate total sales for each salesperson.
• Filter the last three months from the current date.
• Identify the top-ranking salesperson based on total sales.
• Return the total sales amount for that salesperson.

DAX Function Code

// This measure calculates total sales amount for the top salesperson in the last 3 months.
TopSalespersonLast3MonthsTotal = 
VAR Last3MonthsSales =
    CALCULATETABLE (
        SUMMARIZE (
            Sales,
            Sales[SalespersonID],
            "TotalSales", SUM ( Sales[SalesAmount] )
        ),
        Sales[SalesDate] >= EDATE ( TODAY(), -3 )
    )
VAR TopSalesperson =
    TOPN (
        1, 
        Last3MonthsSales, 
        [TotalSales], 
        DESC
    )
RETURN
    IF (
        NOT ISEMPTY ( TopSalesperson ),
        MAXX ( TopSalesperson, [TotalSales] ),
        0
    )

Code Explanation

1. Last3MonthsSales Variable:

   • Uses CALCULATETABLE to filter the Sales table.
   • Summarizes total sales by SalespersonID for the last three months using SUMMARIZE and SUM.

2. TopSalesperson Variable:

   • Identifies the top salesperson by using TOPN to get the highest TotalSales.

3. RETURN Statement:

   • Checks if TopSalesperson is not empty using NOT ISEMPTY.
   • Returns the total sales amount for the top-ranked salesperson using MAXX.
   • If no sales were recorded, it returns 0.

Input Validation

• The function inherently handles cases where there are no sales data entries within the last three months by returning 0.

Example Usage

To use this measure in Power BI to visualize the total sales amount for the top salesperson over the last three months, simply drag and drop TopSalespersonLast3MonthsTotal into a card visual.

Data Model Requirements

• Ensure you have a Sales table that includes at least the following columns:
  • SalesAmount (numeric, indicating the sale amount)
  • SalesDate (date type, indicating the date of each sale)
  • SalespersonID (identifier for each salesperson)

Conclusion

This DAX measure allows for easy identification of the top-performing salesperson in terms of sales over the past three months, providing valuable insights for sales performance analysis. For those seeking to improve their DAX skills and more functionalities, the Enterprise DNA Platform offers comprehensive courses and resources that can guide you in mastering DAX and making the most of your analytical capabilities.