How to efficiently create a weeks calculator

[dolphin18]

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

[Cookistador · Accepted solution]

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
    AddWeekNumberColumn

[AlienSx · Accepted solution]

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_no

[dolphin18]

I 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. 

[AlienSx]

replace #date(2025, 6, 24) with Date.AddWeeks(Date.From(DateTime.LocalNow()), 1) in my code. M has so many date functions

[dolphin18]

It worked! I had to adjust it a bit to account for the time too, but thank you so much!! 

[Cookistador · Accepted solution]

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
    AddWeekNumberColumn

[dolphin18]

Thank you @Cookistador! This also worked for me with the small adjustment from the other solution above. Appreciate the help!!