Code Generator | DAX

DAX Sales Filter for Weekly Performance

This DAX function filters sales data based on user-selected week, returning products with total sales over 100K. It ensures valid inputs and provides concise insights into top-performing products per selected week.

Prompt

Sales Table - SALES_DATE, PRODUCT_NAME, SALES_AMOUNT

Calendar Lookup table - CALENDAR_DATE, WEEK_NAME

Both tables are joined using SALES_DATE = CALENDAR_DATE.

Output: Depending on WEEK_NAME selected from Slicer, I want to display raw sales data for each product that has a total sales amount of more than 100K in a selected week. This means there may be a single transaction where a product has SALES_AMOUNT > 100K or multiple transactions across a week (MON-SUN) with total SALES_AMOUNT > 100K. 

Sample data: Below data should display all the green highlighted records. Suppose I am to filter by WEEK_NAME = WK05. This is because PROD_A and PROD_C both have total sales in WK05  > 100K.

Answer

DAX Function for Filtering Sales Data

Overview

This DAX function will filter records from the Sales table based on user selection of WEEK_NAME from a slicer. It will return raw sales data for each product with total sales exceeding 100K in the selected week.

Assumptions

The Sales table contains the columns: SALES_DATE, PRODUCT_NAME, and SALES_AMOUNT.
The Calendar table includes the columns: CALENDAR_DATE and WEEK_NAME.
The tables are properly related via SALES_DATE linked to CALENDAR_DATE.

DAX Function Definition

FilteredSalesData = 
VAR SelectedWeek =
    SELECTEDVALUE(Calendar[WEEK_NAME], "No Week Selected") // Get the selected week from the slicer.
VAR TotalSalesPerProduct =
    SUMMARIZE(
        FILTER(
            Sales,
            RELATED(Calendar[WEEK_NAME]) = SelectedWeek // Filter sales for the selected week.
        ),
        Sales[PRODUCT_NAME],
        "Total Sales", SUM(Sales[SALES_AMOUNT]) // Calculate total sales for each product.
    )
RETURN
    FILTER(
        TotalSalesPerProduct,
        [Total Sales] > 100000 // Return only those products with sales > 100K.
    )

Function Explanation

Input Variables:
- SelectedWeek: Captures the currently selected week from the slicer, defaulting to "No Week Selected" if none is chosen.
Table Creation:
- The SUMMARIZE function creates a summary table from the Sales table filtered to include only sales matching the selected week.
- It groups data by PRODUCT_NAME and calculates the total sales for each product.
Output:
- The FILTER function checks the summarized total sales, returning only those products with total sales greater than 100K.

Input Validation

If no WEEK_NAME is selected, the function will return an empty result, ensuring that no invalid or unwanted data is made visible.

Example Usage

In a reporting scenario, this DAX measure can be added to a visual such as a table. When a user selects WK05 from the slicer, the output will display all products with total sales exceeding 100K for that week.

Sample Data Illustration

Consider the following hypothetical sales data:

PRODUCT_NAME	SALES_DATE	SALES_AMOUNT
PROD_A	2023-02-01	70,000
PROD_A	2023-02-03	50,000
PROD_B	2023-02-04	30,000
PROD_C	2023-02-02	120,000
PROD_C	2023-02-05	40,000

With WK05 selected, the function will evaluate:

PROD_A: Total = 70,000 + 50,000 = 120,000 (included)
PROD_B: Total = 30,000 (excluded)
PROD_C: Total = 120,000 (included)

Conclusion

This DAX function efficiently filters sales data based on product sales exceeding a specified threshold for a selected week, allowing for quick insights into top-selling products. For deeper learning in DAX and advanced data analysis skills, consider checking the courses offered on the Enterprise DNA Platform.