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
HenryJS
Post Prodigy
Post Prodigy

Custom Week Number Column

Hi all,

 

How can I create a Week Number column from a date column I have in my data?

 

I want the below data range to be 'Week 1' and so on...

 

10/04/20 00:00:00 - 17/04/2020 00:00:00 - WEEK 1

 

Thanks,

2 ACCEPTED SOLUTIONS
jdbuchanan71
Super User
Super User

There is a week number function in DAX that should work for you.

 

Column = 
VAR _Wk = WEEKNUM ( DATES[Date] )
RETURN
"week " & _Wk

View solution in original post

v-eachen-msft
Community Support
Community Support

Hi @HenryJS ,

 

You could refer to the following codes in query editor.

= "WEEK " & Number.ToText(Date.WeekOfYear([Column1]))

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

6 REPLIES 6
v-eachen-msft
Community Support
Community Support

Hi @HenryJS ,

 

You could refer to the following codes in query editor.

= "WEEK " & Number.ToText(Date.WeekOfYear([Column1]))

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
amitchandak
Super User
Super User

@HenryJS , you can do like.

 

replace the startofyear('Date'[Date]) with starting date you want

 

Week Start With Year=  QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY),7)+1 

Week Start Date with Year = var _1 =QUOTIENT(DATEDIFF(STARTOFYEAR('Date'[Date]),'Date'[Date],DAY),7) return STARTOFYEAR('Date'[Date]) + if(_1<52,_1,51)*7

 

 

jdbuchanan71
Super User
Super User

There is a week number function in DAX that should work for you.

 

Column = 
VAR _Wk = WEEKNUM ( DATES[Date] )
RETURN
"week " & _Wk
ryan_mayu
Super User
Super User

@HenryJS 

 

I have some questions

1.  There are 8 days from day 10 to day 17. Do you want each custom week has 8 days?

2. how to stop the week? If you don't set up the terminal day. The week number will continue growing.

 

Column = 
VAR week="week"&ROUNDUP(('Table'[Date]-date(2020,4,10)+1)/7,0)
return if('Table'[Date]<date(2020,4,10),blank(),week)

1.PNG





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

Proud to be a Super User!




how do the weeks stop? when you reach December 31 I should stop.

@Syndicate_Admin 

it  looks like the fiscal year and calendar year are the same. then you can use WEEKNUM function





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

Proud to be a Super User!




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.