Overlapping Weeks Between Months in Calendar Logic

[Harrisfil]

Hello,

I’m working on creating a custom week column in my calendar dataset and have encountered a challenge with weeks that span across months. Here’s a detailed explanation of the issue:

The Issue

When a week overlaps two months, it is currently being assigned the same week number in both months. For example:

• Week 13 spans from March 26, 2023, to April 1, 2023. Currently, all these days are labeled as "Week 13" in both March and April.

This creates a problem in reporting because:

1. The first day of April (April 1) should ideally belong to April's dominant week (Week 14), not Week 13.
2. The weeks that overlap months need to be consistently assigned to the month with the most days of that week.

What I Want to Achieve

I would like to:

1. Ensure each day in a month is assigned to the correct week for that month based on the majority of that week’s days.
2. For overlapping weeks, the month with the most days in the week should "win" and determine the week number for all dates in that month.

Example

Here’s a sample dataset with the current week assignment (WKFormat) and what I want the corrected week assignment (correctedWKformat) to look like:

Year Date MonthName WeekOfYear WKFormat correctedWKformat

2023	20/3/2023	March	12	WK12	WK12
2023	31/3/2023	March	13	WK13	WK13
2023	1/4/2023	April	13	WK13	WK14
2023	2/4/2023	April	14	WK14	WK14

In this example:

• Week 13 spans 6 days in March and 1 day in April. Therefore, Week 13 should belong entirely to March.
• The first day of April (April 1) should start with Week 14 since it belongs to the majority of the next week (Week 14).

My Request

Has anyone encountered a similar issue or has suggestions for how to:

1. Dynamically assign weeks to the correct month based on where the majority of a week’s days fall?
2. Ensure the corrected week assignment doesn’t leave gaps or overlaps?

I’d appreciate any input, whether it’s ideas, examples, or best practices.

Thank you for your help!

[Anonymous]

Hi @Harrisfil ,

I made simple samples and you can check the results below:

DimDate = 
ADDCOLUMNS (
    'Table',
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMMM" ),
    "MonthSort", MONTH ( [Date] ),
    "Day", DAY ( [Date] ),
    "DayName", FORMAT ( [Date], "DDDD" ),
    "WeekDay", WEEKDAY ( [Date],1 ),
    "WeekStart",
        [Date] - WEEKDAY ( [Date], 1 ) + 1,
    "WeekEnd",   [Date] - WEEKDAY([Date], 1) + 7
)


CorrectedWK = IF([WeekDay] in {5,6,7}&&MONTH([Date])>MONTH([WeekStart]),[Weeknum] +1 ,[Weeknum])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Harrisfil]

Thank you for your answer .
i tried it in a full calendar and it is not giving the desired result for allo months across years. For example 31/3/2024 -01/4/2024  it gives same week.

Thanks again for spending time on that 🙂

[Anonymous · Accepted solution]

Hi @Harrisfil ,

I changed the arithmetic logic and you can check the new attachment.

New WeekStart = IF(MONTH('DimDate'[WeekStart])<>MONTH([Date]),[WeekStart]+7,[WeekStart])

New Weeknum = WEEKNUM([New WeekStart],1)

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[Harrisfil]

Thank you very much for your effort and your time