topic sort weeks by dates in the calendar in DAX Commands and Tips

sort weeks by dates in the calendar

lawada — Mon, 31 Jan 2022 08:37:15 GMT

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

Re: sort weeks by dates in the calendar

ValtteriN — Mon, 31 Jan 2022 08:49:49 GMT

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/

Re: sort weeks by dates in the calendar

lawada — Mon, 31 Jan 2022 09:05:10 GMT

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 ?

Re: sort weeks by dates in the calendar

amitchandak — Mon, 31 Jan 2022 09:19:46 GMT

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-5-Power-BI/ba-p/1187502