SortByColumn property set to an invalid column ID

Hi Tom,

I closed and re-opened that PBIX file.  I double checked that there is only one value in my sort order column for each Week-Year value, and there are no nulls or blanks in either column.  Still not working though unfortunately

I checked the data type on other columns in this table that are being used to sort, and changed it to mirror those, still same issue.  I have attached the code for my date table below.  I am trying to sort "Week + Year (Order) - Format: (W## - YYY)" by the column "Sorting Order - Weeks"

The DAX code for the calendar table is very long, I will break it up into a few replies

DAX Table Code 1.

Date = 
------------------------------------------------------------
--
-- Configuration
--
------------------------------------------------------------
VAR TodayReference =
    TODAY () -- Change this if you need to use another date as a reference "current" day
VAR FirstYear =
    YEAR ( TodayReference )-7
VAR LastYear = 
    YEAR ( TodayReference )+5
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 = "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, ... 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, "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" }
        }
    ) ------------------------------------------------------------
--  
-- End of General Configuration
--
------------------------------------------------------------
--  
-- The following variables define specific parameters 
-- for calendars - you should modify them only to 
-- change configuration of specific countries, translate 
-- names of holidays, or to add configuration for other 
-- countries
--
------------------------------------------------------------
VAR InLieuOf_prefix = "(in lieu of " -- prefix of substitute holidays
VAR InLieuOf_suffix = ")" -- prefix of substitute holidays
VAR HolidayParameters =
    DATATABLE (
        "ISO Country", STRING,
        -- ISO country code (to enable filter based on country)
        "MonthNumber", INTEGER,
        -- Number of month
        "DayNumber", INTEGER,
        -- Absolute day (ignore WeekDayNumber, otherwise use 0)
        "WeekDayNumber", INTEGER,
        -- 0 = Sunday, 1 = Monday, ... , 7 = Saturday
        "OffsetWeek", INTEGER,
        -- 1 = first, 2 = second, ... -1 = last, -2 = second-last, ...
        "HolidayName", STRING,
        -- Holiday name 
        "SubstituteHoliday", INTEGER,
        -- 0 = no substituteHoliday, 1 = substitute holiday with next working day, 2 = substitute holiday with next working day 
        -- (use 2 before 1 only, e.g. Christmas = 2, Boxing Day = 1)
        "ConflictPriority", INTEGER,
        -- Priority in case of two or more holidays in the same date - lower number --> higher priority
        -- For example: marking Easter relative days with 150 and other holidays with 100 means that other holidays take 
        --              precedence over Easter-related days; use 50 for Easter related holidays to invert such a priority
        {
            --
            -- US = United States
            { "US", 1, 0, 1, 1, "New Year's Day", 0, 100 },
--            { "US", 1, 0, 1, 3, "Martin Luther King, Jr.", 0, 100 },
--            { "US", 2, 0, 1, 3, "Presidents' Day", 0, 100 },
            // aka Washington's Birthday
            { "US", 5, 0, 1, -1, "Memorial Day", 0, 100 },
            { "US", 7, 0, 1, 1, "Independence Day", 0, 100 },
            { "US", 9, 0, 1, 1, "Labor Day", 0, 100 },
--            { "US", 10, 0, 1, 2, "Columbus Day", 0, 100 },
--            { "US", 11, 11, 0, 0, "Veterans Day", 0, 100 },
            { "US", 11, 0, 4, 4, "Thanksgiving Day", 0, 100 },
            { "US", 11, 0, 5, 4, "Thanksgiving Day Friday", 0, 100 },
            { "US", 12, 0, 3, 4, "Christmas Eve", 0, 100 },
            { "US", 12, 0, 4, 4, "Christmas Day", 0, 100 },
            { "US", 12, 0, 5, 4, "New Year's Eve", 0, 100 },
            --
            -- CA = Canada (include only nationwide and Thanksgiving)
            { "CA", 1, 1, 0, 0, "New Year's Day", 0, 100 },
            { "CA", 99, -2, 0, 0, "Good Friday", 0, 50 },
            { "CA", 7, 1, 0, 0, "Canada Day", 0, 100 },
            { "CA", 9, 0, 1, 1, "Labour Day", 0, 100 },
            { "CA", 10, 0, 1, 2, "Thanksgiving", 0, 100 },
            { "CA", 12, 25, 0, 0, "Christmas Day", 0, 100 },
            --
            -- UK = England (different configuration in Scotland and Northern Ireland)
            { "UK", 1, 1, 0, 0, "New Year's Day", 1, 100 },
            { "UK", 99, -2, 0, 0, "Good Friday", 0, 50 },
            { "UK", 99, 1, 0, 0, "Easter Monday", 0, 50 },
            { "UK", 5, 0, 1, 1, "May Day Bank Holiday", 0, 100 },
            { "UK", 5, 0, 1, -1, "Spring Bank Holiday", 0, 100 },
            { "UK", 8, 0, 1, -1, "Late Summer Bank Holiday", 0, 100 },
            { "UK", 12, 25, 0, 0, "Christmas Day", 2, 100 },
            { "UK", 12, 26, 0, 0, "Boxing Day", 1, 100 },
            --
            -- AU = Australia
            { "AU", 1, 1, 0, 0, "New Year's Day", 1, 100 },
            { "AU", 1, 26, 0, 0, "Australia Day", 1, 100},
            { "AU", 99, -2, 0, 0, "Good Friday", 0, 50 },
            { "AU", 99, 1, 0, 0, "Easter Monday", 0, 50 },
            { "AU", 4, 25, 0, 0, "Anzac Day", 1, 100 },
            { "AU", 12, 25, 0, 0, "Christmas Day", 2, 100 },
            { "AU", 12, 26, 0, 0, "Boxing Day", 1, 100 },
            --
            -- DE = Germany
            { "DE", 1, 1, 0, 0, "New Year's Day", 0, 100 },
            { "DE", 99, -2, 0, 0, "Good Friday", 0, 50 },
            { "DE", 99, 1, 0, 0, "Easter Monday", 0, 50 },
            { "DE", 5, 1, 0, 0, "Labour Day", 0, 100 },
            { "DE", 99, 39, 0, 0, "Ascension Day", 0, 50 },
            { "DE", 99, 50, 0, 0, "Whit Monday", 0, 50 },
            { "DE", 10, 3, 0, 0, "German Unity Day", 0, 100 },
            { "DE", 12, 25, 0, 0, "Christmas Day", 0, 100 },
            { "DE", 12, 26, 0, 0, "St. Stephen's Day", 0, 100 },
            --
            -- FR = France
            { "FR", 1, 1, 0, 0, "New Year's Day", 0, 100 },
            { "FR", 99, 1, 0, 0, "Easter Monday", 0, 50 },
            { "FR", 5, 1, 0, 0, "Labour Day", 0, 100 },
            { "FR", 5, 8, 0, 0, "Victor in Europe Day", 0, 100 },
            { "FR", 99, 39, 0, 0, "Ascension Day", 0, 50 },
            { "FR", 99, 50, 0, 0, "Whit Monday", 0, 50 },
            { "FR", 7, 14, 0, 0, "Bastille Day", 0, 100 },
            { "FR", 8, 15, 0, 0, "Assumption Day", 0, 100 },
            { "FR", 11, 1, 0, 0, "All Saints' Day", 0, 100 },
            { "FR", 11, 11, 0, 0, "Armistice Day", 0, 100 },
            { "FR", 12, 25, 0, 0, "Christmas Day", 0, 100 },
            --
            -- IT = Italy
            { "IT", 1, 1, 0, 0, "New Year's Day", 0, 100 },
            { "IT", 1, 6, 0, 0, "Epiphany", 0, 100 },
            { "IT", 99, 1, 0, 0, "Easter Monday", 0, 100 },
            { "IT", 4, 25, 0, 0, "Liberation Day", 0, 100 },
            { "IT", 5, 1, 0, 0, "Labour Day", 0, 100 },
            { "IT", 6, 2, 0, 0, "Republic Day", 0, 100 },
            { "IT", 8, 15, 0, 0, "Assumption Day", 0, 100 },
            { "IT", 11, 1, 0, 0, "All Saints' Day", 0, 100 },
            { "IT", 12, 8, 0, 0, "Immaculate Conception", 0, 100 },
            { "IT", 12, 25, 0, 0, "Christmas Day", 0, 100 },
            { "IT", 12, 26, 0, 0, "St. Stephen's Day", 0, 100 },
            --
            -- ES = Spain
            { "ES", 1, 1, 0, 0, "New Year's Day", 0, 100 },
            { "ES", 1, 6, 0, 0, "Epiphany", 0, 100 },
            { "ES", 99, -3, 0, 0, "Maundy Thursday", 0, 50 },
            // Except Catalonia
            { "ES", 99, -2, 0, 0, "Good Friday", 0, 50 },
            { "ES", 99, 1, 0, 0, "Easter Monday", 0, 50 },
            // Belearic Islands, Basque Country, Catalonia, La Rioja, Navarra and Valenciana only
            { "ES", 5, 1, 0, 0, "Labour Day", 0, 100 },
            { "ES", 8, 15, 0, 0, "Assumption Day", 0, 100 },
            { "ES", 10, 12, 0, 0, "Fiesta Navional de España", 0, 100 },
            { "ES", 11, 1, 0, 0, "All Saints' Day", 0, 100 },
            { "ES", 12, 6, 0, 0, "Constitution Day", 0, 100 },
            { "ES", 12, 8, 0, 0, "Immaculate Conception", 0, 100 },
            { "ES", 12, 25, 0, 0, "Christmas Day", 0, 100 },
            --
            -- NL = The Netherlands
            { "NL", 1, 1, 0, 0, "New Year's Day", 0, 100 },
            { "NL", 99, 1, 0, 0, "Easter Monday", 0, 50 },
            { "NL", 99, 39, 0, 0, "Ascension Day", 0, 50 },
            { "NL", 99, 50, 0, 0, "Whit Monday", 0, 50 },
            { "NL", 4, 27, 0, 0, "King's Day", 0, 100 },
            // King's day shifted to Saturday if on a Sunday - not handled in this calendar
            { "NL", 5, 5, 0, 0, "Liberation Day", 0, 100 },
            { "NL", 12, 25, 0, 0, "Christmas Day", 0, 100 },
            { "NL", 12, 26, 0, 0, "St. Stephen's Day", 0, 100 },
            --
            -- BE = Belgium
            { "BE", 1, 1, 0, 0, "New Year's Day", 0, 100 },
            { "BE", 99, 1, 0, 0, "Easter Monday", 0, 50 },
            { "BE", 99, 39, 0, 0, "Ascension Day", 0, 50 },
            { "BE", 99, 50, 0, 0, "Whit Monday", 0, 50 },
            { "BE", 5, 1, 0, 0, "Labour Day", 0, 100 },
            { "BE", 7, 21, 0, 0, "Belgian National DayDay", 0, 100 },
            { "BE", 8, 15, 0, 0, "Assumption Day", 0, 100 },
            { "BE", 11, 1, 0, 0, "All Saints' Day", 0, 100 },
            { "BE", 11, 11, 0, 0, "Armistice Day", 0, 100 },
            { "BE", 12, 25, 0, 0, "Christmas Day", 0, 100 },
			--
            -- PT = Portugal
            { "PT", 1, 1, 0, 0, "New Year's Day", 0, 100 },
            { "PT", 99, -2, 0, 0, "Good Friday", 0, 50 },
            { "PT", 99, 60, 0, 0, "Corpus Christi", 0, 50 },
            { "PT", 4, 25, 0, 0, "Freedom Day", 0, 100 },
            { "PT", 5, 1, 0, 0, "Labour Day", 0, 100 },
            { "PT", 6, 10, 0, 0, "Portugal Day", 0, 100 },
            { "PT", 8, 15, 0, 0, "Assumption Day", 0, 100 },
            { "PT", 10, 5, 0, 0, "Republic Day", 0, 100 },
            { "PT", 11, 1, 0, 0, "All Saints' Day", 0, 100 },
            { "PT", 12, 1, 0, 0, "Restoration of Independence", 0, 100 },
            { "PT", 12, 8, 0, 0, "Immaculate Conception", 0, 100 },
            { "PT", 12, 25, 0, 0, "Christmas Day", 0, 100 }

        }
    )
VAR HolidayDates_ConfigGeneration =
    FILTER (
        HolidayParameters,
        IF (
            CONTAINS ( HolidayParameters, [ISO Country], IsoCountryHolidays )
                || IsoCountryHolidays = "",
            [ISO Country] = IsoCountryHolidays,
            ERROR ( "IsoCountryHolidays set to an unsupported contry code" )
        )
    )
VAR HolidayDates_GeneratedRawWithDuplicates =
    GENERATE (
        GENERATE (
            GENERATESERIES ( FirstYear - 1, LastYear + 1, 1 ),
            HolidayDates_ConfigGeneration
        ),
        VAR HolidayYear = [Value]
        VAR EasterDate =
            -- Code adapted from original VB version from https://www.assa.org.au/edm 
            VAR EasterYear = HolidayYear
            VAR FirstDig =
                INT ( EasterYear / 100 )
            VAR Remain19 =
                MOD ( EasterYear, 19 ) //
            -- Calculate PFM date
            VAR temp1 =
                MOD (
                    INT ( ( FirstDig - 15 ) / 2 )
                        + 202
                        - 11 * Remain19
                        + SWITCH (
                            TRUE,
                            FirstDig IN { 21, 24, 25, 27, 28, 29, 30, 31, 32, 34, 35, 38 }, -1,
                            FirstDig IN { 33, 36, 37, 39, 40 }, -2,
                            0
                        ),
                    30
                )
            VAR tA =
                temp1 + 21
                    + IF ( temp1 = 29 || ( temp1 = 28 && Remain19 > 10 ), -1 ) // 
            -- Find the next Sunday
            VAR tB =
                MOD ( tA - 19, 7 )
            VAR tCpre =
                MOD ( 40 - FirstDig, 4 )
            VAR tC =
                tCpre
                    + IF ( tCpre = 3, 1 )
                    + IF ( tCpre > 1, 1 )
            VAR temp2 =
                MOD ( EasterYear, 100 )
            VAR tD =
                MOD ( temp2 + INT ( temp2 / 4 ), 7 )
            VAR tE =
                MOD ( 20 - tB - tC - tD, 7 )
                    + 1
            VAR d = tA + tE //
            -- Return the date
            VAR EasterDay =
                IF ( d > 31, d - 31, d )
            VAR EasterMonth =
                IF ( d > 31, 4, 3 )
            RETURN
                DATE ( EasterYear, EasterMonth, EasterDay ) //
        -- End of code adapted from original VB version from https://www.assa.org.au/edm 
        VAR HolidayDate =
            SWITCH (
                TRUE,
                [DayNumber] <> 0
                    && [WeekDayNumber] <> 0, ERROR ( "Wrong configuration in HolidayParameters" ),
                [DayNumber] <> 0
                    && [MonthNumber] <= 12, DATE ( HolidayYear, [MonthNumber], [DayNumber] ),
                [MonthNumber] = 99, -- Easter offset
                EasterDate + [DayNumber],
                [WeekDayNumber] <> 0,
                VAR ReferenceDate =
                    DATE ( HolidayYear, 1
                        + MOD ( [MonthNumber] - 1 + IF ( [OffsetWeek] < 0, 1 ), 12 ), 1 )
                        - IF ( [OffsetWeek] < 0, 1 )
                VAR ReferenceWeekDayNumber =
                    WEEKDAY ( ReferenceDate, 1 ) - 1
                VAR Offset =
                    [WeekDayNumber] - ReferenceWeekDayNumber
                        + 7 * [OffsetWeek]
                        + IF (
                            [OffsetWeek] > 0,
                            IF ( [WeekDayNumber] >= ReferenceWeekDayNumber, - 7 ),
                            IF ( ReferenceWeekDayNumber >= [WeekDayNumber], 7 )
                        )
                RETURN
                    ReferenceDate + Offset,
                ERROR ( "Wrong configuration in HolidayParameters" )
            )
        VAR HolidayDay =
            WEEKDAY ( HolidayDate, 1 ) - 1
        VAR SubstituteHolidayOffset =
            IF (
                [SubstituteHoliday] > 0
                    && NOT CONTAINS ( WorkingDays, [WorkingDayNumber], HolidayDay ),
                VAR NextWorkingDay =
                    MINX (
                        FILTER ( WorkingDays, [WorkingDayNumber] > HolidayDay ),
                        [WorkingDayNumber]
                    )
                VAR SubstituteDay =
                    IF (
                        ISBLANK ( NextWorkingDay ),
                        MINX ( WorkingDays, [WorkingDayNumber] ) + 7,
                        NextWorkingDay
                    )
                RETURN
                    SubstituteDay - HolidayDay
                        + ( [SubstituteHoliday] - 1 )
            )
        RETURN
            ROW (
                -- Use DATE function to get a DATE column as a result 
                "HolidayDate", DATE ( YEAR ( HolidayDate ), MONTH ( HolidayDate ), DAY ( HolidayDate ) ),
                "SubstituteHolidayOffset", SubstituteHolidayOffset
            )
    ) //
VAR HolidayDates_RawDatesUnique = 
    DISTINCT ( 
        SELECTCOLUMNS ( 
            HolidayDates_GeneratedRawWithDuplicates,
            "HolidayDateUnique", [HolidayDate]
        )
    )
VAR HolidayDates_GeneratedRaw = 
    GENERATE (
        HolidayDates_RawDatesUnique,
        VAR FilterDate = [HolidayDateUnique]
        RETURN 
            TOPN (
                1,
                FILTER ( 
                    HolidayDates_GeneratedRawWithDuplicates,
                    [HolidayDate] = FilterDate
                    && [SubstituteHoliday] = 0 -- Remove duplicates only if no substitute holidays
                ),
                [ConflictPriority],
                ASC,
                [HolidayName], 
                ASC
            )
    )  

Dax table Code 2

VAR HolidayDates_GeneratedSubstitutes =
    SELECTCOLUMNS (
        FILTER ( HolidayDates_GeneratedRawWithDuplicates, [SubstituteHoliday] > 0 ),
        "Value", [Value],
        "ISO Country", [ISO Country],
        "MonthNumber", [MonthNumber],
        "DayNumber", [DayNumber],
        "WeekDayNumber", [WeekDayNumber],
        "OffsetWeek", [OffsetWeek],
        "HolidayName", [HolidayName],
        "SubstituteHoliday", [SubstituteHoliday],
        "ConflictPriority", [ConflictPriority],
        "HolidayDate", [HolidayDate],
        "SubstituteHolidayOffset", 
            VAR CurrentHolidayDate = [HolidayDate]
            VAR CurrentHolidayName = [HolidayName]
            VAR OriginalSubstituteDate = [HolidayDate] + [SubstituteHolidayOffset]
            VAR OtherHolidays = 
                FILTER ( 
                    HolidayDates_GeneratedRawWithDuplicates, 
                    [HolidayDate] <> CurrentHolidayDate
                    || [HolidayName] <> CurrentHolidayName
                )
            VAR ConflictDay0 = 
                CONTAINS ( 
                    OtherHolidays,
                    [HolidayDate], OriginalSubstituteDate
                )
            VAR ConflictDay1 = 
                ConflictDay0 
                && CONTAINS ( 
                    OtherHolidays,
                    [HolidayDate], OriginalSubstituteDate + 1
                )
            VAR ConflictDay2 = 
                ConflictDay1 
                && CONTAINS ( 
                    OtherHolidays,
                    [HolidayDate], OriginalSubstituteDate + 2
                )
            VAR SubstituteOffsetStep1 = [SubstituteHolidayOffset] + ConflictDay0 + ConflictDay1 + ConflictDay2
            VAR HolidayDateStep1 = CurrentHolidayDate + SubstituteOffsetStep1
            VAR HolidayDayStep1 =
                WEEKDAY ( HolidayDateStep1, 1 ) - 1
            VAR SubstituteHolidayOffsetNonWorkingDays =
                IF (
                    NOT CONTAINS ( WorkingDays, [WorkingDayNumber], HolidayDayStep1 ),
                    VAR NextWorkingDayStep2 =
                        MINX (
                            FILTER ( WorkingDays, [WorkingDayNumber] > HolidayDayStep1 ),
                            [WorkingDayNumber]
                        )
                    VAR SubstituteDay =
                        IF (
                            ISBLANK ( NextWorkingDayStep2 ),
                            MINX ( WorkingDays, [WorkingDayNumber] ) + 7,
                            NextWorkingDayStep2
                        )
                    RETURN SubstituteDay - HolidayDateStep1
                )
            VAR SubstituteOffsetStep2 = SubstituteOffsetStep1 + SubstituteHolidayOffsetNonWorkingDays
            VAR SubstituteDateStep2 = OriginalSubstituteDate + SubstituteOffsetStep2
            VAR ConflictDayStep2_0 = 
                CONTAINS ( 
                    OtherHolidays,
                    [HolidayDate], SubstituteDateStep2
                )
            VAR ConflictDayStep2_1 = 
                ConflictDayStep2_0
                && CONTAINS ( 
                    OtherHolidays,
                    [HolidayDate], SubstituteDateStep2 + 1
                )
            VAR ConflictDayStep2_2 = 
                ConflictDayStep2_1 
                && CONTAINS ( 
                    OtherHolidays,
                    [HolidayDate], SubstituteDateStep2 + 2
                )
            VAR FinalSubstituteHolidayOffset = 
                SubstituteOffsetStep2 + ConflictDayStep2_0 + ConflictDayStep2_1 + ConflictDayStep2_2
            RETURN
                FinalSubstituteHolidayOffset
        )
