Forum Discussion
Helper IShow 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/
Helper IGreg_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
Community ChampionAre 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 |
- kcantor
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/
- YuanG
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?