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 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
)
)