To calculate the average quantity for the previous 5 working weeks in DAX, you can create a **measure** that dynamically calculates the average based on the previous 5 weeks. Here's a step-by-step guide to achieve this:
### 1. **Create a Measure for 5-Week Average**
This measure will take the context of the current week and calculate the average of the past 5 weeks dynamically. Here’s the DAX formula you can use:
```DAX
Average_5_Weeks =
VAR CurrentWeek = MAX('Table'[Week]) -- Assume 'Week' column identifies each working week uniquely
VAR Past5Weeks =
FILTER(
ALL('Table'),
'Table'[Week] <= CurrentWeek && 'Table'[Week] > CurrentWeek - 5
)
RETURN
AVERAGEX(Past5Weeks, 'Table'[Quantity]) -- Replace 'Quantity' with your actual column name
```
### Explanation of the Measure
- **CurrentWeek**: This variable identifies the current week in the table's context.
- **Past5Weeks**: This variable creates a table of the previous 5 weeks (using `FILTER` to get only rows where the `Week` is within the last 5 weeks).
- **AVERAGEX**: Calculates the average quantity over the filtered table of the past 5 weeks.
> Replace `'Table'` with your actual table name and `'Quantity'` with the column that holds the quantity values.
### 2. **Apply the Measure in Your Matrix or Table Visual**
- Place this measure in your table or matrix visual instead of using the `SUM` or `TOTAL` aggregation.
- This will ensure that each week in the visual displays the average quantity for the preceding 5 weeks.
### 3. **Handle Missing Data**
If some weeks do not have data, the formula above will still work as it calculates the average over available weeks within the range. If you want to ensure exactly 5 weeks of data, add logic to exclude periods with fewer than 5 weeks of data.
### Example Result
For each row in your table, the measure `Average_5_Weeks` will calculate the average of the previous 5 weeks, displaying it in place of the total quantity.
If this solution brightened your path or made things easier, please consider giving kudos. Your recognition not only uplifts those who helped but inspires others to keep contributing for the good of our community!
