Forum Discussion
Show data by week number
The calendar is separate and more useful that way. In fact, having your week numbers in your fact table may be throwing off the calculation and sort. While I don't have time to fully explain this, at this time, I wanted to at least respond and share a link that could help.
https://www.sqlbi.com/articles/week-based-time-intelligence-in-dax/
Is your WeekNum column set to numeric?
Greg_Deckler Yes, it's numeric. I try with the character it's the same. And i have another question, if the weeks are in right order, it will be like week 1 to week 53, but in fact my data start at week 28 of 2015, so is that possibe in my line chart, the x axe will show in date order? Like week 28 of 2015 to week 53 of 2015 and then weeks of 2016
Are you creating the Weeknumber field in your date calendar or in your fact table? Add it to your date table.
As for why Jan starts at week 2, look into ISO weeks versus the different "standards" of labeling weeks. You may need to quantify a 'series' number to determine which week number is assigned due to which day of the week the year starts on or how many days are required to call it a week.
I use a calendar table with both week numbers and ISO week numbers included for that reason. Here is a snip of my calendar. Notice how the ISO week number is different? I added many years to show that, I don't usually use that large of a date table.
DateKey DateInt YearKey HalfYearKey QuarterKey MonthKey MonthOfYear QuarterOfYear DayOfYear DayOfMonth DayOfWeekMon DayOfWeekSun Month Year WeekNumber WeekOfYearISO 12/28/1948 19481228 1948 19482 19484 194812 12 4 363 28 2 3 12 1948 53 53 12/30/2082 20821230 2082 20822 20824 208212 12 4 364 30 3 4 12 2082 53 53 12/28/2065 20651228 2065 20652 20654 206512 12 4 362 28 1 2 12 2065 53 53 12/30/1998 19981230 1998 19982 19984 199812 12 4 364 30 3 4 12 1998 53 53 12/28/1981 19811228 1981 19812 19814 198112 12 4 362 28 1 2 12 1981 53 53 1/2/2055 20550102 2055 20551 20551 20551 1 1 2 2 6 7 1 2055 1 53 1/3/2049 20490103 2049 20491 20491 20491 1 1 3 3 7 1 1 2049 1 53 12/30/1914 19141230 1914 19142 19144 191412 12 4 364 30 3 4 12 1914 53 53 1/2/1971 19710102 1971 19711 19711 19711 1 1 2 2 6 7 1 1971 1 53 12/27/2088 20881227 2088 20882 20884 208812 12 4 362 27 1 2 12 2088 53 53 1/3/1965 19650103 1965 19651 19651 19651 1 1 3 3 7 1 1 1965 1 53 12/27/2004 20041227 2004 20042 20044 200412 12 4 362 27 1 2 12 2004 53 53 12/31/2048 20481231 2048 20482 20484 204812 12 4 366 31 4 5 12 2048 53 53 12/27/1920 19201227 1920 19202 19204 192012 12 4 362 27 1 2 12 1920 53 53 12/31/1964 19641231 1964 19642 19644 196412 12 4 366 31 4 5 12 1964 53 53 12/31/2071 20711231 2071 20712 20714 207112 12 4 365 31 4 5 12 2071 53 53 12/29/2054 20541229 2054 20542 20544 205412 12 4 363 29 2 3 12 2054 53 53 12/31/1987 19871231 1987 19872 19874 198712 12 4 365 31 4 5 12 1987 53 53 12/29/1970 19701229 1970 19702 19704 197012 12 4 363 29 2 3 12 1970 53 53 1/3/2044 20440103 2044 20441 20441 20441 1 1 3 3 7 1 1 2044 1 53 1/1/2027 20270101 2027 20271 20271 20271 1 1 1 1 5 6 1 2027 1 53 1/2/2021 20210102 2021 20211 20211 20211 1 1 2 2 6 7 1 2021 1 53 12/31/1903 19031231 1903 19032 19034 190312 12 4 365 31 4 5 12 1903 53 53 1/3/1960 19600103 1960 19601 19601 19601 1 1 3 3 7 1 1 1960 1 53 1/1/1943 19430101 1943 19431 19431 19431 1 1 1 1 5 6 1 1943 1 53 Hi kcantor
Thanks for the response.
Sorry i don't really understand the date table things. I just have one table in my dataset, and the date is one column in format dd/mm/yyyy, then I creat a column of week number. So the date table is a new table i have to creat or it's in somewhere i just need to correct?