DAX Weekly Sales Filter Formula

DAX Formula to Filter Items Sold in a Week

To filter all items sold within a specific week, you can use the following DAX formula, which utilizes the FILTER and CALCULATE functions.

DAX Formula

ItemsSoldThisWeek = 
CALCULATE(
    SUM(Sales[Quantity]), 
    FILTER(
        Sales, 
        Sales[Date] >= STARTOFWEEK(TODAY(), 1) && 
        Sales[Date] < STARTOFWEEK(TODAY() + 7, 1)
    )
)

Explanation of the Formula

1. CALCULATE Function: This function modifies the context in which the data is evaluated. In this formula, it calculates the sum of the sales quantity.

2. SUM Function: The SUM(Sales[Quantity]) aggregates the total quantity sold from the Sales table.

3. FILTER Function:

   • This function returns a table that has been filtered based on a condition.
   • The filter condition checks if the sales date falls within the current week.

4. STARTOFWEEK Function:

   • STARTOFWEEK(TODAY(), 1) returns the first day of the current week based on the provided day of the week (1 for Monday).
   • The second STARTOFWEEK shifts the range to represent the beginning of the next week.

5. Date Range:

   • Sales[Date] >= STARTOFWEEK(TODAY(), 1) ensures items sold from the start of the current week are included.
   • Sales[Date] < STARTOFWEEK(TODAY() + 7, 1) ensures items sold until the start of the next week are included, effectively capturing a full week.

Practical Example

Assuming you have a Sales table with the columns: Date and Quantity. If you need to calculate the total number of items sold during the current week, you can create a measure using the above DAX formula. This measure can then be used in visualizations to provide insights into weekly sales performance.

Scenario

• Sales Table:
  • Date: Contains various transaction dates.
  • Quantity: Represents the number of items sold on each date.

Application

• Add the measure ItemsSoldThisWeek to your Power BI report to dynamically see the sum of items sold in the current week. The measure updates automatically based on the current date.

Conclusion

This DAX formula effectively filters and aggregates sales data for the current week, enabling data analysts to monitor weekly sales trends efficiently. For users seeking to deepen their understanding of DAX, consider exploring additional resources available on the Enterprise DNA Platform.