ISO Weeks
Plants plan in weeks, and most use ISO weeks: Monday to Sunday, week 1 being the week with the year's first Thursday. The demo
calendar has an iso_week column but no ISO year, and that gap is enough to corrupt every week that straddles a year boundary. In this data that is each week 1; from January 2027 it would be week 53 of 2026 as well.
Where It Breaks
Of the 974 days in dim_date, 5 sit in a different ISO year from their calendar year:
Dec 30, 2024, Dec 31, 2024, Dec 29, 2025, Dec 30, 2025, Dec 31, 2025. They are in ISO week 1 of the next year. A matrix with calendar
year and iso_week on the rows files them under week 1 of their own calendar year instead, beside a January
week they don't belong to.
| Bucket | Wrong: dates in it | Units | Right: dates in it | Units | Off by |
|---|---|---|---|---|---|
| 2024-W1 | Jan 1, 2024 to Jan 7, 2024 and Dec 30, 2024 to Dec 31, 2024 | 205,804 | Jan 1, 2024 to Jan 7, 2024 | 145,282 | +42% |
| 2025-W1 | Jan 1, 2025 to Jan 5, 2025 and Dec 29, 2025 to Dec 31, 2025 | 178,052 | Dec 30, 2024 to Jan 5, 2025 | 148,337 | +20% |
| 2026-W1 | Jan 1, 2026 to Jan 4, 2026 | 60,967 | Dec 29, 2025 to Jan 4, 2026 | 151,204 | -60% |
The wrong grouping makes "week 1" a bucket of 4 to 9 days. A weekly chart then shows a spike or a hole every January, and anyone comparing week 1 with last year's week 1 is comparing different numbers of days.
The Fix
Add an ISO year column and use it with iso_week everywhere a week is the grain. The ISO year is the calendar year of the
week's Thursday, which is one line of DAX. Then pick the comparison you mean:
- 52 weeks ago shifts the dates back 364 days, so Mondays line up with Mondays. Simple, and fine for most plants.
- Same ISO week last year matches the week number. The two differ after a 53-week year: 2026 has a week 53, so in 2027 "364 days back" lands one week number off from "the same week number".
-- The ISO year is the calendar year of the week's Thursday
ISO Year =
YEAR ( dim_date[date] - WEEKDAY ( dim_date[date], 2 ) + 4 )
-- One value per ISO week, for the axis and as the sort column of a label
ISO Year Week = dim_date[ISO Year] * 100 + dim_date[iso_week]
ISO Week Label =
dim_date[ISO Year] & "-W" & FORMAT ( dim_date[iso_week], "00" ) Units Produced = SUM ( fact_production_hourly[units_produced] )
-- Same weekdays a year back: 52 weeks, not one calendar year
Units 52 Weeks Ago =
CALCULATE ( [Units Produced], DATEADD ( dim_date[date], -364, DAY ) )
-- Same ISO week number in the previous ISO year. Blank for week 53
-- when last year had only 52, which is the honest answer.
Units Same ISO Week LY =
VAR Y = SELECTEDVALUE ( dim_date[ISO Year] )
VAR W = SELECTEDVALUE ( dim_date[iso_week] )
RETURN
IF (
NOT ISBLANK ( Y ) && NOT ISBLANK ( W ),
CALCULATE (
[Units Produced],
REMOVEFILTERS ( dim_date ),
dim_date[ISO Year] = Y - 1,
dim_date[iso_week] = W
)
)