How do you create column for fiscal year that is different from calendar year with Label
Hello everyone, I am trying to create our fiscal calendar with the following logic/conditions and label in the slicer: *Sep, Oct, Nov and Dec 2023 will be labeled as "2023 Q4", while Jan-Dec of the following year should show the normal quarter as Q1, Q2, Q3, Q4. I have created a fiscal year calendar below: FY Calendar = VAR _calendar = CALENDAR( "9/1/2023","12/31/2024") RETURN ADDCOLUMNS ( _calendar, "YEAR", YEAR ( [Date] ), "MonthNumber", MONTH ([Date] ), "Month", FORMAT( [Date], "mmmm" ), "Quarter", "QTR" & FORMAT( [Date], "Q"), "QuarterNumber", FORMAT( [Date], "Q"), "MonthYearNumber", FORMAT([Date], "yy mm"), "Month Year", FORMAT([Date],"mmm yyyy")) I have been dealing with for 2 days and having no luck. It sounds easy but already having headache. I am not sure if I am missing condition. Hope somebody can help.
Calendar for 13 period Fiscal year
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
