Search code examples
databasecalendarpowerbiweek-numberlinegraph

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

Solution

  • 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.