cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MIchri
Resolver I
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
TomMartens
Super User II
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

View solution in original post

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.

 

Mariusz
Super User II
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.

 

TomMartens
Super User II
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

View solution in original post

@TomMartens Thanks! it worked

supergimi
Resolver III
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

Hi @supergimi 

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

/Mikkel 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors