Forum Discussion
How to efficiently create a weeks calculator
Hello,
I am trying to find a better way to write my M code that calculates the week # that a date falls on from my current week (Week 0). So, for example, say I have a start date of 7/31/2025 and I am calculating the week from 6/24/25, the the output should be 'Week 5'. I have found 1 solution to give me the week # but I would like a more efficient way to calculate it. Here is the part of my code that gives me my desired output:
each if Date.IsInCurrentWeek[Start Date] then 0
else if Date.IsInNextNWeeks([Start Date], 1) then 1
else if Date.IsInNextNWeeks([Start Date], 2) then 2
else if Date.IsInNextNWeeks([Start Date], 3) then 3
else if Date.IsInNextNWeeks([Start Date], 4) then 4
else if Date.IsInNextNWeeks([Start Date], 5) then 5
....and so on and so on...
else null
However, you can see how much of a manual effort this is to list out one by one. Plus, it is not as efficient because a start date can be listed years in advance and my current code might not capture it. Is there a more efficient way to write my code to check each date in my 'Start Date' column and calculate what week it falls on from my current week?
I have included a table below to show my desired output:
| ID | Start Date | Week |
| A | 6/23/25 | 0 |
| B | 7/1/25 | 1 |
| C | 8/11/24 | 7 |
| D | 4/2/25 | null |
Any help or advice would be greatly appreciated! Thanks!!
Hi dolphin18
The following m code should help you,
Just rename YourPreviousStepName by the name of your previous step
let DesiredWeekStartDate = #date(2025, 1, 1), AddWeekNumberColumn = Table.AddColumn( YourPreviousStepName, "Week", each let DaysDifference = Duration.Days([Start Date] - DesiredWeekStartDate ), WeekNumber = if DaysDifference >= 0 then "Week " & Text.From(Number.RoundDown(DaysDifference / 7)) else null in WeekNumber, type text ) in AddWeekNumberColumnlet start_week = Date.StartOfWeek(#date(2025, 6, 24)), Source = #table( {"ID", "Start Date"}, { {"A", #date(2025, 6, 23)}, {"B", #date(2025, 7, 1)}, {"C", #date(2025, 8, 11)}, {"D", #date(2025, 4, 2)} } ), week_no = Table.AddColumn( Source, "Week", (x) => ((days_between) => if days_between < 0 then null else days_between / 7 )(Duration.Days(Date.StartOfWeek(x[Start Date]) - start_week)) ) in week_no
6 Replies
Hi dolphin18
The following m code should help you,
Just rename YourPreviousStepName by the name of your previous step
let DesiredWeekStartDate = #date(2025, 1, 1), AddWeekNumberColumn = Table.AddColumn( YourPreviousStepName, "Week", each let DaysDifference = Duration.Days([Start Date] - DesiredWeekStartDate ), WeekNumber = if DaysDifference >= 0 then "Week " & Text.From(Number.RoundDown(DaysDifference / 7)) else null in WeekNumber, type text ) in AddWeekNumberColumnThank you Cookistador! This also worked for me with the small adjustment from the other solution above. Appreciate the help!!
let start_week = Date.StartOfWeek(#date(2025, 6, 24)), Source = #table( {"ID", "Start Date"}, { {"A", #date(2025, 6, 23)}, {"B", #date(2025, 7, 1)}, {"C", #date(2025, 8, 11)}, {"D", #date(2025, 4, 2)} } ), week_no = Table.AddColumn( Source, "Week", (x) => ((days_between) => if days_between < 0 then null else days_between / 7 )(Duration.Days(Date.StartOfWeek(x[Start Date]) - start_week)) ) in week_noI got this to work! However, is there a way to not specify my start date? I want to be able to calculate from current week. For example, say the current date is 7/8/2025 then my new Week 1 will be from 7/13/2025-7/19/2025 as opposed to 6/29/2025-7/5/2025 when putting the start date of 6/24/2025. I tried inputting Date.IsInCurrentWeek but was getting an error.
replace #date(2025, 6, 24) with Date.AddWeeks(Date.From(DateTime.LocalNow()), 1) in my code. M has so many date functions
