PlantOps BI

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.

Units produced in each week-1 bucket, grouped the wrong way (calendar year + ISO week) and the right way (ISO year + ISO week).

BucketWrong: dates in itUnitsRight: dates in itUnitsOff 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:

Calculated columns on dim_date:

-- 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" )

Measures:

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
        )
    )

Sort ISO Week Label by ISO Year Week, or 2025-W10 sorts before 2025-W2. The site computes ISO weeks independently of the demo calendar and its tests check the two agree on every day, and that every correctly grouped week has seven days.

Schedule attainment · XmR chart · About the demo dataset