cancel
Showing results for
Did you mean:
Resolver I

## Week number by year

Hi!

I have a chart showing trend by week number. However when 2020 began my week 1 is placed at the start of my chart and not as sequal to week 52/53.

I've tried making a "week + year" column but as it is text I cannot arrange it in correct order.

I need;

week 51 (2019)    week 52 (2019)    week 1(2020)   week 2 (2020) etc..

Thanks!

Mikkel

1 ACCEPTED SOLUTION
Super User II

Hey @MIchri

Just create an additional calculated column that contains an integer value like so

yearweekno = year('<tablename>'[date]) * 100 + weeknum('<tablename>'[date])

Then you can use this column to sort your column that contains these values

week 51 (2019)    week 52 (2019)    week 1(2020)   week 2 (2020)

This explains the feature "Sort by column": https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column#sort-using-the-sort-by-column-butto...

The same article also explains how the sort order can be adjusted in visuals.

Hopefully this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
6 REPLIES 6
Anonymous
Not applicable

Hi @MIchri the DAX syntax you need to use is weeknum.

I personally use datediff and specify the split by days, but from what you are saying you want weeknum.

Let me know if that helps, or please explain again.

Super User II

Hi @MIchri

You can add sort by column.

https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Super User II

Hey @MIchri

Just create an additional calculated column that contains an integer value like so

yearweekno = year('<tablename>'[date]) * 100 + weeknum('<tablename>'[date])

Then you can use this column to sort your column that contains these values

week 51 (2019)    week 52 (2019)    week 1(2020)   week 2 (2020)

This explains the feature "Sort by column": https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column#sort-using-the-sort-by-column-butto...

The same article also explains how the sort order can be adjusted in visuals.

Hopefully this provides what you are looking for.

Regards,

Tom

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Resolver I

@TomMartens Thanks! it worked

Resolver III

Hi,

you have to build a yearweeknumber column with data as 201951, 201952, 201953, 202001, 202002... and then you have to order your week year (i.e. week 52 (2019)...) by column yearweeknumber in the model tab

Lorenzo

Resolver I

Hi @supergimi

Thanks, it almost worked. But my yearweeknumber makes it 201952 and 20201 (not 202001) and therefore rates 201952 a higher value.

/Mikkel

Announcements