How to assign numbers to each week in calendar table (i.e. Week 1, week 2, week 3..)

[kathyyy19]

Hi Everyone,

I learnt to create a calenar table using DAX and would like to learn about how to assign numbers to each week for reporting purposes. Below is the table I have created.  

In this table, I'd like to add a column showing week number. For example, from 2023-05-24 to 2023-05-31 is Week 1. Only week 1 is from a Wednesday to Wednesday. From Week 2 and onwards, I'd like to have the weeks running from Thursday to Wednday. That being said, Week 2 is 2023-06-01 to 2023-06-07. Week 3 is 2023-06-08 t0 2023-06-14 etc. 

Does anyone know how to write a DAX to achieve this result? 

Thank you in advance for your help!! 🙂

[amitchandak]

@kathyyy19 , do you want start you week always from 1st jun and merge week 53 in 52 ?

First have year start date like

Start year = Date( if( month([date]) <6, year([date]) -1, Year([date])) ,6,1)

Then have week like

Week =

var _week = quotient([Start Year],[Date],day),7)

return

if(_week <=52, _week, 52)

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

[kathyyy19]

Hi Amit,

Thanks for your comment. I'd like my first week starting from May 24 2023. First week is running from 2023/05/24-2023/05/31. Second week= 2023/06/01-2023/06/07. Third week=2023/06/08-2023/06/14 etc. Can you show me how to achieve this please?

[kathyyy19]

Thank you Amit! I will try your method!