DAX Function for Weekly Sales Line Chart

This document outlines how to create a DAX measure that computes the total sales for each week leading up to the selected week. This measure can be useful for visualizations, such as line charts, where you want to analyze trends over time.

Measure Definition

Purpose

To create a DAX measure that calculates total sales per week and can dynamically adjust to display sales data only up to the selected week in a line chart.

Parameters

• SalesAmount: Numeric value representing the total sales amount.
• DateColumn: Date field to evaluate the sales based on time.

Return Type

The function returns a numeric value representing total sales aggregated by weeks.

Code Snippet

Weekly Sales Until Selected Week = 
VAR SelectedDate = MAX('Date'[Date])  // Capturing the latest date selected by the user
VAR StartOfWeek = SelectedDate - WEEKDAY(SelectedDate, 2) + 1  // Finding the start of the current week (Monday)
RETURN
    // Calculate the total sales from the start of the current week to the maximum selected date
    CALCULATE(
        SUM('Sales'[SalesAmount]),  // Aggregate the sales amount
        FILTER(
            ALL('Date'),  // Remove filters on Date table
            'Date'[Date] <= SelectedDate &&  // Up to the selected date
            'Date'[Date] >= StartOfWeek  // Start from the beginning of the current week
        )
    )

Explanation of the Code

• MAX('Date'[Date]): Retrieves the most recent date selected in the report context to leverage in calculations.
• WEEKDAY(SelectedDate, 2): Determines the day-of-week for the selected date, treating Monday as the first day, allowing the calculation of the week’s start.
• CALCULATE: Changes the context of the calculation to aggregate total sales within the specified date range.
• FILTER(ALL('Date'), ...): Ensures we consider the entire date range by removing any existing filters on the Date table while applying new filters.

Input Validation

Since DAX performs well with its handling of contexts, there isn't a need for extensive input validation, but ensure the following:

• The Date table must be correctly set up with continuous date values.
• The Sales table must have a column named SalesAmount for the aggregation to work.

Use Case Example

In a Power BI report, you can implement this measure to visualize weekly sales data:

1. Create a line chart visualization.
2. Set the X-axis to the Date field from the Date table.
3. Set the Y-axis to the measure Weekly Sales Until Selected Week.
4. Add a slicer for the Date field to allow users to select a week range.

With this setup, you will see a line chart reflecting sales trends week by week, dynamically updating as users select different periods.

Conclusion

This DAX measure provides an efficient method to analyze weekly sales data up to a specified week. By utilizing context and dynamic calculations, it can offer insights into business performance over time.

For further learning on DAX and Power BI, you may consider exploring the courses available on the Enterprise DNA Platform to enhance your skills.