Week number - week starting first monday of year

[Kamilchoi86]

Hi all,

How can I get the week each date of the year belongs to where the start of week 1 is the first Monday of the year.

Thank you

[Mohan-anan]

This is as simple to use 
"

1. Open your Power BI Desktop project.

2. In the "Fields" pane on the right-hand side, select the table to which you want to add the calculated column.

3. In the "Modeling" tab, click on the "New Column" button in the "Calculations" group.

4. In the formula bar that appears at the top of the page, enter the following DAX formula to calculate the week number based on a specific date column. For example, if you have a date column called "Date" and you want to calculate the week number, you can use the following formula:
   
   Week Number = WEEKNUM([Date], 1)
   
   
   

5. 1 if week starts with Monday  

"

[Syndicate_Admin]

Hi, Did you manage to solve this issue and create the week numbers? I have the same thing to do and followed the proposed solutions and got the same errors. Thanks, Agata

[AgataJ]

Update to my post* The solution works for me, I misunderstood it a little yesterday, created new custom column in original Calendar Table, not in new Query table. My mistake. Agata

[ppm1 · Accepted solution]

Here's one way to do it in the query editor.  To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.

let
today = Date.From(DateTime.LocalNow()),
StartDate = Date.AddYears(Date.StartOfYear(today), -5), //or used a fixed date like #date(2020,1,1)
EndDate = Date.AddYears(Date.EndOfYear(today), 0), //or use a fixed date like #date(2022,12,31)
DateList = List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1,0,0,0)),
InitialTable = Table.FromColumns({DateList}, {"Date"}),
#"Changed Type" = Table.TransformColumnTypes(InitialTable,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "WeekOfYear", each let 
FirstMondayCY = List.Select(List.Transform({0..6}, (x)=>Date.AddDays(#date(Date.Year([Date]),1,1), x)), (y)=>Date.DayOfWeekName(y) = "Monday"){0},
FirstMondayPY =  List.Select(List.Transform({0..6}, (x)=>Date.AddDays(#date(Date.Year([Date])-1,1,1), x)), (y)=>Date.DayOfWeekName(y) = "Monday"){0},
Result = if [Date] < FirstMondayCY then FirstMondayPY else FirstMondayCY
in 
Number.RoundUp((Duration.TotalDays([Date]-Result)+1)/7,0))
in
    #"Added Custom"

The key step is to add a custom column with an expression like this:

let 
FirstMondayCY = List.Select(List.Transform({0..6}, (x)=>Date.AddDays(#date(Date.Year([Date]),1,1), x)), (y)=>Date.DayOfWeekName(y) = "Monday"){0},
FirstMondayPY =  List.Select(List.Transform({0..6}, (x)=>Date.AddDays(#date(Date.Year([Date])-1,1,1), x)), (y)=>Date.DayOfWeekName(y) = "Monday"){0},
Result = if [Date] < FirstMondayCY then FirstMondayPY else FirstMondayCY
in 
Number.RoundUp((Duration.TotalDays([Date]-Result)+1)/7,0)

Pat

[IvanKo]

Thank you for sharing this solution with us

[Anonymous]

When i paste this code into Power Query advanced editor it gives me a correct "Date" column and a "WeekOfYear" column with ERRORS

[v-stephen-msft]

Hi @Kamilchoi86 ,

The solution in Power Query using M language.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI11DVUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
    #"Added Custom" = Table.AddColumn(Source, "EndDate", each Date.From(DateTime.LocalNow())),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"EndDate", type date}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type2",{{"StartDate", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Dates", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Expanded Dates" = Table.ExpandListColumn(#"Added Custom1", "Dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates", type date}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"StartDate", "EndDate"}),
    #"Added Custom2" = Table.AddColumn(#"Removed Columns1", "Custom", each Date.DayOfWeek([Dates],Day.Monday)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each ([Custom] = 1))
in
    #"Filtered Rows"

The solution in Power BI Desktop using DAX.

This is the formula to create a calculated table which returns the dates of all Mondays of the year.

Table = FILTER(ADDCOLUMNS(CALENDAR(DATE(2022,1,1),DATE(2022,12,31)),"WeekDay",WEEKDAY([Date],2)),[WeekDay]=1)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mussaenda]

Hi @Kamilchoi86 ,

You would need to declare the start of the wek on your power query ( I am assuming you are looking for a power query solution)

Check the same topics that were solved:

Solved: how to change the start of week in power query - Microsoft Power BI Community