Filtering by Fiscal Month Year is not working

[dnavia]

Hello!

I've been trying to set up a fiscal calendar year and so far the code has not given me any trouble.

`Date =

-- -- Configuration

---

VAR TodayReference = TODAY () -- Change this if you need to use another date as a reference "current" day VAR FirstYear = 2019 VAR LastYear = 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 TypeStartFiscalYear = 1 -- Fiscal year as Calendar Year of : 0 - First day of fiscal year, 1 - Last day of fiscal year VAR IsoCountryHolidays = "US" -- Use only supported ISO countries or "" for no holidays VAR WeeklyType = "Last" -- Use: "Nearest" or "Last" VAR QuarterWeekType = "445" -- Supports only "445", "454", and "544" VAR CalendarRange = "Calendar" -- 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 = "Weekly" -- Supports "Weekly", "Custom" -- Set the working days - 0 = Sunday, 1 = Monday, ... 6 = Saturday VAR WorkingDays = DATATABLE ( "WorkingDayNumber"; INTEGER; { { 1 }; { 2 }; { 3 }; { 4 }; { 5 } } ) --

-- Use CustomFiscalPeriods in case you need arbitrary definition of weekly fiscal years -- Set "UseCustomFiscalPeriods" to TRUE in order to use CustomFiscalPeriods VAR UseCustomFiscalPeriods = FALSE -- Set "IgnoreWeeklyFiscalPeriods" to TRUE in order to ignore the WeeklyFiscalPeriods -- You should set IgnoreWeeklyFiscalPeriods to TRUE only when UseCustomFiscalPeriods is TRUE, too VAR IgnoreWeeklyFiscalPeriods = FALSE -- Include here your own definition of custom fiscal periods VAR CustomFiscalPeriods = FILTER ( DATATABLE ( "Fiscal YearNumber"; INTEGER; "FirstDayOfYear"; DATETIME; "LastDayOfYear"; DATETIME; { -- IMPORTANT!!! The first day of each year must be a weekday corresponding to the definition of FirstDayOfWeek -- If you want to use this table, remember to set the UseCustomFiscalPeriods variable to TRUE -- If the IgnoreWeeklyFiscalPeriods is TRUE, there are no warnings in case the FirstDayOfWeek -- does not match the first day of the year { 2016; "2015-06-28"; "2016-07-02" }; { 2017; "2016-07-03"; "2017-07-01" }; { 2018; "2017-07-02"; "2018-06-30" }; { 2019; "2018-07-01"; "2019-06-29" } } ); UseCustomFiscalPeriods )`

Once I've set up the calendar and I try to filter by Fiscal Month Year (Jan 2020) and I check the dates, it shows me the calendar dates (from the 1st up to the 31 of Jan 2020).

I would really appreciate if you could help me out.

Thank you!

[marcosqlbi]

What is your expected outcome? Maybe you want to see the fiscal week calendar, which is made by columns with the prefix FW?

[dnavia]

I have a lot of data coming from a WMS (warehouse management system), which have dates columns. I want to connect those tables to the "DATE" table and be able to filter the whole report based on Fiscal months ie: Jan 2020 goes from Jan 4th to Feb 7th based on the 5-4-4. And if possible, give the option to filter by fiscal week too.

[marcosqlbi]

The columns for weekly date calendar have the prefix FW - you should look at those ones for your purpose. In case you detect errors please post the expected value and the value you obtain opening the issue. Thanks!

[dnavia]

Marco, first of all thank you for replying so fast.

I think I didn't explain myself correctly, I want to add to my report two filters, Fiscal Month Year and Fiscal Week, so my client can "dive" in the fiscal month in case he wants to see a week in more detail.

Right now I am trying to filter for the fiscal month January 2020 which goes from Jan 4th to Feb 7th, but when I go to the Data View, and from the date table i filter the Fiscal Month Year Jan 2020, I get the calendar month January 2020.

Please check the attached images.

Once again, thank you for your help!

[marcosqlbi]

You have to use columns with the prefix "FW" and create new columns to name January as Jan 4th to Feb 7th. This is currently not managed by the FW columns because there is no standard here. We prefer to use the definition "Period" because it doesn't match with the month names. The columns with the prefix Fiscal are based on calendar months. You should use the columns with "FW" prefix only for week-based calendars.

[dnavia]

Good morning Marco!

I wrote to you last night over tweeter about this issue.

From what I understand from your response, it is not possible to use both fiscal periods and fiscal weeks on the same date table, could you please confirm this?

If that is the case, I would guess that it is enough to deactivate all other columns that are not related to fiscal periods to make it work?

Once again thank you for your help and for your time!

[marcosqlbi]

I'm not saying this. I'm saying that the prefix of the columns has different meanings. We have 3 calendars in one table: Gregorian (Calendar), Fiscal based on Month (Fiscal), and Fiscal by Week (FW). You can use them at the same time, but they are not designed to be mixed. So you should not use Fiscal and FW columns within the same report. But you can add other columns if you want. This is what I meant in my answer.