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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Analitika
Post Prodigy
Post Prodigy

Sorting year weeks in Power BI

Hello,

 

I would like to ask how to properly sort year week because in visual it seems unsorted:

Here is the view of it

 

Analitika_0-1653394254537.png

Is possible to fix it?

4 REPLIES 4
Adescrit
Impactful Individual
Impactful Individual

Hi @Analitika ,

 

Due to the "-" in the middle of the Year-Week number, that value is stored as text. This is the reason the sorting is not working for you.

 

In order to sort it correctly, you can replace the Year-Week formula you are currently using with the following:

 

Year-Week = 
    VAR __Year = Year( [Date] )
    VAR __WeekNum = WEEKNUM( [Date] )

RETURN
    IF( __WeekNum < 10, CONCATENATE( __Year, "-0" & __WeekNum ), CONCATENATE( __Year, "-" & __WeekNum ))

 

The difference is the "0" before any single-digit week numbers. This allows the sorting to work correctly.


Did I answer your question? Mark my post as a solution!
My LinkedIn
ddpl
Solution Sage
Solution Sage

Hi

Changed data type as Date and format as below

ddpl_0-1653394542121.png

 

I am getting this 

Analitika_0-1653395750536.png

 

Hi @Analitika ,

Please create a new column on your date table like below :

YearWeekIndex = dim_date[Year] * 100 +dim_date[Week of Year]
Then select your YearWeek column, go to sort by column and click on 'YearWeekIndex'. Your yearweek field will now be sorted using this column.

rohit_singh_0-1653396368691.png

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.