Show data by week number

[YuanG]

Hi Everyone,

I'm working on a line chart with some data calcul by week ,and i want the data not show  in format dd/mm/yyyy, but with the number of weeks in year. Like this chart, in 27 June 2016, i want it could show 'week 27'

I've try with the number of weeks, but it can't figure like a date format. Also i try to show the week number in tooltips, but it can't works with character data,  it's not bed if i could have the week number in tooltips.

Thank you in advance!

[Eric_Zhang]

@YuanG

Add a calculated column

Week = WEEKNUM([Date])

Create a measure and add it to the Tooltips field.

WeekNum = AVERAGE(Table[Week])

Then you will see the week number when hovering on the visual.

[YuanG]

Hi @Eric_Zhang

Thank you, it works!

And i've found a new problem, it's about a mistake of the week number.

Like in the chart below, when i see with all the date, the week of 28 dec 2015 should be the week 53, but here it show 44, and there is no first week of 2016, it start from the second week, normaily the week of 4 jan 2016 should be the the first week of 2016.

And when i show the data by years, it seperate the week 53 of 2015 in week 53 of 2015 and week 1 of 2016, which they should be the week 53 of 2015

Do you know maybe how can i correct this?

[Greg_Deckler]

Not quite clear on the issue but you can create a week number from a date field using a calculated column such as:

WeekNum = WEEKNUM([Date])

Then you can create a column like:

Week = "Week " & [WeekNum]

Set your sort by for this column to the "WeekNum" column and then use the "Week" column in you Axis.

[sxlubinoff]

Hello,

Hope you can help me.

What if I need to sort weeks from different years ?

Example _

2016 (WK) 51- 2016 (WK) 52 - 2017 (WK) 1 - 2017 ( WK) 2 ) ...Sort problems started when I upload data for 2017 (WK )10 

Now sort is showing as below :

201651 -201652 - 201701-201710-20172-20173-20174 ....

Hope you can help me to resolve it ..I tried everything..!!

[cmonnette]

Have you been able to resolve this? I am having the same issue sorting weeknumbers across multiple years.

[cmonnette]

I was able to resolve this by adding the following column to my data table and then sorting in default order.

"WorkWeek",  YEAR([date]) &  format(WEEKNUM ( [Date] ),"00")  

[Anonymous]

can you explain a bit more how you did it?

[cmonnette]

Have you been able to resolve this? I am having the same issue sorting weeknumbers across multiple years.

[sxlubinoff]

I follow what you advised below, however, what if I have data from different years?? Sort I have been trying is not working ..for example : I have 2016- (WK)51 /2016-(WK)52 / 2017-(WK)1 / 2017-(WK)2 ...as soon I uploaded 2017- (WK)10 problems started.. Now the sort is showing : 2016-51 / 2016-52 / 2017-1 /2017-10 / 2017-2.... Would you please advise how to resolve it ?

[YuanG]

Hi @Greg_Deckler

First of all, thanks for the reponse.

With the column Week, when i creat a line chart, it will not in the right ordre, i've try to make the week number in type character, but it not works, so do you know how can i do with this?

[Greg_Deckler]

@YuanG - Select your Week column to have a "Sort By" of your WeekNum column.

[YuanG]

@Greg_Deckler

i've try with the sort by, it's not correct neither, and the order is weird

[Greg_Deckler]

Is your WeekNum column set to numeric?

[YuanG]

@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

[kcantor]

@YuanG

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

[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?

[kcantor · Accepted solution]

@YuanG

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]

Thank you so much!!! @kcantor