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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kathyyy19
Helper III
Helper III

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

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!! 🙂

 

Annotation 2023-06-06 164533.png

3 REPLIES 3
amitchandak
Super User
Super User

@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...

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?

Thank you Amit! I will try your method!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.