Morning! I have a dax created calendar table that I am having trouble getting it to start the fiscal year on the exact date every year. This is causing some of the columns like fiscal month to bleed into other months. As it is currently written, the fiscal year is starting on the first Sunday after 10/1 of each year, when it should start on 10/1 of each year.
Date =
VAR FirstFiscalMonth = 10 -- First month of the fiscal year
VAR FirstDayOfWeek = 0 -- 0 = Sunday, 1 = Monday, ...
VAR FirstAuditDate = MIN ( 'CalendarAuto'[Date] )
VAR LastAuditDate = TODAY()
VAR TypeStartFiscalYear = 1 -- Fiscal year as Calendar Year of :
-- 0 - First day of fiscal year
-- 1 - Last day of fiscal year
VAR QuarterWeekType = "445" -- Supports only "445", "454", and "544"
VAR WeeklyType = "Nearest" -- Use: "Nearest" or "Last"
-- Last: for last weekday of the month at fiscal year end
-- Nearest: for last weekday nearest the end of month
-- Reference for Last/Nearest definition: https://en.wikipedia.org/wiki/4%E2%80%934%E2%80%935_calendar)
--
-- For ISO calendar use
-- FirstFiscalMonth = 1 (ISO always starts in January)
-- FirstDayOfWeek = 1 (ISO always starts on Monday)
-- WeeklyType = "Nearest" (ISO use the nearest week type algorithm)
-- For US with last Saturday of the month at fiscal year end
-- FirstDayOfWeek = 0 (US weeks start on Sunday)
-- WeeklyType = "Last"
-- For US with last Saturday nearest the end of month
-- FirstDayOfWeek = 0 (US weeks start on Sunday)
-- WeeklyType = "Nearest"
--
------------------------------
VAR FirstYear =
YEAR ( FirstAuditDate )
+ 1 * ( MONTH ( FirstAuditDate ) >= FirstFiscalMonth
&& FirstFiscalMonth > 1 )
VAR LastYear =
YEAR ( LastAuditDate )
+ 1 * ( MONTH ( LastAuditDate ) >= FirstFiscalMonth
&& FirstFiscalMonth > 1 )
VAR WorkingDayType = "Working Day" -- Description for working days
VAR NonWorkingDayType = "Non-Working day" -- Description for non-working days
------------------------------
-- Set the working days - 0 = Sunday, 1 = Monday, ... 6 = Saturday
VAR WorkingDays =
DATATABLE ( "WorkingDayNumber", INTEGER, { { 1 }, { 2 }, { 3 }, { 4 }, { 5 } } ) --
------------------------------------------------------------
--
-- End of General Configuration
--
------------------------------------------------------------
VAR FirstDayCalendar =
DATE ( FirstYear - 1, 10, 1 )
VAR LastDayCalendar =
DATE ( LastYear + 1, 9, 30 )
VAR WeekDayCalculationType =
IF ( FirstDayOfWeek = 0, 7, FirstDayOfWeek ) + 10
VAR OffsetFiscalYear =
IF ( FirstFiscalMonth > 1, 1, 0 )
VAR WeeklyFiscalPeriods =
GENERATE (
SELECTCOLUMNS (
GENERATESERIES ( FirstYear - OffsetFiscalYear, LastYear + OffsetFiscalYear, 1 ),
"FW YearNumber", [Value]
),
VAR StartFiscalYearNumber = [FW YearNumber] - ( OffsetFiscalYear * TypeStartFiscalYear )
VAR FirstDayCurrentYear =
DATE ( StartFiscalYearNumber, FirstFiscalMonth, 1 )
VAR FirstDayNextYear =
DATE ( StartFiscalYearNumber + 1, FirstFiscalMonth, 1 )
VAR DayOfWeekNumberCurrentYear =
WEEKDAY ( FirstDayCurrentYear, WeekDayCalculationType )
VAR OffsetStartCurrentFiscalYear =
SWITCH (
WeeklyType,
"Last", 1 - DayOfWeekNumberCurrentYear,
"Nearest", IF (
DayOfWeekNumberCurrentYear >= 5,
8 - DayOfWeekNumberCurrentYear,
1 - DayOfWeekNumberCurrentYear
),
ERROR ( "Unkonwn WeeklyType definition" )
)
VAR DayOfWeekNumberNextYear =
WEEKDAY ( FirstDayNextYear, WeekDayCalculationType )
VAR OffsetStartNextFiscalYear =
SWITCH (
WeeklyType,
"Last", - DayOfWeekNumberNextYear,
"Nearest", IF (
DayOfWeekNumberNextYear >= 5,
7 - DayOfWeekNumberNextYear,
- DayOfWeekNumberNextYear
),
ERROR ( "Unkonwn WeeklyType definition : " )
)
VAR FirstDayOfFiscalYear = FirstDayCurrentYear + OffsetStartCurrentFiscalYear
VAR LastDayOfFiscalYear = FirstDayNextYear + OffsetStartNextFiscalYear
RETURN
ROW (
"FW StartOfYear", FirstDayOfFiscalYear,
"FW EndOfYear", LastDayOfFiscalYear
)
)
VAR WeeksInP1 =
SWITCH (
QuarterWeekType,
"445", 4,
"454", 4,
"544", 5,
ERROR ( "QuarterWeekType only supports 445, 454, and 544" )
)
VAR WeeksInP2 =
SWITCH (
QuarterWeekType,
"445", 4,
"454", 5,
"544", 4,
ERROR ( "QuarterWeekType only supports 445, 454, and 544" )
)
VAR WeeksInP3 =
SWITCH (
QuarterWeekType,
"445", 5,
"454", 4,
"544", 4,
ERROR ( "QuarterWeekType only supports 445, 454, and 544" )
)
VAR FirstSundayReference =
DATE ( 1900, 12, 30 ) -- Do not change this
VAR FirstWeekReference = FirstSundayReference + FirstDayOfWeek
VAR CustomFiscalRawDays =
GENERATE ( WeeklyFiscalPeriods, CALENDAR ( [FW StartOfYear], [FW EndOfYear] ) )
VAR FiscalWeeksBase =
GENERATE (
CustomFiscalRawDays,
VAR CalDate = [Date]
VAR FwFirstDayOfYear = [FW StartOfYear]
VAR FwDayOfYear =
INT ( CalDate - FwFirstDayOfYear + 1 )
VAR CalYear =
YEAR ( [Date] )
VAR CalMonthNumber =
MONTH ( [Date] )
VAR CalDay =
DAY ( [Date] )
VAR FwDayOfYearNumber = CalDate - [FW StartOfYear] + 1
VAR FwWeekNumber =
INT ( CEILING ( FwDayOfYearNumber / 7, 1 ) )
VAR FwPeriodNumber =
IF ( FwWeekNumber > 52, 14, ROUNDUP ( FwWeekNumber / 4, 0 ) )
VAR FwYearNumber = [FW YearNumber]
VAR FwQuarterNumber =
IF ( FwWeekNumber > 52, 4, ROUNDUP ( FwWeekNumber / 13, 0 ) )
VAR FwWeekInQuarterNumber =
IF ( FwWeekNumber > 52, 14, FwWeekNumber - 13 * ( FwQuarterNumber - 1 ) )
VAR FwMonthNumber =
( FwQuarterNumber - 1 ) * 3
+ SWITCH (
TRUE,
FwWeekInQuarterNumber <= WeeksInP1, 1,
FwWeekInQuarterNumber <= ( WeeksInP1 + WeeksInP2 ), 2,
3
)
VAR WeekDayNumber =
WEEKDAY ( CalDate, WeekDayCalculationType )
VAR FirstDayOfWeek = [Date] - WeekDayNumber + 1
VAR LastDayOfWeek = FirstDayOfWeek + 6
VAR IsWorkingDay =
CONTAINS ( WorkingDays, [WorkingDayNumber], WEEKDAY ( CalDate, 1 ) - 1 )
RETURN
ROW (
"DateKey", CalYear * 10000 + CalMonthNumber * 100 + CalDay,
"FW Year", FwYearNumber,
"FW QuarterNumber", FwQuarterNumber,
"FW Quarter", "FQ" & FwQuarterNumber,
"FW YearQuarterNumber", FwYearNumber * 4 - 1 + FwQuarterNumber,
"FW Quarter Year", "FQ" & FwQuarterNumber & "-" & FwYearNumber,
"FW MonthNumber", FwMonthNumber,
"FW YearMonthNumber", FwYearNumber * 12 - 1 + FwMonthNumber,
"FW WeekNumber", FwWeekNumber,
"FW Week", "FW" & FORMAT ( FwWeekNumber, "00" ),
"FW PeriodNumber", FwPeriodNumber,
"FW Period", "P" & FORMAT ( FwPeriodNumber, "00" ),
"FW YearWeekNumber", INT ( DIVIDE ( CalDate - FirstWeekReference, 7 ) ) + 1,
"FW Week Year", "FW" & FORMAT ( FwWeekNumber, "00" ) & "-" & FwYearNumber,
"FW StartOfWeek", FirstDayOfWeek,
"FW EndOfWeek", LastDayOfWeek,
"WeekDayNumber", WeekDayNumber,
"Week Day", FORMAT ( CalDate, "ddd" ),
"FW DayOfYearNumber", FwDayOfYear,
"IsWorkingDay", IsWorkingDay,
"Day Type", IF ( IsWorkingDay, WorkingDayType, NonWorkingDayType )
)
)
VAR FiscalWeeks_Pre =
GENERATE (
FiscalWeeksBase,
VAR CalDate = [Date]
VAR FWYearNumber = [FW YearNumber]
VAR FwYearWeekNumber = [FW YearWeekNumber]
VAR FwYearMonthNumber = [FW YearMonthNumber]
VAR FwYearQuarterNumber = [FW YearQuarterNumber]
VAR FwStartOfMonth =
MINX (
FILTER ( FiscalWeeksBase, [FW YearMonthNumber] = FwYearMonthNumber ),
[Date]
)
VAR FwEndOfMonth =
MAXX (
FILTER ( FiscalWeeksBase, [FW YearMonthNumber] = FwYearMonthNumber ),
[Date]
)
VAR FwMonthDays =
INT ( FwEndOfMonth - FwStartOfMonth + 1 )
VAR FwDayOfMonthNumber =
INT ( CalDate - FwStartOfMonth + 1 )
VAR FwStartOfQuarter =
MINX (
FILTER ( FiscalWeeksBase, [FW YearQuarterNumber] = FwYearQuarterNumber ),
[Date]
)
VAR FwEndOfQuarter =
MAXX (
FILTER ( FiscalWeeksBase, [FW YearQuarterNumber] = FwYearQuarterNumber ),
[Date]
)
VAR FwQuarterDays =
INT ( FwEndOfQuarter - FwStartOfQuarter + 1 )
VAR FwDayOfQuarterNumber =
INT ( CalDate - FwStartOfQuarter + 1 )
VAR FwStartOfYear =
MINX ( FILTER ( FiscalWeeksBase, [FW YearNumber] = FwYearNumber ), [Date] )
VAR FwEndOfYear =
MAXX ( FILTER ( FiscalWeeksBase, [FW YearNumber] = FwYearNumber ), [Date] )
VAR FwYearDays =
INT ( FwEndOfYear - FwStartOfYear + 1 )
RETURN
ROW (
"FW Month", "FM" & FORMAT ( [FW MonthNumber], "00 " ) & FORMAT ( FwStartOfMonth + 14, "mmm" ),
"FW Month Year", "FM " & FORMAT ( FwStartOfMonth + 14, "mmm yyyy" ),
"FW StartOfMonth", FwStartOfMonth,
"FW EndOfMonth", FwEndOfMonth,
"FW DayOfMonthNumber", FwDayOfMonthNumber,
"FW StartOfQuarter", FwStartOfQuarter,
"FW EndOfQuarter", FwEndOfQuarter,
"FW DayOfQuarterNumber", FwDayOfQuarterNumber,
"FW MonthDays", FwMonthDays,
"FW QuarterDays", FwQuarterDays,
"FW YearDays", FwYearDays
)
)
VAR FiscalWeeks =
GENERATE (
FiscalWeeks_Pre,
VAR CalDate = [Date]
VAR FwYearMonthNumber = [FW YearMonthNumber]
VAR FwYearQuarterNumber = [FW YearQuarterNumber]
VAR FWYearNumber = [FW YearNumber]
VAR FwDayOfMonthNumber = [FW DayOfMonthNumber]
VAR FwDayOfQuarterNumber = [FW DayOfQuarterNumber]
VAR FwDayOfYearNumber = [FW DayOfYearNumber]
VAR FwMonthDays = [FW EndOfMonth] - [FW StartOfMonth] + 1
VAR FwQuarterDays = [FW EndOfQuarter] - [FW StartOfQuarter] + 1
VAR FwYearDays = [FW EndOfYear] - [FW StartOfYear] + 1
VAR FwDatePreviousWeek = CalDate - 7
VAR FwDatePreviousMonth =
MAXX (
FILTER (
FiscalWeeks_Pre,
[Fw YearMonthNumber] = FwYearMonthNumber - 1
&& ( [FW DayOfMonthNumber] <= FwDayOfMonthNumber
|| FwDayOfMonthNumber = FwMonthDays )
),
[Date]
)
VAR FwDatePreviousQuarter =
MAXX (
FILTER (
FiscalWeeks_Pre,
[Fw YearQuarterNumber] = FwYearQuarterNumber - 1
&& ( [FW DayOfQuarterNumber] <= FwDayOfQuarterNumber
|| FwDayOfQuarterNumber = FwQuarterDays )
),
[Date]
)
VAR FwDatePreviousYear =
MAXX (
FILTER (
FiscalWeeks_Pre,
[Fw YearNumber] = FWYearNumber - 1
&& ( [FW DayOfYearNumber] <= FwDayOfYearNumber
|| FwDayOfYearNumber = FwYearDays )
),
[Date]
)
RETURN
ROW (
"FW DatePreviousWeek", FwDatePreviousWeek,
"FW DatePreviousMonth", FwDatePreviousMonth,
"FW DatePreviousQuarter", FwDatePreviousQuarter,
"FW DatePreviousYear", FwDatePreviousYear
)
)
VAR CompleteCalendar =
FILTER (
FiscalWeeks,
( [FW YearNumber] >= FirstYear
&& [FW YearNumber] <= LastYear )
)
VAR Result =
SELECTCOLUMNS (
CompleteCalendar,
-- Base date columns
"Date", [Date],
"Sequential Day Number", INT ( [Date] ),
-- FW = Fiscal Weekly calendar
"Fiscal Year Number", [FW YearNumber],
"Fiscal Year", "FY " & [FW Year],
"Fiscal Quarter Number", [FW QuarterNumber],
"Fiscal Quarter", [FW Quarter],
"Fiscal Year Quarter Number", [FW YearQuarterNumber],
"Fiscal Year Quarter", [FW Quarter Year],
"Fiscal Month Number", [FW MonthNumber],
"Fiscal Month", [FW Month],
"Fiscal Year Month Number", [FW YearMonthNumber],
"Fiscal Year Month", [FW Month Year],
"Fiscal Week Number", [FW WeekNumber],
"Fiscal Week", [FW Week],
"Fiscal Year Week Number", [FW YearWeekNumber],
"Fiscal Year Week", [FW Week Year],
-- Holidays and working days
"Working Day", [Day Type],
"Day of Week Number", [WeekDayNumber],
"Day of Week", [Week Day],
"Day of Fiscal Month Number", [FW DayOfMonthNumber],
"Day of Fiscal Quarter Number", [FW DayOfQuarterNumber],
"Day of Fiscal Year Number", [FW DayOfYearNumber]
)
RETURN
Result
