Calendar for 13 period Fiscal year

keekee · ‎11-11-2022

HI,

I am looking for some guidance on how to amend this calendar; I need to amend for a 13 period fiscal year which starts on the Second or Third SUnday every September.

This calendar (below) is based on Fiscal Quarters (which I actually don't need); I need 13 periods each year, each with 4 weeks and every fifth year or so, period 13 has 5 weeks. The date ranges are as below: - so 5 weeks in 20/21 and 2015/2016

{ 2015, "2015-09-13", "2016-09-17" },

{ 2016, "2016-09-18", "2017-09-16" },

{ 2017, "2017-09-17", "2018-09-15" },

{ 2018, "2018-09-16", "2019-09-14" },

{ 2019, "2019-09-15", "2020-09-12" },

{ 2020, "2020-09-13", "2021-09-18" },

{ 2021, "2021-09-19", "2022-09-17" }

MY current calendar looks like this:

I want to remove the dependency on 454, 544, 445:

Calendar =

------------------------------------------------------------

--

-- Configuration

--

-- Fiscal week run from Sunday to Saturday

------------------------------------------------------------

VAR TodayReference =

TODAY () -- Change this if you need to use another date as a reference "current" day

VAR FirstYear = 2015

VAR LastYear = YEAR ( TodayReference )

VAR FiscalCalendarFirstMonth = 9 -- For Fiscal 52-53 weeks (start depends on rules) and Gregorian (starts on the first of the month)

VAR FirstDayOfWeek = 0 -- Use: 0 - Sunday, 1 - Monday, 2 - Tuesday, ... 5 - Friday, 6 - Saturday

VAR WeeklyType = "Last" -- Use: "Nearest" or "Last"

VAR QuarterWeekType = "454" -- Supports only "445", "454", and "544"

VAR CalendarRange = "Calendar" -- Supports "Calendar", "FiscalGregorian", "FiscalWeekly"

VAR CalendarGregorianPrefix = "" -- Prefix used in columns of standard Gregorian calendar

VAR FiscalPrefix = "F" -- Prefix used in columns of fiscal Gregorian calendar

VAR FiscalWeeklyPrefix = "FW " -- Prefix used in columns of fiscal weekly calendar

VAR FiscalWeeklyPrefixSpace =

IF ( FiscalWeeklyPrefix <> "", FiscalWeeklyPrefix & " ", "" )

VAR CustomFiscalPeriods =

DATATABLE (

"Fiscal YearNumber", INTEGER,

"FiscalFirstDayOfYear", DATETIME,

"FiscalLastDayOfYear", DATETIME,

{

{ 2015, "2015-09-13", "2016-09-17" },

{ 2016, "2016-09-18", "2017-09-16" },

{ 2017, "2017-09-17", "2018-09-15" },

{ 2018, "2018-09-16", "2019-09-14" },

{ 2019, "2019-09-15", "2020-09-12" },

{ 2020, "2020-09-13", "2021-09-18" },

{ 2021, "2021-09-19", "2022-09-17" }

}

)

-- schema 454

VAR WeeksInP1 = 4

VAR WeeksInP2 = 5

VAR WeeksInP3 = 4

-------------------------------------------------------------------------------------------------------------------------------------

VAR FirstDayCalendar =

DATE ( FirstYear - 1, 1, 1 )

VAR LastDayCalendar =

Date ( LastYear + 2, 12, 31 )

VAR RawDays =

CALENDAR ( FirstDayCalendar, LastDayCalendar )

VAR FiscalRawDays =

GENERATE ( CustomFiscalPeriods, CALENDAR ( [FiscalFirstDayOfYear], [FiscalLastDayOfYear] ) )

VAR FirstSundayReference =

DATE ( 1900, 12, 30 ) -- Do not change this

VAR FirstWeekReference = FirstSundayReference + FirstDayOfWeek

VAR WeekDayCalculationType =

IF ( FirstDayOfWeek = 0, 7, FirstDayOfWeek )

+ 10

VAR FiscalWeeksBase =

GENERATE (

FiscalRawDays,

VAR CalDate = [Date]

VAR FwFirstDayOfYear = [FiscalFirstDayOfYear]

VAR FwDayOfYear =

INT ( CalDate - FwFirstDayOfYear + 1 )

VAR CalYear =

YEAR ( [Date] )

VAR CalMonthNumber =

MONTH ( [Date] )

VAR CalDay =

DAY ( [Date] )

VAR FwDayOfYearNumber = CalDate - [FiscalFirstDayOfYear]

+ 1

VAR FwWeekNumber =

INT ( CEILING ( FwDayOfYearNumber / 7, 1 ) )

VAR FwPeriodNumber =

IF ( FwWeekNumber > 52, 13, ROUNDUP ( FwWeekNumber / 4, 0 ) )

VAR FwYearNumber = [Fiscal 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 FwMonthNameNumber = IF(FwMonthNumber = 12, 1, FwMonthNumber + 1 )

VAR WeekDayNumber =

WEEKDAY ( CalDate, WeekDayCalculationType )

VAR FirstDayOfWeek = [Date] - WeekDayNumber

+ 1

VAR LastDayOfWeek = FirstDayOfWeek + 6

RETURN

ROW (

"DateKey", CalYear * 10000

+ CalMonthNumber * 100

+ CalDay,

"FW YearNumber", FwYearNumber, -- It is already in the first set of columns of the GENERATE function

"FW Year", FiscalWeeklyPrefixSpace & FwYearNumber,

"FW QuarterNumber", FwQuarterNumber,

"FW Quarter", FiscalWeeklyPrefix & "Q"

& FwQuarterNumber,

"FW YearQuarterNumber", FwYearNumber * 4

- 1

+ FwQuarterNumber,

"FW Quarter Year", FiscalWeeklyPrefix & "Q"

& FwQuarterNumber

& " "

& FwYearNumber,

"FW MonthNumber", FwMonthNumber,

"Fw MonthNameNumber", FwMonthNameNumber,

"FW Month", FiscalWeeklyPrefix & "P"

& FORMAT ( FwMonthNumber, "00" ),

"FW MonthName", FiscalWeeklyPrefix & FORMAT(DATE(FwYearNumber,FwMonthNameNumber,1),"MMM"),

"FW YearMonthNumber", FwYearNumber * 12

- 1

+ FwMonthNumber,

"FW Month Year", FiscalWeeklyPrefix & "P"

& FORMAT ( FwMonthNumber, "00" )

& " "

& FwYearNumber,

"FW WeekNumber", FwWeekNumber,

"FW Week", FiscalWeeklyPrefix & "W"

& FORMAT ( FwWeekNumber, "00" ),

"FW PeriodNumber", FwPeriodNumber,

"FW Period", FiscalWeeklyPrefix & "P"

& FORMAT ( FwPeriodNumber, "00" ),

"FW YearWeekNumber", INT ( DIVIDE ( CalDate - FirstWeekReference, 7 ) )

+ 1,

"FW Week Year", FiscalWeeklyPrefix & "W"

& FORMAT ( FwWeekNumber, "00" )

& " "

& FwYearNumber,

"FW StartOfWeek", FirstDayOfWeek,

"FW EndOfWeek", LastDayOfWeek,

"WeekDayNumber", WeekDayNumber,

"Week Day", FORMAT ( CalDate, "ddd" ),

"FW DayOfYearNumber", FwDayOfYear

)

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 FWMonthName = [FW MonthName]

VAR CurrentWeekPos =

AVERAGEX (

FILTER ( FiscalWeeksBase, [Date] = TodayReference ),

[FW YearWeekNumber]

)

VAR CurrentMonthPos =

AVERAGEX (

FILTER ( FiscalWeeksBase, [Date] = TodayReference ),

[FW YearMonthNumber]

)

VAR CurrentQuarterPos =

AVERAGEX (

FILTER ( FiscalWeeksBase, [Date] = TodayReference ),

[FW YearQuarterNumber]

)

VAR CurrentYearPos =

AVERAGEX (

FILTER ( FiscalWeeksBase, [Date] = TodayReference ),

[FW YearNumber]

)

VAR RelativeWeekPos = CurrentWeekPos - FwYearWeekNumber

VAR RelativeMonthPos = CurrentMonthPos - FwYearMonthNumber

VAR RelativeQuarterPos = CurrentQuarterPos - FwYearQuarterNumber

VAR RelativeYearPos = CurrentYearPos - FwYearNumber

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 RelativeWeekPos", RelativeWeekPos,

"FW RelativeMonthPos", RelativeMonthPos,

"FW RelativeQuarterPos", RelativeQuarterPos,

"FW RelativeYearPos", RelativeYearPos,

"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 = [FiscalLastDayOfYear] - [FiscalFirstDayOfYear] + 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 CompleteCalendarJoin =

NATURALINNERJOIN (

CalendarStandardGregorianBase,

FiscalWeeks

)

VAR Result =

SELECTCOLUMNS (

CompleteCalendarJoin,

-- Base date columns

"Date", [Date],

"DateKey", [DateKey],

"Day of Month", [Day of Month],

"WeekDayNumber", [WeekDayNumber],

"Week Day", [Week Day],

-- Calendar = Solar Calendar (January-December)

"Calendar YearNumber", [Calendar YearNumber],

"Calendar Year", [Calendar Year],

"Calendar QuarterNumber", [Calendar QuarterNumber],

"Calendar Quarter", [Calendar Quarter],

"Calendar YearQuarterNumber", [Calendar YearQuarterNumber],

"Calendar Quarter Year", [Calendar Quarter Year],

"Calendar MonthNumber", [Calendar MonthNumber],

"Calendar Month", [Calendar Month],

"Calendar YearMonthNumber", [Calendar YearMonthNumber],

"Calendar Month Year", [Calendar Month Year],

"Calendar WeekNumber", [Calendar WeekNumber],

"Calendar Week", [Calendar Week],

"Calendar YearWeekNumber", [Calendar YearWeekNumber],

"Calendar Week Year", [Calendar Week Year],

"Calendar WeekYearOrder", [Calendar WeekYearOrder],

-- FW = Fiscal Weekly calendar

"FW YearNumber", [FW YearNumber],

"FW Year", [FW Year],

"FW QuarterNumber", [FW QuarterNumber],

"FW Quarter", [FW Quarter],

"FW YearQuarterNumber", [FW YearQuarterNumber],

"FW Quarter Year", [FW Quarter Year],

"FW MonthNumber", [FW MonthNumber],

"Fw MonthNameNumber", [Fw MonthNameNumber],

"FW Month", [FW Month],

"FW YearMonthNumber", [FW YearMonthNumber],

"FW MonthName", [FW MonthName],

"FW Month Year", [FW Month Year],

"FW WeekNumber", [FW WeekNumber],

"Accounting Week", [FW Week],

"Accounting PeriodNumber", [FW PeriodNumber],

"Accounting Period", [FW Period],

"FW YearWeekNumber", [FW YearWeekNumber],

"FW Week Year", [FW Week Year],

"FW StartOfWeek", [FW StartOfWeek],

"FW EndOfWeek", [FW EndOfWeek],

"FW RelativeWeekPos", [FW RelativeWeekPos],

"FW RelativeMonthPos", [FW RelativeMonthPos],

"FW RelativeQuarterPos", [FW RelativeQuarterPos],

"FW RelativeYearPos", [FW RelativeYearPos],

"FW StartOfMonth", [FW StartOfMonth],

"FW EndOfMonth", [FW EndOfMonth],

"FW StartOfQuarter", [FW StartOfQuarter],

"FW EndOfQuarter", [FW EndOfQuarter],

"FW StartOfYear", [FiscalFirstDayOfYear],

"FW EndOfYear", [FiscalLastDayOfYear],

"FW MonthDays", [FW MonthDays],

"FW QuarterDays", [FW QuarterDays],

"FW YearDays", [FW YearDays],

"FW DayOfMonthNumber", [FW DayOfMonthNumber],

"FW DayOfQuarterNumber", [FW DayOfQuarterNumber],

"FW DayOfYearNumber", [FW DayOfYearNumber],

"FW DatePreviousWeek", [FW DatePreviousWeek],

"FW DatePreviousMonth", [FW DatePreviousMonth],

"FW DatePreviousQuarter", [FW DatePreviousQuarter],

"FW DatePreviousYear", [FW DatePreviousYear]

)

RETURN Result

keekee · ‎11-14-2022

HI, We are using DirectQuery with Analysis services so I cannot embed this code into any tables in Visual Studio. If someone had a DAX solution that would be great;

TIA

daXtreme · ‎11-12-2022

No idea if anyone will have time to read this... I seriously doubt it; writing such code means the model is most likely wrong. Please go to this website www.daxpatterns.com and find the articles/guides about fiscal year calendars. Please do not re-invent the wheel.

Calendar for 13 period Fiscal year

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024