Sort by week number in order according to year

[Anonymous]

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:

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!

Name	From	Until
Kacy Jones	23/01/2020	26/01/2020
Kade Kennedy	29/01/2020	01/02/2020
Cristian Thompson	24/02/2020	27/02/2020
Kade Kennedy	03/02/2020	06/02/2020
Damian Mclellan	21/02/2020	24/02/2020
Damian Mclellan	24/02/2020	27/02/2020
Cristian Thompson	29/02/2020	03/03/2020
Ishan Rigby	21/02/2020	24/02/2020
Jimmie Harding	23/02/2020	26/02/2020
Brax Oneill	01/03/2020	04/03/2020
Kacy Jones	01/03/2020	04/03/2020
Kacy Jones	18/11/2020	23/11/2020
Kade Kennedy	24/11/2020	29/11/2020
Cristian Thompson	20/12/2020	25/12/2020
Kade Kennedy	29/11/2020	04/12/2020
Damian Mclellan	17/12/2020	22/12/2020
Damian Mclellan	20/12/2020	25/12/2020
Cristian Thompson	25/12/2020	30/12/2020
Ishan Rigby	17/12/2020	22/12/2020
Jimmie Harding	19/12/2020	24/12/2020
Brax Oneill	26/12/2020	31/12/2020
Kacy Jones	26/12/2020	31/12/2020
Kacy Jones	18/11/2021	08/12/2021
Kade Kennedy	24/11/2021	16/12/2021
Cristian Thompson	20/12/2021	12/12/2021
Kade Kennedy	29/11/2021	09/12/2021
Damian Mclellan	17/12/2021	29/12/2021
Damian Mclellan	20/12/2021	26/12/2021
Cristian Thompson	25/12/2021	29/12/2021
Ishan Rigby	17/12/2021	29/12/2021
Jimmie Harding	19/12/2021	25/12/2021
Brax Oneill	26/12/2021	31/12/2021
Kacy Jones	26/12/2021	31/12/2021
Bear Barry	27/12/2021	02/01/2022
Jorgie Galloway	03/01/2022	09/01/2022
Farah Lyon	07/01/2022	26/01/2022
Salman Goodman	20/12/2021	10/01/2022
Vincenzo Murray	22/12/2021	11/01/2022
Jorgie Galloway	19/12/2021	09/01/2022
Kade Kennedy	06/01/2022	27/01/2022
Kacy Jones	10/01/2022	16/01/2022
Kacy Jones	16/01/2022	22/01/2022
Kade Kennedy	27/01/2022	16/02/2022
Cristian Thompson	23/01/2022	30/01/2022
Kade Kennedy	28/01/2022	17/02/2022
Damian Mclellan	04/02/2022	16/02/2022
Damian Mclellan	31/01/2022	06/02/2022
Cristian Thompson	26/01/2022	16/02/2022
Ishan Rigby	31/01/2022	21/02/2022
Jimmie Harding	31/01/2022	16/02/2022
Brax Oneill	27/01/2022	15/02/2022
Kacy Jones	30/11/2021	30/11/2021
Bear Barry	30/11/2021	30/11/2021
Jorgie Galloway	09/02/2022	20/02/2022
Farah Lyon	16/02/2022	09/03/2022
Salman Goodman	10/02/2022	27/02/2022
Vincenzo Murray	19/02/2022	10/03/2022
Jorgie Galloway	20/02/2022	25/02/2022
Kade Kennedy	21/02/2022	27/02/2022
Kacy Jones	21/02/2022	14/03/2022
Kacy Jones	21/02/2022	13/03/2022
Kade Kennedy	14/02/2022	20/02/2022
Cristian Thompson	04/03/2022	22/03/2022
Kade Kennedy	28/02/2022	06/03/2022
Damian Mclellan	06/03/2022	12/03/2022
Damian Mclellan	09/03/2022	30/03/2022
Cristian Thompson	06/03/2022	13/03/2022
Ishan Rigby	09/03/2022	30/03/2022
Jimmie Harding	14/03/2022	30/03/2022
Brax Oneill	11/03/2022	16/03/2022
Kacy Jones	18/03/2022	18/03/2022
Bear Barry	14/03/2022	20/03/2022
Jorgie Galloway	14/03/2022	04/04/2022
Farah Lyon	09/03/2022	30/03/2022
Salman Goodman	30/11/2021	30/11/2021
Vincenzo Murray	28/03/2022	08/04/2022
Jorgie Galloway	23/03/2022	30/03/2022
Kade Kennedy	17/03/2022	27/03/2022
Kacy Jones	21/03/2022	25/03/2022
Kacy Jones	01/04/2022	21/04/2022
Kade Kennedy	28/03/2022	15/04/2022
Cristian Thompson	30/03/2022	20/04/2022
Kade Kennedy	12/04/2022	03/05/2022
Damian Mclellan	10/04/2022	16/04/2022
Damian Mclellan	18/04/2022	27/04/2022
Cristian Thompson	01/01/2022	01/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

[danextian]

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" )

[Anonymous]

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?

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

[Anonymous]

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?

[v-kkf-msft]

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.

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.

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]

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

[v-kkf-msft · 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.

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.

[Prabhakaran2794]

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.