Forum Discussion
Helper IOverlapping Weeks Between Months in Calendar Logic
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:
- The first day of April (April 1) should ideally belong to April's dominant week (Week 14), not Week 13.
- 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:
- Ensure each day in a month is assigned to the correct week for that month based on the majority of that week’s days.
- 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:
- Dynamically assign weeks to the correct month based on where the majority of a week’s days fall?
- 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!
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
HarrisfilThank 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 🙂
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 ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.