cancel
Showing results for
Did you mean:

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

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
Super User

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

3 REPLIES 3
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!

Proud to be a Super User!

Helper III

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 ?

Super User

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors