Forum Discussion
Show data by week number
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!
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/
18 Replies
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.
- 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 ?
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?
YuanG - Select your Week column to have a "Sort By" of your WeekNum column.
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..!!
Have you been able to resolve this? I am having the same issue sorting weeknumbers across multiple years.
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.
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?
