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

[Anonymous]

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 the M Date.WeekOfYear but it doesn't appear flexible enough. And I don't think DAX WeekNum suit.

I'm wary of creating a custom function for this, as it feels like something which exists already.

Reading through the highlights of ISO 8601, week numbers can be a complex beast, so perhaps it's unwise not to use the ISO version of DAX WeekNum.

-thanks

Alex.

[NikkiBeak]

You could add a calculated column to your table, not the ideal as calculated columns can slow the report.

but here's a simple formula that could help

in this case my table had a year column but you could also use a formula to populate it.

IF(WEEKDAY(DATE(DT_Dates_Table[Year],1,1),2)=1,WEEKNUM(DT_Dates_Table[Date],2),WEEKNUM(DT_Dates_Table[Date],2)-1)

[MarcelBeug · Accepted solution]

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]

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

[Anonymous]

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.

[Anonymous]

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