Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
apollnor
Advocate II
Advocate II

Week 52 at start and at end of year

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?

 

 

10 REPLIES 10
Clara684
New Member

Did someone manage to solve this?

v-jiascu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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. 

example2.png

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.

Week_52_at_start_and_at_end_of_year

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.

Week_52_at_start_and_at_end_of_year2

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

Week_52_at_start_and_at_end_of_year3

 

Best Regards,

Dale

 

 

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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.

Week_52_at_start_and_at_end_of_year

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

So which week would you say 01.01.2017 belongs to?

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.

Week_52_at_start_and_at_end_of_year4

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

@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)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors