Set month by number of days in working week

[Igor_Ribeiro]

I need each week of the year to be within only the month with the most working days.

Example:

On Week 13 of 2021 (29/03/2021 - 02/04/2021) there are 3 working days in march (29/03/2021 - 31/03/2021) and 2 working days in april (01/04/2021 - 02/04/2021). 

So I need to set the entire Week 13 as March, including the 2 days of April which should be considered to be March.

[Vegar]

You can do this identifying the most frequent month using Mode(). See sample script below.

LET _start = '2021-01-01';
LET _end = '2022-04-26';

Calendar:
LOAD WeekName(Date) as YearWeek,
   Month(Date) as Month_calendar,
   *;
LOAD
   dayname(RecNo() -1+ '$(_start)') as Date
AutoGenerate(1+'$(_end)'-'$(_start)')
;

LEFT JOIN
LOAD
   YearWeek,
   Mode(Month(Date)) as Month
Resident Calendar
Group by YearWeek
;

[Igor_Ribeiro · Accepted solution]

This method works but it doen't consider working days. Eventually I found a better solution:

IF(NetWorkDays(WeekStart(Date), MonthEnd(WeekStart(Date))) > NetWorkDays(MonthStart(WeekEnd(Date)), WeekEnd(Date)), Month(WeekStart(Date)), Month(WeekEnd(Date)))) as [Month_Floor]