DAX Function for Weekly Sales Visualization

This document outlines the creation of a DAX measure to visualize weekly sales data in a line chart, with an option to limit the display to a specified end date.

Purpose

The goal is to create a DAX measure that calculates weekly sales totals, allowing for dynamic filtering by an end date.

DAX Code

Below is the DAX function to calculate weekly sales and limit the data until a certain date.

WeeklySales = 
VAR EndDate = DATE(2023, 10, 31) // Define the end date to limit data
RETURN
    CALCULATE(
        SUM(Sales[SalesAmount]), // Calculate total sales
        FILTER(
            ADDCOLUMNS(
                VALUES(Sales[OrderDate]), // Get distinct order dates
                "WeekStart", STARTOFWEEK(Sales[OrderDate]) // Calculate week start for each date
            ),
            [WeekStart] <= EndDate // Filter to only include weeks up to EndDate
        )
    )

Code Explanation

• EndDate: A variable that defines the last date for which sales data should be displayed. You can modify the date as necessary.
• CALCULATE: This function changes the context in which data is evaluated.
• SUM(Sales[SalesAmount]): This aggregates the total sales amount from the Sales table.
• FILTER: It filters the data based on specific conditions.
• ADDCOLUMNS: This function adds a calculated column that gives the start of the week for each order date.
• STARTOFWEEK(Sales[OrderDate]): This retrieves the start date of the week for each order date.
• Filtered Condition: Checks if the calculated week start date is less than or equal to EndDate.

Practical Usage Example

To use this measure in your line chart visualization:

1. Add the WeeklySales measure to the values of your line chart.
2. Set the X-axis to the WeekStart calculated column or any equivalent date representation you have.
3. Optionally, add slicers or filters for enhanced interactivity, such as selecting different products, regions, or categories.

Additional Considerations

• Ensure that your Sales table contains the correct columns (SalesAmount and OrderDate).
• Adjust the EndDate dynamically based on user input or a slicer to allow for flexibility.
• This measure can enhance dashboards by providing a clear view of sales trends over time.

Conclusion

This DAX measure provides a straightforward yet powerful way to visualize weekly sales data limited by a specified date. For further learning on DAX and data modeling, consider the courses available on the Enterprise DNA Platform.