Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Week number - first full week rather than 1st January?

Hi,   How would I go about calculating a week number but having the first full week being the first full week, rather than 1st January? And the week starting on a Sunday.   I'm currently using th...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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"

     

MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
8 years ago

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"

 

Emanuel's avatar
Emanuel
Icon for Helper I rankHelper I
5 years ago

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