cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

## 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
New Member

Did someone manage to solve this?

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.

Employee

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

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.
Employee

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

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?

Employee

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

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.

Community Champion

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

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors