Fiscal Calendar Generator
Many plants report in 4-4-5 periods: every period is whole weeks, so a period compares cleanly with the same period last year. Pick the rules and copy a DAX calculated table that builds the calendar. The preview shows each year's start and length, so a 53-week year is no surprise.
| Fiscal year | Starts | Ends | Weeks |
|---|
The DAX
Years must be whole numbers from 1990 to 2090, first no later than last. The DAX below is for the last valid choice.
-- 4-4-5 fiscal calendar. Each year starts on the Monday nearest 1 January.
-- Years run 52 weeks, or 53 when the drift adds up; week 53 joins period 12.
Fiscal Calendar =
-- Whole fiscal years FY2024 to FY2027, so no year is cut short at either end
VAR FirstDay = DATE ( 2024, 1, 1 )
VAR LastDay = DATE ( 2028, 1, 2 )
VAR AnchorMonth = 1
-- 1 = Monday ... 7 = Sunday, as WEEKDAY ( date, 2 )
VAR WeekStart = 1
RETURN
GENERATE (
CALENDAR ( FirstDay, LastDay ),
VAR d = [Date]
VAR y = YEAR ( d )
-- the year start in this, the next and the previous calendar year
VAR a1 = DATE ( y + 1, AnchorMonth, 1 )
VAR s1 = a1 + MOD ( WeekStart - WEEKDAY ( a1, 2 ) + 3, 7 ) - 3
VAR a0 = DATE ( y, AnchorMonth, 1 )
VAR s0 = a0 + MOD ( WeekStart - WEEKDAY ( a0, 2 ) + 3, 7 ) - 3
VAR am = DATE ( y - 1, AnchorMonth, 1 )
VAR sm = am + MOD ( WeekStart - WEEKDAY ( am, 2 ) + 3, 7 ) - 3
VAR StartYear = IF ( d >= s1, y + 1, IF ( d >= s0, y, y - 1 ) )
VAR YearStart = IF ( d >= s1, s1, IF ( d >= s0, s0, sm ) )
VAR Week = INT ( DATEDIFF ( YearStart, d, DAY ) / 7 ) + 1
VAR Period =
SWITCH (
TRUE (),
Week <= 4, 1,
Week <= 8, 2,
Week <= 13, 3,
Week <= 17, 4,
Week <= 21, 5,
Week <= 26, 6,
Week <= 30, 7,
Week <= 34, 8,
Week <= 39, 9,
Week <= 43, 10,
Week <= 47, 11,
12
)
RETURN
ROW (
"Fiscal Year", StartYear,
"Fiscal Quarter", QUOTIENT ( Period - 1, 3 ) + 1,
"Fiscal Period", Period,
"Fiscal Week", Week,
"Fiscal Year Start", YearStart,
"Week Start", d - MOD ( WEEKDAY ( d, 2 ) - WeekStart, 7 )
)
) Comparing with Last Year
Time intelligence functions such as SAMEPERIODLASTYEAR shift calendar dates, and a 4-4-5 period starts on a different
day each year, so they compare the wrong days. Shift by fiscal week instead: take the weeks in view and look them up one fiscal year
earlier. That works at week, period, quarter and year grain, and under slicers; a partial week compares with the whole week a year before. And because period 12 of a 53-week year has
an extra week, compare per-week averages there, or the extra week reads as growth.
Units = SUM ( fact_production_hourly[units_produced] )
-- The same fiscal weeks one fiscal year earlier. VALUES ( [Fiscal Week] ) is read
-- before REMOVEFILTERS clears the calendar, so it holds the weeks in view, whether
-- they come from a week, period, quarter or year row, or a slicer. Periods cover the
-- same week numbers every year, so this is also same-period-last-year. Week 53 has
-- no match in a 52-week year and returns blank. Use the calendar's own columns on
-- the axis: the auto date hierarchy groups by a hidden table this cannot see.
Units Same Period LY =
VAR FY = SELECTEDVALUE ( 'Fiscal Calendar'[Fiscal Year] )
RETURN
IF (
NOT ISBLANK ( FY ),
CALCULATE (
[Units],
REMOVEFILTERS ( 'Fiscal Calendar' ),
'Fiscal Calendar'[Fiscal Year] = FY - 1,
TREATAS ( VALUES ( 'Fiscal Calendar'[Fiscal Week] ), 'Fiscal Calendar'[Fiscal Week] )
)
)
-- Period 12 of a 53-week year has one more week than the year before.
-- Compare averages per week so the extra week does not read as growth.
-- Count only weeks that have production, so the weeks still to come in an
-- open period do not dilute its average.
Weeks in Context =
CALCULATE ( DISTINCTCOUNT ( 'Fiscal Calendar'[Week Start] ), fact_production_hourly )
Units per Week = DIVIDE ( [Units], [Weeks in Context] )
Units per Week Same Period LY =
VAR FY = SELECTEDVALUE ( 'Fiscal Calendar'[Fiscal Year] )
RETURN
IF (
NOT ISBLANK ( FY ),
CALCULATE (
[Units per Week],
REMOVEFILTERS ( 'Fiscal Calendar' ),
'Fiscal Calendar'[Fiscal Year] = FY - 1,
TREATAS ( VALUES ( 'Fiscal Calendar'[Fiscal Week] ), 'Fiscal Calendar'[Fiscal Week] )
)
) How the Rules Work
A fiscal year starts on the chosen weekday nearest the 1st of the anchor month, never more than three days away. Because 52 weeks is 364 days, the start drifts a day a year (two in a leap year) until a year has to take a 53rd week to catch up, about one year in five or six. With Monday and January the weeks match ISO week numbers.
A year anchored in January takes the number of that January, so FY2025 can start on 30 December 2024; any other anchor is labelled
by the year it ends in, so a year starting near 1 October 2024 is FY2025. The table covers whole fiscal years, so none is cut short at either end. The site's tests check the JavaScript behind the preview against those rules, check the DAX
year-start formula against it with DAX's MOD rule for negative numbers, and lint every combination of options.