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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
lawada
Helper III
Helper III

sort weeks by dates in the calendar

hello ,

im  trying to create a column that sorts weeks according to dates rather than week name. 

ex. w1 2022 would be : january1-2022 till january7-2022

w2 would be be january8-2022 till january14-2022

w3 woud be  january15-2022 till  january21-2022 

where W1 should always be first 7 days of the begining of the year

 

i was trying to use weeknum function but its sorting weeks according to weekdays from Sunday till Saturday

 

1 ACCEPTED SOLUTION

@lawada , Try columns like

 

Week No =
Var _start = date(Year([Date]),1,1)
return
quotient(datediff(_start,[Date],day) ,7)+1

 

Week Year = Year([Date])*100 + [Week No]

 

Week Start Date = date(Year([Date]),1,1) + ([Week NO]-1) * 7

if(isfiltered('Table Name'[Column name]), Selectedvalue('Table Name'[Column name]), "All")

 

Week That Resets Yearly
https://community.powerbi.com/t5/Community-Blog/Week-That-Resets-Yearly-Decoding-Date-and-Calendar-3...

View solution in original post

3 REPLIES 3
ValtteriN
Super User
Super User

Hi,

WEEKNUM accepts second parameter where you can define the start date of the week. I recommend cheking this documentation about the function: https://docs.microsoft.com/en-US/dax/weeknum-function-dax

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/





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

Proud to be a Super User!




the documentation explains the sorting according to weekdays as well. im trying to do something different and sort by dates in the calender as the example i mentiond . any solution for that ?

@lawada , Try columns like

 

Week No =
Var _start = date(Year([Date]),1,1)
return
quotient(datediff(_start,[Date],day) ,7)+1

 

Week Year = Year([Date])*100 + [Week No]

 

Week Start Date = date(Year([Date]),1,1) + ([Week NO]-1) * 7

if(isfiltered('Table Name'[Column name]), Selectedvalue('Table Name'[Column name]), "All")

 

Week That Resets Yearly
https://community.powerbi.com/t5/Community-Blog/Week-That-Resets-Yearly-Decoding-Date-and-Calendar-3...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.