Forum Discussion
Solved
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...
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
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
It worked! I had to adjust it a bit to account for the time too, but thank you so much!!
