DAX Formula to Total Volume for the Week

Formula

To calculate the total volume for the week, you can use the following DAX formula:

TotalVolumeForWeek = 
CALCULATE(
    SUM(Sales[Volume]), 
    DATESINPERIOD(
        Sales[Date], 
        MAX(Sales[Date]), 
        -7, 
        DAY
    )
)

Explanation of the Formula

Breakdown of Components

1. CALCULATE: This function modifies the context in which data is evaluated. It allows you to apply filters on your calculations.
2. SUM(Sales[Volume]): This part sums up the values in the Volume column of the Sales table.
3. DATESINPERIOD: This function defines a date range. In this case, it fetches the last 7 days leading up to the maximum date in the context of the Sales[Date] column.
   • Sales[Date]: Column used to determine the date context.
   • MAX(Sales[Date]): Captures the most recent date available in the filtering context.
   • -7: Indicates we are looking back seven days.
   • DAY: Specifies the time unit for the range to be days.

Logic Summary

This formula calculates the total volume sold in the last week (7 days) relative to the latest date present in the data.

Practical Example

Scenario

Suppose you have a Sales table with the following data:

Calculation for Week Ending on 2023-10-08

Assuming today’s context is 2023-10-08, the DAX formula would sum the volume from:

• 2023-10-02 (150)
• 2023-10-03 (200)
• 2023-10-04 (250)
• 2023-10-05 (300)
• 2023-10-06 (100)
• 2023-10-07 (50)
• 2023-10-08 (400)

Result

The result of TotalVolumeForWeek would be: 150 + 200 + 250 + 300 + 100 + 50 + 400 = 1450

Conclusion

The provided DAX formula efficiently computes the total volume sold over the most recent week. Understanding and applying such formulas can significantly enhance data analysis capabilities. For further learning, consider exploring courses available on the Enterprise DNA Platform to deepen your knowledge and technical skills in DAX.