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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Sort by week number in order according to year

Hello,

I have the following table and i am trying to show the number of names by ISO week number whilst taking into account the year, so the week number should be displayed in chronological order. 

Here is the problem, with all week numbers sorted in ascending order and all weekly data grouped:

MakeItReal_0-1650587545704.png

I have a calendar table with the Date field connected to the Results table (Until field) but i am not sure what changes are needed. Any help is much appreciated!

 

NameFromUntil
Kacy Jones23/01/202026/01/2020
Kade Kennedy29/01/202001/02/2020
Cristian Thompson24/02/202027/02/2020
Kade Kennedy03/02/202006/02/2020
Damian Mclellan21/02/202024/02/2020
Damian Mclellan24/02/202027/02/2020
Cristian Thompson29/02/202003/03/2020
Ishan Rigby21/02/202024/02/2020
Jimmie Harding23/02/202026/02/2020
Brax Oneill01/03/202004/03/2020
Kacy Jones01/03/202004/03/2020
Kacy Jones18/11/202023/11/2020
Kade Kennedy24/11/202029/11/2020
Cristian Thompson20/12/202025/12/2020
Kade Kennedy29/11/202004/12/2020
Damian Mclellan17/12/202022/12/2020
Damian Mclellan20/12/202025/12/2020
Cristian Thompson25/12/202030/12/2020
Ishan Rigby17/12/202022/12/2020
Jimmie Harding19/12/202024/12/2020
Brax Oneill26/12/202031/12/2020
Kacy Jones26/12/202031/12/2020
Kacy Jones18/11/202108/12/2021
Kade Kennedy24/11/202116/12/2021
Cristian Thompson20/12/202112/12/2021
Kade Kennedy29/11/202109/12/2021
Damian Mclellan17/12/202129/12/2021
Damian Mclellan20/12/202126/12/2021
Cristian Thompson25/12/202129/12/2021
Ishan Rigby17/12/202129/12/2021
Jimmie Harding19/12/202125/12/2021
Brax Oneill26/12/202131/12/2021
Kacy Jones26/12/202131/12/2021
Bear Barry27/12/202102/01/2022
Jorgie Galloway03/01/202209/01/2022
Farah Lyon07/01/202226/01/2022
Salman Goodman20/12/202110/01/2022
Vincenzo Murray22/12/202111/01/2022
Jorgie Galloway19/12/202109/01/2022
Kade Kennedy06/01/202227/01/2022
Kacy Jones10/01/202216/01/2022
Kacy Jones16/01/202222/01/2022
Kade Kennedy27/01/202216/02/2022
Cristian Thompson23/01/202230/01/2022
Kade Kennedy28/01/202217/02/2022
Damian Mclellan04/02/202216/02/2022
Damian Mclellan31/01/202206/02/2022
Cristian Thompson26/01/202216/02/2022
Ishan Rigby31/01/202221/02/2022
Jimmie Harding31/01/202216/02/2022
Brax Oneill27/01/202215/02/2022
Kacy Jones30/11/202130/11/2021
Bear Barry30/11/202130/11/2021
Jorgie Galloway09/02/202220/02/2022
Farah Lyon16/02/202209/03/2022
Salman Goodman10/02/202227/02/2022
Vincenzo Murray19/02/202210/03/2022
Jorgie Galloway20/02/202225/02/2022
Kade Kennedy21/02/202227/02/2022
Kacy Jones21/02/202214/03/2022
Kacy Jones21/02/202213/03/2022
Kade Kennedy14/02/202220/02/2022
Cristian Thompson04/03/202222/03/2022
Kade Kennedy28/02/202206/03/2022
Damian Mclellan06/03/202212/03/2022
Damian Mclellan09/03/202230/03/2022
Cristian Thompson06/03/202213/03/2022
Ishan Rigby09/03/202230/03/2022
Jimmie Harding14/03/202230/03/2022
Brax Oneill11/03/202216/03/2022
Kacy Jones18/03/202218/03/2022
Bear Barry14/03/202220/03/2022
Jorgie Galloway14/03/202204/04/2022
Farah Lyon09/03/202230/03/2022
Salman Goodman30/11/202130/11/2021
Vincenzo Murray28/03/202208/04/2022
Jorgie Galloway23/03/202230/03/2022
Kade Kennedy17/03/202227/03/2022
Kacy Jones21/03/202225/03/2022
Kacy Jones01/04/202221/04/2022
Kade Kennedy28/03/202215/04/2022
Cristian Thompson30/03/202220/04/2022
Kade Kennedy12/04/202203/05/2022
Damian Mclellan10/04/202216/04/2022
Damian Mclellan18/04/202227/04/2022
Cristian Thompson01/01/202201/01/2022

 

