Forum Discussion
Sort order of calendar data table - does it matter?
I have a table of every date between 1/4/2003 and 31/3/2035 with numerous other columns for month, month number, year number, period etc etc.
I can sort this by date in the query but when I try and sort in the data modeling bit, it doesn't work. Seems it wants to sort by month or something? In any case, the first row is 07/12/2003.
I'm not sure this even matters as when I create a calculated column with this function:
COUNTROWS(FILTER(all(Dates),Dates[Date]<=EARLIER(Dates[Date])))
it returns values consistent with a table sorted in date order. It just seems odd I can't view it this way. I've read various articles on time intelligence, calendar tables etc but none seem to contain the answer.
Help would be appreciated. It's possible I'm not setting the table up correctly. Thanks.
Anonymous,
You can create calendar table using DAX below. Then change data type of DateAsInteger column to whole number.Dates = ADDCOLUMNS ( CALENDAR (DATE(2003,3,1), DATE(2035,3,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )
In Data view, you can sort the Date column by DateAsInteger column, and you can display the date column ascending. For more details, please check attached PBIX file.
Regards,
Lydia
1 Reply
Anonymous,
You can create calendar table using DAX below. Then change data type of DateAsInteger column to whole number.Dates = ADDCOLUMNS ( CALENDAR (DATE(2003,3,1), DATE(2035,3,31)), "DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ), "Year", YEAR ( [Date] ), "Monthnumber", FORMAT ( [Date], "MM" ), "YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ), "YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ), "MonthNameShort", FORMAT ( [Date], "mmm" ), "MonthNameLong", FORMAT ( [Date], "mmmm" ), "DayOfWeekNumber", WEEKDAY ( [Date] ), "DayOfWeek", FORMAT ( [Date], "dddd" ), "DayOfWeekShort", FORMAT ( [Date], "ddd" ), "Quarter", "Q" & FORMAT ( [Date], "Q" ), "YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" ) )
In Data view, you can sort the Date column by DateAsInteger column, and you can display the date column ascending. For more details, please check attached PBIX file.
Regards,
Lydia