@HankScorpio
In DAX, you indeed cannot use traditional loop constructs like in procedural programming languages. Instead, you can achieve a similar result by creating a series of measures that calculate values conditionally based on the start date and the current context on your report or visual.
However, to compare dates and calculate weekly results, we'll need to ensure that the 'Staff' table and the 'date table' are properly related in the model. Here's how you can calculate weekly results starting from each staff member's start date:
1. Make sure you have a 'date table' that covers all the dates in your range and that it is marked as a date table in your model.
2. Set up a relationship between your 'Staff' table and 'date table' on the date columns.
3. In your 'Staff' table, add a calculated column to determine the week number since the start date for each date in your 'date table'.
Week Number Since Start =
INT(( 'date table'[Date] - Staff[Start date] ) / 7)
4. You will need to create individual measures for each week's AHT based on the 'Week Number Since Start'. Here's how you can write the measure for Week 1:
Week 1 AHT =
CALCULATE(
[Total AHT],
FILTER(
ALL('date table'),
'date table'[Date] >= MIN(Staff[Start date])
&& 'date table'[Date] < MIN(Staff[Start date]) + 7
),
REMOVEFILTERS(Staff)
)
5. Copy the measure and adjust it for subsequent weeks by changing the week window:
Week 2 AHT =
CALCULATE(
[Total AHT],
FILTER(
ALL('date table'),
'date table'[Date] >= MIN(Staff[Start date]) + 7
&& 'date table'[Date] < MIN(Staff[Start date]) + 14
),
REMOVEFILTERS(Staff)
)
By following these steps, you'll have a set of measures that can be used in your reports to display weekly results based on each staff member's start date. You can continue creating such measures for as many weeks as needed.
This approach does require you to create multiple measures, and it can be cumbersome if you have a lot of weeks to cover. An alternative approach for a more dynamic solution might be to use Power BI's built-in time intelligence functions in combination with calculated columns or to write more complex DAX code using `SUMMARIZE` and other functions to create a summary table dynamically within a measure.
Remember, if you have different start dates and need the weeks to be relative to each individual's start date, using `REMOVEFILTERS(Staff)` allows the calculation to consider each staff member's start date without being affected by row context in the 'Staff' table.
If this post helps, please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍
