Forum Discussion
SortByColumn property set to an invalid column ID
I am trying to sort my Week-Year column of my date table, by a column called "Sorting Order - Weeks". However, for this specific column I am getting the following error. "The column "Week _ Year (...
tackytechtom
Most Valuable Professional
Most Valuable ProfessionalDang it! Is the data type of the sort by column correct?
Otherwise, can you paste the DAX code of the two columns in here? Then I can simulate the issue on my end 🙂
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
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
)
)