Here is the download link to my PBIX file (click the download icon at the top-right corner of the window): https://drive.google.com/file/d/1AJi_tjNBpJ_16Zyi7JpdvPIUpT6WuqZz/view?usp=sharing

1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

Yes. Please create the new columns:

 

ISOYear = YEAR ( 'Calendar'[Date] + 26 - 'Calendar'[ISOWeekNumber] )
ISOYearWeekNum = [ISOYear] & FORMAT ( [ISOWeekNumber], "00" )
ISO_W_Y = "W" & [ISOWeekNumber] & " Y" & [ISOYear]

 

Then sort the [ISO_W_Y] column by [ISOYearWeekNum] field.

 

vkkfmsft_1-1651645026752.png

vkkfmsft_0-1651644997612.png

vkkfmsft_2-1651645066007.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

7 REPLIES 7
danextian
Super User
Super User

Hi @Anonymous ,

I am quite confused with what you want to achieve.
Do you want to include year in your sorting such that the year first then followed the week number? Or is it the other way around? In your column chart, you can actually add Year to the x-axis followed by ISOWeekNumber.  In the formatting Pane within X-axis tile, you can turn off Concatenate labels and then sort you data ascending or descending based on YearISOWeekNumber. Alternatively you can create a calculated column to combine both fields and use this instead in your chart.

Year and ISO Week =
'Calendar'[Year] & FORMAT ( 'Calendar'[ISOWeekNumber], " 00" )









Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
Anonymous
Not applicable

Hi @danextian Thanks for your reply! To clarify, i am trying to show the data in a stacked column chart with the x-axis showing time in chronological order using the week number.

Based on your suggestion, i have created a calculated column and the data displays correctly, except for the blank column - i'm not sure why it is there?

Secondly, is it possible to show only the week number without the year?

MakeItReal_0-1650601536292.png

I would like to have the label for each week start with "Week #" and followed by the week number. Is that possible?

Anonymous
Not applicable

Here is an update on what i have tried to fix this issue.

I have created a calculated column:

 

WeekNumberText = "Week #" & 'Calendar'[Week Number]

 

In Data view in Power BI, i selected the WeekNumberText column and chose to sort by the following column:

 

ISOYearWeekNum = 'Calendar'[CalcISOYear]*100+'Calendar'[Week Number]

 

But i get the following error. Can someone please help?

MakeItReal_1-1650801781880.png

 

Hi @Anonymous ,

 

If you want to sort one column by another column, you need to make sure that the values of these two columns are 1 : 1. And the calculated column you created is * : 1 between columns [WeekNumberText] and [ISOYearWeekNum], so you receive the following error.

vkkfmsft_0-1651022193213.png

So you had to add the year to the X axis, as @danextian said.


As for the blank column, this is because the year 2020 is included in your Results table, while the Calendar table does not.

 

vkkfmsft_1-1651022301085.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Anonymous
Not applicable

@v-kkf-msft Instead of, for example, "2022 12" label on the x-axis, is it possible to show in this format "W12 Y2022"?

Hi @Anonymous ,

 

Yes. Please create the new columns:

 

ISOYear = YEAR ( 'Calendar'[Date] + 26 - 'Calendar'[ISOWeekNumber] )
ISOYearWeekNum = [ISOYear] & FORMAT ( [ISOWeekNumber], "00" )
ISO_W_Y = "W" & [ISOWeekNumber] & " Y" & [ISOYear]

 

Then sort the [ISO_W_Y] column by [ISOYearWeekNum] field.

 

vkkfmsft_1-1651645026752.png

vkkfmsft_0-1651644997612.png

vkkfmsft_2-1651645066007.png

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

This method helped me in project but curious on why do we + 26 and - week of the year? How you got this logic. Couldn't sleep thinking about this.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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