Return custom weeknum based on specific logic

[JLK84]

Hi,

I am stuck with this problem and I have no more ideas so any help will be greatly appreciated

Goal:
I have all the columns I need, except the last one. What I'm trying to create is the last column Week number based on 5 first working Days.

Note: In my project working day = only if day is no weekend, no Bank Holiday and no first or last day of month.

Logic for this column: 
(IF WorkingDay (Y/N) column = "yes") then count first 5 working days and return as week 1. Then count another 5 working days and return 2 etc.
Exclude the first and the last day of the month from the calculations.

Of course, the calculation should be unique for each month, so I entered the YearMonthID column.

Date	YearMonthID	Weekend	BankHoliday	FirstORLastDayOfMonth	WorkingDay (Y/N)	Week number based on 5 first working Days
01/04/2021	202104	no	no	yes	no
02/04/2021	202104	no	no	no	yes	1
03/04/2021	202104	yes	no	no	no
04/04/2021	202104	yes	no	no	no
05/04/2021	202104	no	yes	no	no
06/04/2021	202104	no	no	no	yes	1
07/04/2021	202104	no	no	no	yes	1
08/04/2021	202104	no	no	no	yes	1
09/04/2021	202104	no	no	no	yes	1
10/04/2021	202104	yes	no	no	no
11/04/2021	202104	yes	no	no	no
12/04/2021	202104	no	no	no	yes	2
13/04/2021	202104	no	no	no	yes	2
14/04/2021	202104	no	no	no	yes	2
15/04/2021	202104	no	no	no	yes	2
16/04/2021	202104	no	no	no	yes	2
17/04/2021	202104	yes	no	no	no
18/04/2021	202104	yes	no	no	no
...	...	...	...	...	...	...
26/04/2021	202104	no	no	no	yes	4
27/04/2021	202104	no	no	no	yes	4
28/04/2021	202104	no	no	no	yes	4
29/04/2021	202104	no	no	no	yes	4
30/04/2021	202104	no	no	yes	no

Does anyone have suggestions for how to get the column values I need?

Thank you in advance,
Jack

[AlexisOlson · Accepted solution]

You can count the number of working days in the month to date and then bucket those into multiples of 5.

WeekNumber =
VAR WorkdaysMTD =
    CALCULATE (
        COUNT ( Table1[Date] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[YearMonthID] ),
            Table1[Date] <= EARLIER ( Table1[Date] )
        ),
        Table1[WorkingDay (Y/N)] = "yes"
    )
RETURN
    IF ( Table1[WorkingDay (Y/N)] = "yes", ROUNDUP ( WorkdaysMTD / 5, 0 ) )