Ben
Ben

Reputation: 3

Power BI: Week #'s not sorting properly

I am having trouble with a line graph visual, where the data is organized by week number and by year number. However when I put the information into the visual and try viewing both 2020 & 2021, it rearranges the data in the order of 2021 & 2020. How do i get it to properly see the data in the correct order of week number by year? Picture of problem line graph is here

I tried sorting the week # by an index value, also by year, also by week... with no luck

Upvotes: 0

Views: 4921

Answers (2)

user7964529
user7964529

Reputation: 23

This actually does not give the correct sequence, when you are dealing with single digit week numbers. For example when dealing with the first ten weeks of 2020, the sequence would be 20201, 202010,20202, 20203... which is obviously wrong.

Here you need a double digit Week number, so a small change in the suggested formula should do it:

YearWeek = YEAR('Table'[Date]) & FORMAT(WEEKNUM('Table'[Date]),"00")

The sequence should now work.

Upvotes: 0

Jon
Jon

Reputation: 4957

From the images it looks like there is no sort on the year and week, just by the week. You need to add a column that has a year week key, that you can sort by. For example 202101 for the week one of 2021.

Assuming you have a date like dd/mm/yyyy format, for example 11/04/2021 in DAX you can use:

YearWeek = YEAR('Table'[Date]) & WEEKNUM('Table'[Date]) 

enter image description here

This should now sort the data correctly. If you want you can add another column, that is more user friendly like WK01-2021, if you wish, you can then sort by that column, or use the new key column to sort the textual one.

If you just have a year and week column, create a new column that concatenates the two.

For this you should have a Calendar table, that contains a the date groupings that you you need. For example using CALENDARAUTO or you can do it in Power Query here or here.

Upvotes: 1

Related Questions