VAR HolidayDates_Generated =
    UNION (
        SELECTCOLUMNS (
            HolidayDates_GeneratedRaw,
            "HolidayDate", [HolidayDate],
            "HolidayName", [HolidayName]
        ),
        SELECTCOLUMNS (
            FILTER ( HolidayDates_GeneratedSubstitutes, [SubstituteHolidayOffset] <> 0 ), 
            "HolidayDate", [HolidayDate] + [SubstituteHolidayOffset],
            "HolidayName", InLieuOf_prefix & [HolidayName]
                & InLieuOf_suffix
        )
    ) -- Alternative way to express holidays: create a table with the list of the dates
-- The following table should be used instead of HolidayDates_Generated in the HolidayDates variable
VAR HolidayDates_US_ExplicitDates =
    DATATABLE (
        "HolidayDate", DATETIME,
        "HolidayName", STRING,
        {
            { "2008-01-01", "New Year's Day" },
            { "2008-12-25", "Christmas Day" },
            -------------------------
            { "2008-11-27", "Thanksgiving Day" },
            { "2009-11-26", "Thanksgiving Day" },
            { "2010-11-25", "Thanksgiving Day" },
            { "2011-11-24", "Thanksgiving Day" },
            { "2012-11-22", "Thanksgiving Day" },
            { "2013-11-28", "Thanksgiving Day" },
            { "2014-11-27", "Thanksgiving Day" },
            { "2015-11-26", "Thanksgiving Day" },
            { "2016-11-24", "Thanksgiving Day" },
            { "2017-11-23", "Thanksgiving Day" },
            { "2018-11-22", "Thanksgiving Day" },
            { "2019-11-28", "Thanksgiving Day" },
            { "2020-11-26", "Thanksgiving Day" }
        }
    )
