Week 52 at start and at end of year

[apollnor]

Is there a clever way to solve the issue of week 52/53 potentially being in January and December the same year, in a column chart?

In the visual, this would put week 52 at the end of the bar chart, because 52 is higher than 1. But week 52 is actually both at the start and end of the year.

I cannot use a ranked text-column to show weeks because I need to use the continuous type in the visual.

Maybe there could be implemented an option in the column chart visual where one can switch on logic that solves this front end?

[Clara684]

Did someone manage to solve this?

[v-jiascu-msft]

Hi @apollnor,

The 52/53 week could be one part in this year and another part in the NEXT year. Why do you want to put it in the start of a bar chart?

Could you please post the expected result?

Best Regards,

Dale

[apollnor]

Of course, that is a different "problem", as could week 5 be part of january and febuary. The business needs to decide how to use date hierarchies based on what results they expect to see in a columns chart based on this.

The thing is that since week 52 or 53 can be (like in ISO 2017) be both in January and December, a column chart showing a whole years values divided on weeks store as datatype INT will show all of week 52 after week 51. 

That is incorrect if you wanted the correct history for the year over weeks in such a representation.

Check out my amazing paint skills below for desired result and undesirable result. 

[v-jiascu-msft]

Hi @apollnor,

The week 52 and 53 could be both in January and December. BUT it's January of this year and December of the next year. For example, the week 53 of 2017 is from 2017-12-31 to 2018-01-06.

If you want to put week 53 in the head, you can do it following steps below.

1. Change the type of week number to TEXT. (Number type will sort by itself.)

2. In the Query Editor, add a custom column like this.

3. Sort the column week number by the custom column.

Best Regards,

Dale

[apollnor]

Look at ISO weeks, like we use in Norway and you might understand. http://www.norskkalender.no/default.asp?year=2017

Like i said in my original post...Cant use text datatype. See that ugly horizontal scroll bar you get? Nobody wants that, and you can avoid it using a continous x axis, but that requires either a number date type or a date datetype in the column.

[v-jiascu-msft]

Hi @apollnor,

But the week 52 belongs to the year before 2017, which is year 2016, right? It shouldn't be considered in the year 2017.

Best Regards,

Dale

[apollnor]

So which week would you say 01.01.2017 belongs to?

[v-jiascu-msft]

Hi @apollnor,

I think it belongs to week 52. One week could be shared by two years. I think it's better not to consider the year. Week 1 of Year 2018 starts from Jan 1. So week 52 or 53 shouldn't be in the head.

Or we can use year-week instead. For example, 2017-01, 2017-02, ... ... 2017-52.

Another solution could be adjusting all the week to start from Jan 1.

Which do you prefer?

Best Regards,

Dale

[apollnor]

I prefer what is real. That's my OCD. 

In a time dimension table the fact about the unique occurrence of 01.01.17 is as follows:

• Week 52
• January
• First Quarter
• 2017

Change that and you change facts, and that could have unknown implications down the road, and/or you would need to train the people using the data into knowing that you've put dates that belong to week 52/53 that occur in January into for example week 1 where it really doesn't belong, because you failed to find another way of solving it and making it look pretty. 

I've already tried the approach where you introduce the month or year into the week field, but then you get the problem with not being able to use continuous visuals again, because the field can no longer be an integer, and you're back to square one. 

This is why I'm asking for more intelligent support on week numbers in Power BI. All the data needed to present the date chronologically correct within the year, month and week it belongs to is in the date datatype.

[PaulDBrown]

@apollnor;
This is just an idea, but I think it should work. If you create a custom column with YearWeek (201752, 201801, 201802....201852) and set it as a whole number, you will be able to use this column to sort the axis in the visual. (Or just sort the table by this column).

does it make sense?

Edit: I see the “conflict” is between 2016 and 2017, but it should still work (201652, 201701, 201702...201752, 201801, 201802...201852 and so on - since the YearWeek will always be "consecutive" integers)