search

sql-bi / DaxDateTemplate

Power BI template to create a Date table in DAX
MIT License
267 stars 76 forks source link

Custom Fiscal Calendar not working? #12

Closed paidcritic closed 5 years ago

paidcritic commented 5 years ago

Trying to build out a 544 Fiscal Week calendar and it seems I have not configured this correctly. My config (top section) is: Date =

-- -- Configuration

VAR TodayReference = TODAY () -- Change this if you need to use another date as a reference "current" day VAR FirstYear = 2018 VAR LastYear = 2020 -- YEAR ( TodayReference ) VAR FiscalCalendarFirstMonth = 1 -- 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 IsoCountryHolidays = "US" -- Use only supported ISO countries or "" for no holidays VAR WeeklyType = "Last" -- Use: "Nearest" or "Last" VAR QuarterWeekType = "544" -- Supports only "445", "454", and "544" VAR CalendarRange = "FiscalWeekly" -- Supports "Calendar", "FiscalGregorian", "FiscalWeekly" -- 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 -- FiscalCalendarFirstMonth = 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 CalendarGregorianPrefix = "" -- Prefix used in columns of standard Gregorian calendar VAR FiscalGregorianPrefix = "F" -- Prefix used in columns of fiscal Gregorian calendar VAR FiscalWeeklyPrefix = "FW " -- Prefix used in columns of fiscal weekly calendar VAR WorkingDayType = "Working day" -- Description for working days VAR NonWorkingDayType = "Non-working day" -- Description for non-working days

VAR WeeklyCalendarType = "Custom" -- Supports "Weekly", "Custom" -- Set the working days - 0 = Sunday, 1 = Monday, ... 7 = Saturday VAR WorkingDays = DATATABLE ( "WorkingDayNumber", INTEGER, { { 1 }, { 2 }, { 3 }, { 4 }, { 5 } } ) -- -- Use CustomFiscalPeriods in case you need arbitrary definition of weekly fiscal years -- The first day of each year must be a weekday corresponding to the definition of FirstDayOfWeek VAR CustomFiscalPeriods = DATATABLE ( "Fiscal YearNumber", INTEGER, "FirstDayOfYear", DATETIME, "LastDayOfYear", DATETIME, { { 2016, "2016-01-03", "2016-12-31" }, { 2017, "2017-01-01", "2017-12-30" }, { 2018, "2017-12-31", "2018-12-29" }, { 2019, "2018-12-30", "2019-12-28" }, { 2020, "2019-12-29", "2021-01-02" } } ) ------------------------------------------------------------ --
-- End of General Configuration

image

But the generated calendar starts at 12/31/2017 marked as F 2018 FQ4 and not F 2018 FQ1. I am sure I have some setting wrong here but cannot figure out which it is. Thanks,

marcosqlbi commented 5 years ago

This should be fixed with 1.06. Please check the new comments related to CustomFiscalPeriods if you want to use them.