Custom Week Number Column

[HenryJS]

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,

[jdbuchanan71 · Accepted solution]

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

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

[v-eachen-msft · Accepted solution]

Hi @HenryJS ,

You could refer to the following codes in query editor.

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

[amitchandak]

@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 · Accepted solution]

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

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

[ryan_mayu]

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

[Syndicate_Admin]

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

[ryan_mayu]

@Syndicate_Admin 

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