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 4 (almost done)
VAR FiscalStandardGregorianBase =
GENERATE (
NATURALLEFTOUTERJOIN ( RawDays, HolidayDates ),
VAR FiscalDate = [Date]
VAR CalYear =
YEAR ( FiscalDate )
VAR CalMonthNumber =
MONTH ( FiscalDate )
VAR CalDay =
DAY ( [Date] )
VAR WeekDayNumber =
WEEKDAY ( FiscalDate, WeekDayCalculationType )
VAR YearWeekNumber =
INT ( DIVIDE ( FiscalDate - FirstWeekReference, 7 ) )
VAR FiscalYear =
CalYear
+ IF ( CalMonthNumber >= FiscalCalendarFirstMonth, 1 )
VAR FiscalMonthNumber =
MOD ( CalMonthNumber - FiscalCalendarFirstMonth, 12 )
+ 1
VAR FiscalFirstDayOfYear =
DATE ( FiscalYear - 1, FiscalCalendarFirstMonth, 1 )
VAR FiscalDayOfYear =
INT ( FiscalDate - FiscalFirstDayOfYear + 1 )
VAR FiscalFirstYearWeekNumber =
INT ( DIVIDE ( FiscalFirstDayOfYear - FirstWeekReference, 7 ) )
VAR FiscalWeekNumber = YearWeekNumber - FiscalFirstYearWeekNumber
+ 1
VAR FiscalQuarterNumber =
ROUNDUP ( FiscalMonthNumber / 3, 0 )
VAR IsWorkingDay =
CONTAINS ( WorkingDays, [WorkingDayNumber], WEEKDAY ( FiscalDate, 1 ) - 1 )
&& ISBLANK ( [Holiday Name] )
RETURN
ROW (
"DateKey", CalYear * 10000
+ CalMonthNumber * 100
+ CalDay,
"Fiscal Year", FiscalGregorianPrefixSpace & FiscalYear,
"Fiscal YearNumber", FiscalYear,
"Fiscal QuarterNumber", FiscalQuarterNumber,
"Fiscal Quarter", FiscalGregorianPrefix & "Q"
& FiscalQuarterNumber
& " ",
"Fiscal YearQuarterNumber", FiscalYear * 4
- 1
+ FiscalQuarterNumber,
"Fiscal Quarter Year", FiscalGregorianPrefix & "Q"
& FiscalQuarterNumber
& " "
& FiscalYear,
"Fiscal MonthNumber", FiscalMonthNumber,
"Fiscal Month", FORMAT ( FiscalDate, "mmm" ),
"Fiscal YearMonthNumber", FiscalYear * 12
- 1
+ FiscalMonthNumber,
"Fiscal Month Year", FORMAT ( FiscalDate, "mmm" ) & " "
& CalYear,
"Fiscal WeekNumber", FiscalWeekNumber,
"Fiscal Week", FiscalGregorianPrefix & "W"
& FORMAT ( FiscalWeekNumber, "00" ),
"Fiscal YearWeekNumber", YearWeekNumber,
"Fiscal Week Year", FiscalGregorianPrefix & "W"
& FORMAT ( FiscalWeekNumber, "00" )
& "-"
& FiscalYear,
"Fiscal WeekYearOrder", FiscalYear * 100
+ FiscalWeekNumber,
"Fiscal DayOfYearNumber", FiscalDayOfYear,
"Day of Month", DAY ( FiscalDate ),
"WeekDayNumber", WeekDayNumber,
"Week Day", FORMAT ( FiscalDate, "ddd" ),
"IsWorkingDay", IsWorkingDay,
"Day Type", IF ( IsWorkingDay, WorkingDayType, NonWorkingDayType )
)
)
VAR FiscalStandardGregorian =
GENERATE (
FiscalStandardGregorianBase,
VAR FiscalDate = [Date]
VAR FiscalYearNumber = [Fiscal YearNumber]
VAR MonthNumber = [Fiscal MonthNumber]
VAR CalendarYearNumber =
YEAR ( FiscalDate )
VAR CalendarMonthNumber =
MONTH ( FiscalDate )
VAR YearWeekNumber = [Fiscal YearWeekNumber]
VAR YearMonthNumber = [Fiscal YearMonthNumber]
VAR YearQuarterNumber = [Fiscal YearQuarterNumber]
VAR CurrentWeekPos =
AVERAGEX (
FILTER ( FiscalStandardGregorianBase, [Date] = TodayReference ),
[Fiscal YearWeekNumber]
)
VAR CurrentMonthPos =
AVERAGEX (
FILTER ( FiscalStandardGregorianBase, [Date] = TodayReference ),
[Fiscal YearMonthNumber]
)
VAR CurrentQuarterPos =
AVERAGEX (
FILTER ( FiscalStandardGregorianBase, [Date] = TodayReference ),
[Fiscal YearQuarterNumber]
)
VAR CurrentYearPos =
AVERAGEX (
FILTER ( FiscalStandardGregorianBase, [Date] = TodayReference ),
[Fiscal YearNumber]
)
VAR RelativeWeekPos = CurrentWeekPos - YearWeekNumber
VAR RelativeMonthPos = CurrentMonthPos - YearMonthNumber
VAR RelativeQuarterPos = CurrentQuarterPos - YearQuarterNumber
VAR RelativeYearPos = CurrentYearPos - FiscalYearNumber
VAR FiscalStartOfMonth =
DATE ( CalendarYearNumber, CalendarMonthNumber, 1 )
VAR FiscalEndOfMonth =
EOMONTH ( FiscalDate, 0 )
VAR FiscalMonthDays =
INT ( FiscalEndOfMonth - FiscalStartOfMonth + 1 )
VAR FiscalDayOfMonthNumber =
INT ( FiscalDate - FiscalStartOfMonth + 1 )
VAR FiscalStartOfQuarter =
MINX (
FILTER (
FiscalStandardGregorianBase,
[Fiscal YearQuarterNumber] = YearQuarterNumber
),
[Date]
)
VAR FiscalEndOfQuarter =
MAXX (
FILTER (
FiscalStandardGregorianBase,
[Fiscal YearQuarterNumber] = YearQuarterNumber
),
[Date]
)
VAR FiscalQuarterDays =
INT ( FiscalEndOfQuarter - FiscalStartOfQuarter + 1 )
VAR FiscalFirstDayOfYear =
DATE ( FiscalYearNumber - 1, FiscalCalendarFirstMonth, 1 )
VAR FiscalLastDayOfYear =
DATE ( FiscalYearNumber, FiscalCalendarFirstMonth, 1 ) - 1
VAR FiscalYearDays =
INT ( FiscalLastDayOfYear - FiscalFirstDayOfYear + 1 )
VAR FiscalDayOfQuarterNumber =
INT ( FiscalDate - FiscalStartOfQuarter + 1 )
VAR FiscalStartOfYear =
MINX (
FILTER (
FiscalStandardGregorianBase,
[Fiscal YearNumber] = FiscalYearNumber
),
[Date]
)
VAR FiscalEndOfYear =
MAXX (
FILTER (
FiscalStandardGregorianBase,
[Fiscal YearNumber] = FiscalYearNumber
),
[Date]
)
VAR FiscalDatePreviousWeek = FiscalDate - 7
VAR FiscalDatePreviousMonth =
MAXX (
FILTER (
FiscalStandardGregorianBase,
[Fiscal YearMonthNumber] = YearMonthNumber - 1
&&
( [Day of Month] <= FiscalDayOfMonthNumber
|| FiscalDayOfMonthNumber = FiscalMonthDays )
),
[Date]
)
VAR FiscalDatePreviousQuarter =
MAXX (
FILTER (
FiscalStandardGregorianBase,
[Fiscal YearMonthNumber] = YearMonthNumber - 3
&&
( [Day of Month] <= FiscalDayOfMonthNumber
|| FiscalDayOfMonthNumber = FiscalMonthDays )
),
[Date]
)
VAR FiscalDatePreviousYear =
MAXX (
FILTER (
FiscalStandardGregorianBase,
[Fiscal YearMonthNumber] = YearMonthNumber - 12
&&
( [Day of Month] <= FiscalDayOfMonthNumber
|| FiscalDayOfMonthNumber = FiscalMonthDays )
),
[Date]
)
RETURN
ROW ( "Fiscal RelativeWeekPos", RelativeWeekPos,
"Fiscal RelativeMonthPos", RelativeMonthPos,
"Fiscal RelativeQuarterPos", RelativeQuarterPos,
"Fiscal RelativeYearPos", RelativeYearPos,
"Fiscal StartOfMonth", FiscalStartOfMonth,
"Fiscal EndOfMonth", FiscalEndOfMonth,
"Fiscal DayOfMonthNumber", FiscalDayOfMonthNumber,
"Fiscal StartOfQuarter", FiscalStartOfQuarter,
"Fiscal EndOfQuarter", FiscalEndOfQuarter,
"Fiscal DayOfQuarterNumber", FiscalDayOfQuarterNumber,
"Fiscal StartOfYear", FiscalStartOfYear,
"Fiscal EndOfYear", FiscalEndOfYear,
"Fiscal DatePreviousWeek", FiscalDatePreviousWeek,
"Fiscal DatePreviousMonth", FiscalDatePreviousMonth,
"Fiscal DatePreviousQuarter", FiscalDatePreviousQuarter,
"Fiscal DatePreviousYear", FiscalDatePreviousYear,
"Fiscal MonthDays", FiscalMonthDays,
"Fiscal QuarterDays", FiscalQuarterDays,
"Fiscal YearDays", FiscalYearDays
)
)
VAR FiscalWeeksBase =
GENERATE (
NATURALLEFTOUTERJOIN ( CustomFiscalRawDays, HolidayDates ),
VAR CalDate = [Date]
VAR FwFirstDayOfYear = [FW StartOfYear]
VAR FwDayOfYear =
INT ( CalDate - FwFirstDayOfYear + 1 )
VAR CalYear =
YEAR ( [Date] )
VAR CalMonthNumber =
MONTH ( [Date] )
VAR CalDay =
DAY ( [Date] )
VAR FwDayOfYearNumber = CalDate - [FW StartOfYear]
+ 1
VAR FwWeekNumber =
INT ( CEILING ( FwDayOfYearNumber / 7, 1 ) )
VAR FwYearNumber = [FW YearNumber]
VAR FwQuarterNumber =
IF ( FwWeekNumber > 52, 4, ROUNDUP ( FwWeekNumber / 13, 0 ) )
VAR FwWeekInQuarterNumber =
IF ( FwWeekNumber > 52, 14, FwWeekNumber - 13 * ( FwQuarterNumber - 1 ) )
VAR FwMonthNumber =
( FwQuarterNumber - 1 )
* 3
+ SWITCH (
TRUE,
FwWeekInQuarterNumber <= WeeksInP1, 1,
FwWeekInQuarterNumber
<= ( WeeksInP1 + WeeksInP2 ), 2,
3
)
VAR WeekDayNumber =
WEEKDAY ( CalDate, WeekDayCalculationType )
VAR FirstDayOfWeek = [Date] - WeekDayNumber
+ 1
VAR LastDayOfWeek = FirstDayOfWeek + 6
VAR IsWorkingDay =
CONTAINS ( WorkingDays, [WorkingDayNumber], WEEKDAY ( CalDate, 1 ) - 1 )
&& ISBLANK ( [Holiday Name] )
RETURN
ROW (
"DateKey", CalYear * 10000
+ CalMonthNumber * 100
+ CalDay,
// "FW YearNumber", FwYearNumber, -- It is already in the first set of columns of the GENERATE function
"FW Year", FiscalWeeklyPrefixSpace & FwYearNumber,
"FW QuarterNumber", FwQuarterNumber,
"FW Quarter", FiscalWeeklyPrefix & "Q"
& FwQuarterNumber,
"FW YearQuarterNumber", FwYearNumber * 4
- 1
+ FwQuarterNumber,
"FW Quarter Year", FiscalWeeklyPrefix & "Q"
& FwQuarterNumber
& " "
& FwYearNumber,
"FW MonthNumber", FwMonthNumber,
"FW Month", FiscalWeeklyPrefix & "P"
& FORMAT ( FwMonthNumber, "00" ),
"FW YearMonthNumber", FwYearNumber * 12
- 1
+ FwMonthNumber,
"FW Month Year", FiscalWeeklyPrefix & "P"
& FORMAT ( FwMonthNumber, "00" )
& " "
& FwYearNumber,
"FW WeekNumber", FwWeekNumber,
"FW Week", FiscalWeeklyPrefix & "W"
& FORMAT ( FwWeekNumber, "00" ),
"FW YearWeekNumber", INT ( DIVIDE ( CalDate - FirstWeekReference, 7 ) )
+ 1,
"FW Week Year", FiscalWeeklyPrefix & "W"
& FORMAT ( FwWeekNumber, "00" )
& " "
& FwYearNumber,
"FW StartOfWeek", FirstDayOfWeek,
"FW EndOfWeek", LastDayOfWeek,
"WeekDayNumber", WeekDayNumber,
"Week Day", FORMAT ( CalDate, "ddd" ),
"FW DayOfYearNumber", FwDayOfYear,
"IsWorkingDay", IsWorkingDay,
"Day Type", IF ( IsWorkingDay, WorkingDayType, NonWorkingDayType )
)
)
VAR FiscalWeeks_Pre =
GENERATE (
FiscalWeeksBase,
VAR CalDate = [Date]
VAR FWYearNumber = [FW YearNumber]
VAR FwYearWeekNumber = [FW YearWeekNumber]
VAR FwYearMonthNumber = [FW YearMonthNumber]
VAR FwYearQuarterNumber = [FW YearQuarterNumber]
VAR CurrentWeekPos =
AVERAGEX (
FILTER ( FiscalWeeksBase, [Date] = TodayReference ),
[FW YearWeekNumber]
)
VAR CurrentMonthPos =
AVERAGEX (
FILTER ( FiscalWeeksBase, [Date] = TodayReference ),
[FW YearMonthNumber]
)
VAR CurrentQuarterPos =
AVERAGEX (
FILTER ( FiscalWeeksBase, [Date] = TodayReference ),
[FW YearQuarterNumber]
)
VAR CurrentYearPos =
AVERAGEX (
FILTER ( FiscalWeeksBase, [Date] = TodayReference ),
[FW YearNumber]
)
VAR RelativeWeekPos = CurrentWeekPos - FwYearWeekNumber
VAR RelativeMonthPos = CurrentMonthPos - FwYearMonthNumber
VAR RelativeQuarterPos = CurrentQuarterPos - FwYearQuarterNumber
VAR RelativeYearPos = CurrentYearPos - FwYearNumber
VAR FwStartOfMonth =
MINX (
FILTER ( FiscalWeeksBase, [FW YearMonthNumber] = FwYearMonthNumber ),
[Date]
)
VAR FwEndOfMonth =
MAXX (
FILTER ( FiscalWeeksBase, [FW YearMonthNumber] = FwYearMonthNumber ),
[Date]
)
VAR FwMonthDays =
INT ( FwEndOfMonth - FwStartOfMonth + 1 )
VAR FwDayOfMonthNumber =
INT ( CalDate - FwStartOfMonth + 1 )
VAR FwStartOfQuarter =
MINX (
FILTER ( FiscalWeeksBase, [FW YearQuarterNumber] = FwYearQuarterNumber ),
[Date]
)
VAR FwEndOfQuarter =
MAXX (
FILTER ( FiscalWeeksBase, [FW YearQuarterNumber] = FwYearQuarterNumber ),
[Date]
)
VAR FwQuarterDays =
INT ( FwEndOfQuarter - FwStartOfQuarter + 1 )
VAR FwDayOfQuarterNumber =
INT ( CalDate - FwStartOfQuarter + 1 )
VAR FwStartOfYear =
MINX (
FILTER ( FiscalWeeksBase, [FW YearNumber] = FwYearNumber ),
[Date]
)
VAR FwEndOfYear =
MAXX (
FILTER ( FiscalWeeksBase, [FW YearNumber] = FwYearNumber ),
[Date]
)
VAR FwYearDays =
INT ( FwEndOfYear - FwStartOfYear + 1 )
RETURN
ROW ( "FW RelativeWeekPos", RelativeWeekPos,
"FW RelativeMonthPos", RelativeMonthPos,
"FW RelativeQuarterPos", RelativeQuarterPos,
"FW RelativeYearPos", RelativeYearPos,
"FW StartOfMonth", FwStartOfMonth,
"FW EndOfMonth", FwEndOfMonth,
"FW DayOfMonthNumber", FwDayOfMonthNumber,
"FW StartOfQuarter", FwStartOfQuarter,
"FW EndOfQuarter", FwEndOfQuarter,
"FW DayOfQuarterNumber", FwDayOfQuarterNumber,
"FW MonthDays", FwMonthDays,
"FW QuarterDays", FwQuarterDays,
"FW YearDays", FwYearDays
)
)
VAR FiscalWeeks =
GENERATE (
FiscalWeeks_Pre,
VAR CalDate = [Date]
VAR FwYearMonthNumber = [FW YearMonthNumber]
VAR FwYearQuarterNumber = [FW YearQuarterNumber]
VAR FWYearNumber = [FW YearNumber]
VAR FwDayOfMonthNumber = [FW DayOfMonthNumber]
VAR FwDayOfQuarterNumber = [FW DayOfQuarterNumber]
VAR FwDayOfYearNumber = [FW DayOfYearNumber]
VAR FwMonthDays = [FW EndOfMonth] - [FW StartOfMonth] + 1
VAR FwQuarterDays = [FW EndOfQuarter] - [FW StartOfQuarter] + 1
VAR FwYearDays = [FW EndOfYear] - [FW StartOfYear] + 1
VAR FwDatePreviousWeek = CalDate - 7
VAR FwDatePreviousMonth =
MAXX (
FILTER (
FiscalWeeks_Pre,
[Fw YearMonthNumber] = FwYearMonthNumber - 1
&&
( [FW DayOfMonthNumber] <= FwDayOfMonthNumber
|| FwDayOfMonthNumber = FwMonthDays )
),
[Date]
)
VAR FwDatePreviousQuarter =
MAXX (
FILTER (
FiscalWeeks_Pre,
[Fw YearQuarterNumber] = FwYearQuarterNumber - 1
&&
( [FW DayOfQuarterNumber] <= FwDayOfQuarterNumber
|| FwDayOfQuarterNumber = FwQuarterDays )
),
[Date]
)
VAR FwDatePreviousYear =
MAXX (
FILTER (
FiscalWeeks_Pre,
[Fw YearNumber] = FWYearNumber - 1
&&
( [FW DayOfYearNumber] <= FwDayOfYearNumber
|| FwDayOfYearNumber = FwYearDays )
),
[Date]
)
RETURN
ROW (
"FW DatePreviousWeek", FwDatePreviousWeek,
"FW DatePreviousMonth", FwDatePreviousMonth,
"FW DatePreviousQuarter", FwDatePreviousQuarter,
"FW DatePreviousYear", FwDatePreviousYear
)
)
VAR CompleteCalendarExpanded =
NATURALINNERJOIN (
FiscalStandardGregorian,
NATURALINNERJOIN ( CalendarStandardGregorian, FiscalWeeks )
)
VAR CompleteCalendar =
FILTER (
CompleteCalendarExpanded,
( [Calendar YearNumber] >= FirstYear && [Calendar YearNumber] <= LastYear && CalendarRange = "Calendar" )
||
( [Fiscal YearNumber] >= FirstYear && [Fiscal YearNumber] <= LastYear && CalendarRange = "FiscalGregorian" )
||
( [FW YearNumber] >= FirstYear && [FW YearNumber] <= LastYear && CalendarRange = "FiscalWeekly" )
)