DAX Function to Show Weekly Sales Until a Calculated Date

This guide outlines how to create a DAX measure to visualize weekly sales in a line chart, up to a specified calculated date.

Objective

To calculate and display total sales for each week, allowing users to visualize performance over time up until a given date.

Code Implementation

DAX Measure Creation

The following DAX measure calculates the weekly sales up to a specified date. Replace Sales[Amount] with your actual sales amount column name, and Sales[Date] with the appropriate date column.

WeeklySalesUntilDate = 
VAR CalDate = SELECTEDVALUE('DateTable'[CalculatedDate])  // Get the calculated date
RETURN
    CALCULATE(
        SUM(Sales[Amount]),  // Sum sales amount
        FILTER(
            ALL('DateTable'),  // Use all dates in the date table
            'DateTable'[Date] <= CalDate && 
            'DateTable'[Date] >= CalDate - WEEKDAY(CalDate, 2)  // Filter to the current week
        )
    )

Explanation

1. Variable Declaration:

   • CalDate captures the selected calculated date from a date table.

2. Calculate Function:

   • CALCULATE modifies the context of the calculation.
   • It sums up Sales[Amount].

3. Filtering Logic:

   • FILTER(ALL('DateTable'),...) ensures that all dates are considered.
   • The condition ensures we only include sales for the week leading up to the CalDate.

Usage

To use this measure in a Power BI report:

1. Create a date table named DateTable that includes a column for dates and a column for the calculated date you want to analyze against.
2. Add a line chart visual to your report.
3. Set the X-axis to your DateTable[Date] (ensure it’s formatted for weeks).
4. Drag the WeeklySalesUntilDate measure to the Y-axis.

Example Scenario

Suppose you have a sales table with sales data from 2020 to present. You want to analyze total weekly sales until October 1, 2023.

1. Create a date slicer with a maximum limit of October 1, 2023.
2. Select October 1, 2023.
3. The line chart will now display cumulative sales for each week, factoring all sales up until October 1, 2023.

Notes

• Ensure your DateTable has continuous dates without gaps for accurate calculations.
• Adjust the WEEKDAY function's second argument if you want to change the start day of the week.
• You can customize the measure further to incorporate specific filters or conditions based on your data model.

Conclusion

This DAX measure provides a robust method for tracking weekly sales performance up to a specified date, enabling insightful analysis in your Power BI reports. For further enhancement of your DAX skills, consider exploring resources on the Enterprise DNA Platform.