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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hey @Anonymous 

 

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-button

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 @Anonymous 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
Community Champion
Community Champion

Hi @Anonymous 

 

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

Hey @Anonymous 

 

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-button

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
Anonymous
Not applicable

@TomMartens Thanks! it worked

Anonymous
Not applicable

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

Anonymous
Not applicable

Hi @Anonymous 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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