Forum Discussion
Week number - first full week rather than 1st January?
First let me remark that I have never heard about such a system of week numbering.
With ISO, the weeks start on Monday and the first week of the year is the first Thursday of the year.
Or, in other words: Jan 4 is always in week 1.
If Jan 1 is on Thursday, then the week from Monday Dec 29 thru Sunday Jan 4 is ISO week 1.
If Jan 1 is on Friday, then the week from Monday Jan 4 thru Sunday Jan 10 is ISO week 1.
The following code gives the week number according to your definition:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Inserted Start of Week" = Table.AddColumn(#"Changed Type", "Start of Week", each Date.StartOfWeek([Date],Day.Sunday), type date), AddedJan1 = Table.AddColumn(#"Inserted Start of Week", "Jan1", each #date(Date.Year([Start of Week]),1,1), type date), AddedWeek = Table.AddColumn(AddedJan1, "Week", each 1+Number.IntegerDivide(Number.From([Start of Week] - [Jan1]),7),Int64.Type), #"Removed Columns" = Table.RemoveColumns(AddedWeek,{"Start of Week", "Jan1"}) in #"Removed Columns"
Community ChampionFirst let me remark that I have never heard about such a system of week numbering.
With ISO, the weeks start on Monday and the first week of the year is the first Thursday of the year.
Or, in other words: Jan 4 is always in week 1.
If Jan 1 is on Thursday, then the week from Monday Dec 29 thru Sunday Jan 4 is ISO week 1.
If Jan 1 is on Friday, then the week from Monday Jan 4 thru Sunday Jan 10 is ISO week 1.
The following code gives the week number according to your definition:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
#"Inserted Start of Week" = Table.AddColumn(#"Changed Type", "Start of Week", each Date.StartOfWeek([Date],Day.Sunday), type date),
AddedJan1 = Table.AddColumn(#"Inserted Start of Week", "Jan1", each #date(Date.Year([Start of Week]),1,1), type date),
AddedWeek = Table.AddColumn(AddedJan1, "Week", each 1+Number.IntegerDivide(Number.From([Start of Week] - [Jan1]),7),Int64.Type),
#"Removed Columns" = Table.RemoveColumns(AddedWeek,{"Start of Week", "Jan1"})
in
#"Removed Columns"
Very nice thanks Marcel!
I agree the week numbering doesn't feel very native to PowerBI, but thanks for the tip it's great that now the tech doesn't need to get in the way of the unique stakeholder requirements (I'm also planning on discussing potentially simplifying them to!).
Thanks to Anonymous for posting this question and many thanks to MarcelBeug for the very helpful response.
Just for your information, this week number format with Week 1 being the first full week of the year, starting on Sunday, is known in the tire industry as the DOT Week and it is used to identify the week of the year in which a tire was manufactured, among other things.
I'm not sure where else it is used but you can imagine the challenges of trying to juggle multiple week numbering systems. Your formula is a huge help and a welcome addition to my dynamic date calendar.
EmanuelHelper I
Hi Marcel,
Thanks for the answer for this question. I need to show this in my X axis (to have the week number same as the Outlook calendar).
I don't know, though, where should the code go, as a custom column (I tried to add a custom column to my table changing the name on the code from Table1 to the name of my own table) but the result is in error.
Can you hep me with the specific location where the code should go in?
Thanks
Emanuel