How to show just the week number in my chart when I have data from several years

[_Kristina_]

Hi guys.

I'm trying to make a line chart showing a development in the number of customers in my company. On the x axis I want to show the week number, but I have several years of data. I managed to make a column showing the week numbers like this: 201738, but I don't think this looks very good in my chart. Is it possible to show just the week numbers but still sort after years?

Hope some of you can help,

Thanks!

[_Kristina_ · Accepted solution]

I tried a range of different stuff, and this is how I best solved my problem:

First of all I made a calendar table

Date = CALENDAR (DATE(2015;1;1); DATE(YEAR(LASTDATE(...));Month(LASTDATE(....));DAY(LASTDATE(...))))

I then made a hierachy with week and year. Drilled down my chart to the lowest level. Went to formatting pane to switch off the concatenation and then I had my weeks in the right order with the year showing below.

To show latest 10 weeks I used the date table to make a relative slicer.

[Sanmelmer]

Hi Krstina!! 

m with the same problem!! 😞 

please could you share your solution in power bi to check it? I dont understand very well your solution.

thanks a lot!!!!!!!!!!!

[vcastello]

Great Solution.

Congratulations !!!!

Vicente

[vcastello]

Hi @_Kristina_

You can try @SivaMani approach. The solution is correct.

But you can add the Year in the visual under the WeekNum so there will be only a WeekNum per year

Hope Tant Helps

Vicente

[_Kristina_]

Thank you both @vcastello @SivaMani 🙂

[SivaMani]

@_Kristina_,

Create a calculated column like below,

WeekNo = WEEKNUM(Table[Date],1)

1 -> week begins on Sunday

2 -> week begins on Monday

[_Kristina_]

@SivaMani Thanks, but I already tried this. The problem is that in my chart, all of the week 1 will be in one point on the axis, all of the week 2s will be in another point and so on. I need to sort after both year and week. Don't know if it makes any sense.

Thanks 🙂

[SivaMani]

@_Kristina_,

In that case, 

Better to keep the year with week number