Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dolphin18
Frequent Visitor

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:

IDStart DateWeek
A6/23/250
B7/1/251
C8/11/247
D4/2/25null

 

Any help or advice would be greatly appreciated! Thanks!!

2 ACCEPTED SOLUTIONS
Cookistador
Super User
Super User

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

 

View solution in original post

AlienSx
Super User
Super User

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

View solution in original post

6 REPLIES 6
AlienSx
Super User
Super User

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

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. 

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

Cookistador
Super User
Super User

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

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.