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!).