I have a table in Power BI where I have the weekly sales amount by account for past 3 months. My date column is week_started_at which is the start of given week. I have added a column called WeekRank to rank each week. I’d like to calculate W/W change of total sales by account. But my DAX measure doesn’t respect the filter context despite having ALLSELECTED. Can you help me understand why this measure fails to respect the filter context when added to a matrix visual:
Imagine I have a table with these columns:
| account | sales_amount | week_started_at |
|---|---|---|
| 123 | $20 | 2024-03-24 |
| 123 | $40 | 2024-03-31 |
| 123 | $60 | 2024-04-07 |
current_week_sales = COALESCE(
CALCULATE(SUM(table[sales_amount]), FILTER(ALLSELECTED(table), table[WeekRank] = MAX(table[WeekRank]))),0)
last_week_sales = COALESCE(
CALCULATE(SUM(table[sales_amount]), FILTER(ALLSELECTED(table), table[WeekRank] = MAX(table[WeekRank]) -1)
), 0)
W/W_Change =
VAR weekly_comparison = [current_week_sales] - [last_week_sales]
RETURN
COALESCE(
IF(ISBLANK(weekly_comparison), "", DIVIDE(weekly_comparison , [last_week_sales]))
,0)
The measures work fine as long as they are added to a tabular visual but they don’t work as expected in matrix. None of the measures respect the filter context in the matrix, as result all values are the same week over week for all accounts.
Would you please help me understand what am I doing wrong?