VAR HolidayDates =
    SELECTCOLUMNS (
        HolidayDates_Generated,
        "Date", [HolidayDate],
        "Holiday Name", [HolidayName]
    ) //
------------------------------------------------------------
--  
-- End of Configuration
--
------------------------------------------------------------
--  
-- The following variables define 
-- the content of the calendar tables
--
------------------------------------------------------------
------------------------------------------------------------
VAR FirstDayCalendar =
    DATE ( FirstYear - 1, 1, 1 )
VAR LastDayCalendar =
    DATE ( LastYear + 1, 12, 31 )
VAR WeekDayCalculationType =
    IF ( FirstDayOfWeek = 0, 7, FirstDayOfWeek )
        + 10
VAR WeeklyFiscalPeriods =
    GENERATE (
        SELECTCOLUMNS (
            GENERATESERIES ( FirstYear - 1, LastYear + 1, 1 ),
            "CalendarType", "Weekly",
            "Fiscal YearNumber", [Value]
        ),
        VAR StartFiscalYearNumber = [Fiscal YearNumber] + IF ( FiscalCalendarFirstMonth > 1, -1, 0 )
        VAR FirstDayCurrentYear =
            DATE ( StartFiscalYearNumber, FiscalCalendarFirstMonth, 1 )
        VAR FirstDayNextYear =
            DATE ( StartFiscalYearNumber + 1, FiscalCalendarFirstMonth, 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 ( "FirstDayOfYear", FirstDayOfFiscalYear,
            "LastDayOfYear", LastDayOfFiscalYear )
    )
VAR CheckFirstDayOfWeek =
    IF (
        WEEKDAY ( MINX ( CustomFiscalPeriods, [FirstDayOfYear] ), 1 )
            <> ( FirstDayOfWeek + 1 ),
        ERROR ( "CustomFiscalPeriods table does not match FirstDayOfWeek setting" ),
        TRUE
    )
VAR CustomFiscalPeriodsWithType =
    GENERATE (
        ROW ( "CalendarType", "Custom" ),
        FILTER ( CustomFiscalPeriods, CheckFirstDayOfWeek )
    )
VAR FiscalPeriods =
    SELECTCOLUMNS (
        FILTER (
            UNION ( WeeklyFiscalPeriods, CustomFiscalPeriodsWithType ),
            [CalendarType] = WeeklyCalendarType
        ),
        "FW YearNumber", [Fiscal YearNumber],
        "FW StartOfYear", [FirstDayOfYear],
        "FW EndOfYear", [LastDayOfYear]
    )
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 RawDays =
    CALENDAR ( FirstDayCalendar, LastDayCalendar )
VAR CalendarGregorianPrefixSpace =
    IF ( CalendarGregorianPrefix <> "", CalendarGregorianPrefix & " ", "" )
VAR FiscalGregorianPrefixSpace =
    IF ( FiscalGregorianPrefix <> "", FiscalGregorianPrefix & " ", "" )
VAR FiscalWeeklyPrefixSpace =
    IF ( FiscalWeeklyPrefix <> "", FiscalWeeklyPrefix & " ", "" )
VAR CustomFiscalRawDays =
    GENERATE ( FiscalPeriods, CALENDAR ( [FW StartOfYear], [FW EndOfYear] ) )
VAR CalendarStandardGregorianBase =
    GENERATE (
        NATURALLEFTOUTERJOIN ( RawDays, HolidayDates ),
        VAR CalDate = [Date]
        VAR CalYear =
            YEAR ( [Date] )
        VAR CalMonthNumber =
            MONTH ( [Date] )
        VAR CalQuarterNumber =
            ROUNDUP ( CalMonthNumber / 3, 0 )
        VAR CalDay =
            DAY ( [Date] )
        VAR CalWeekNumber =
            WEEKNUM ( CalDate, WeekDayCalculationType )
        VAR CalDayOfMonth =
            DAY ( CalDate )
        VAR WeekDayNumber =
            WEEKDAY ( CalDate, WeekDayCalculationType )
        VAR YearWeekNumber =
            INT ( DIVIDE ( CalDate - FirstWeekReference, 7 ) )
        VAR CalendarFirstDayOfYear =
            DATE ( CalYear, 1, 1 )
        VAR CalendarFirstDayOfQuarter =
            DATE ( CalYear, CalQuarterNumber, 1 )
        VAR CalendarDayOfQuarter =
            INT ( CalDate - CalendarFirstDayOfQuarter + 1 )
        VAR CalendarDayOfYear =
            INT ( CalDate - CalendarFirstDayOfYear + 1 )
        VAR IsPast =
            if ( CalDate<=TodayReference,TRUE,FALSE)
        VAR IsPastPrior =
            if ( CalDate<TodayReference,TRUE,FALSE)
        VAR IsWorkingDay =
            CONTAINS ( WorkingDays, [WorkingDayNumber], WEEKDAY ( CalDate, 1 ) - 1 )
                && ISBLANK ( [Holiday Name] )
        VAR _CheckLeapYearBefore =
            CalYear -
            IF ( (CalMonthNumber = 2 && CalDayOfMonth < 29)
                     || CalMonthNumber < 2,
                1,
                0 )
        VAR LeapYearsBefore1900 =
            INT ( 1899 / 4 )
                - INT ( 1899 / 100 )
                + INT ( 1899 / 400 )
        VAR LeapYearsBetween =
            INT ( _CheckLeapYearBefore / 4 )
                - INT ( _CheckLeapYearBefore / 100 )
                + INT ( _CheckLeapYearBefore / 400 )
                - LeapYearsBefore1900
        VAR Sequential365DayNumber =
            INT ( CalDate - LeapYearsBetween ) 
        RETURN
            ROW (
                "DateKey", CalYear * 10000
                    + CalMonthNumber * 100
                    + CalDay,
                "Calendar YearNumber", CalYear,
                "Calendar Year", CalendarGregorianPrefixSpace & CalYear,
                "Calendar QuarterNumber", CalQuarterNumber,
                "Calendar Quarter", CalendarGregorianPrefix & "Q"
                    & CalQuarterNumber
                    & " ",
                "Calendar YearQuarterNumber", CalYear * 4
                    - 1
                    + CalQuarterNumber,
                "Calendar Quarter Year", CalendarGregorianPrefix & "Q"
                    & CalQuarterNumber
                    & " "
                    & CalYear,
                "Calendar MonthNumber", CalMonthNumber,
                "Calendar Month", FORMAT ( CalDate, "mmm" ),
                "Calendar YearMonthNumber", CalYear * 12
                    - 1
                    + CalMonthNumber,
                "Calendar Month Year", FORMAT ( CalDate, "mmm" ) & " "
                    & CalYear,
                "Calendar WeekNumber", CalWeekNumber,
                "Calendar Week", CalendarGregorianPrefix & "W"
                    & FORMAT ( CalWeekNumber, "00" ),
                "Calendar YearWeekNumber", YearWeekNumber,
                "Calendar Week Year", CalendarGregorianPrefix & "W"
                    & FORMAT ( CalWeekNumber, "00" )
                    & "-"
                    & CalYear,
                "Calendar WeekYearOrder", CalYear * 100
                    + CalWeekNumber,
                "Calendar DayOfYearNumber", CalendarDayOfYear,
                "Day of Month", CalDayOfMonth,
                "WeekDayNumber", WeekDayNumber,
                "Week Day", FORMAT ( CalDate, "ddd" ),
                "IsWorkingDay", IsWorkingDay,
                "Day Type", IF ( IsWorkingDay, WorkingDayType, NonWorkingDayType ),
                "Sequential365DayNumber", Sequential365DayNumber,
                "Calendar First Day Of Quarter", CalendarFirstDayOfQuarter,
                "Is Past",IsPast,
                "Is Past Prior",IsPastPrior
            )
    )
VAR CalendarStandardGregorian =
    GENERATE (
        CalendarStandardGregorianBase,
        VAR CalDate = [Date]
        VAR YearNumber = [Calendar YearNumber]
        VAR MonthNumber = [Calendar MonthNumber]
        VAR YearWeekNumber = [Calendar YearWeekNumber]
        VAR YearMonthNumber = [Calendar YearMonthNumber]
        VAR YearQuarterNumber = [Calendar YearQuarterNumber]
        VAR CurrentWeekPos =
            AVERAGEX (
                FILTER ( CalendarStandardGregorianBase, [Date] = TodayReference ),
                [Calendar YearWeekNumber]
            )
        VAR CurrentMonthPos =
            AVERAGEX (
                FILTER ( CalendarStandardGregorianBase, [Date] = TodayReference ),
                [Calendar YearMonthNumber]
            )
        VAR CurrentQuarterPos =
            AVERAGEX (
                FILTER ( CalendarStandardGregorianBase, [Date] = TodayReference ),
                [Calendar YearQuarterNumber]
            )
        VAR CurrentYearPos =
            AVERAGEX (
                FILTER ( CalendarStandardGregorianBase, [Date] = TodayReference ),
                [Calendar YearNumber]
            )
        VAR RelativeWeekPos = CurrentWeekPos - YearWeekNumber
        VAR RelativeMonthPos = CurrentMonthPos - YearMonthNumber
        VAR RelativeQuarterPos = CurrentQuarterPos - YearQuarterNumber
        VAR RelativeYearPos = CurrentYearPos - YearNumber
        VAR CalStartOfMonth =
            DATE ( YearNumber, MonthNumber, 1 )
        VAR CalEndOfMonth =
            EOMONTH ( CalDate, 0 )
        VAR CalMonthDays = 
            INT ( CalEndOfMonth - CalStartOfMonth + 1 ) 
        VAR CalDayOfMonthNumber =
            INT ( CalDate - CalStartOfMonth + 1 )
        VAR CalStartOfQuarter =
            MINX (
                FILTER (
                    CalendarStandardGregorianBase,
                    [Calendar YearQuarterNumber] = YearQuarterNumber
                ),
                [Date]
            )
        VAR CalEndOfQuarter =
            MAXX (
                FILTER (
                    CalendarStandardGregorianBase,
                    [Calendar YearQuarterNumber] = YearQuarterNumber
                ),
                [Date]
            )
        VAR CalQuarterDays =
            INT ( CalEndOfQuarter - CalStartOfQuarter + 1 )         
        VAR CalDayOfQuarterNumber =
            INT ( CalDate - CalStartOfQuarter + 1 )
        VAR CalYearDays =
            INT ( DATE ( YearNumber, 12, 31 ) - DATE ( YearNumber, 1, 1 ) + 1 )
        VAR CalDatePreviousWeek = CalDate - 7
        VAR CalDatePreviousMonth = 
            MAXX (
                FILTER (
                    CalendarStandardGregorianBase,
                    [Calendar YearMonthNumber] = YearMonthNumber - 1
                    &&
                    ( [Day of Month] <= CalDayOfMonthNumber
                      || CalDayOfMonthNumber = CalMonthDays )
                ),
                [Date]
            )
        VAR CalDatePreviousQuarter = 
            MAXX (
                FILTER (
                    CalendarStandardGregorianBase,
                    [Calendar YearMonthNumber] = YearMonthNumber - 3
                    &&
                    ( [Day of Month] <= CalDayOfMonthNumber
                      || CalDayOfMonthNumber = CalMonthDays )
                ),
                [Date]
            )
        VAR CalDatePreviousYear = 
            MAXX (
                FILTER (
                    CalendarStandardGregorianBase,
                    [Calendar YearMonthNumber] = YearMonthNumber - 12
                    &&
                    ( [Day of Month] <= CalDayOfMonthNumber
                      || CalDayOfMonthNumber = CalMonthDays )
                ),
                [Date]
            )
        VAR CalStartOfYear =
            DATE ( YearNumber, 1, 1 )
        VAR CalEndOfYear =
            DATE ( YearNumber, 12, 31 )
        RETURN
            ROW ( "Calendar RelativeWeekPos", RelativeWeekPos,
            "Calendar RelativeMonthPos", RelativeMonthPos,
            "Calendar RelativeQuarterPos", RelativeQuarterPos,
            "Calendar RelativeYearPos", RelativeYearPos,
            "Calendar StartOfMonth", CalStartOfMonth,
            "Calendar EndOfMonth", CalEndOfMonth,
            "Calendar DayOfMonthNumber", CalDayOfMonthNumber,
            "Calendar StartOfQuarter", CalStartOfQuarter,
            "Calendar EndOfQuarter", CalEndOfQuarter,
            "Calendar DayOfQuarterNumber", CalDayOfQuarterNumber,            
            "Calendar StartOfYear", CalStartOfYear,
            "Calendar EndOfYear", CalEndOfYear,
            "Calendar DatePreviousWeek", CalDatePreviousWeek,
            "Calendar DatePreviousMonth", CalDatePreviousMonth,
            "Calendar DatePreviousQuarter", CalDatePreviousQuarter,
            "Calendar DatePreviousYear", CalDatePreviousYear,
            "Calendar MonthDays", CalMonthDays,
            "Calendar QuarterDays", CalQuarterDays,
            "Calendar YearDays", CalYearDays
            )
    )