Forum Discussion

JLK84's avatar
JLK84
Frequent Visitor
5 years ago
Solved

Return custom weeknum based on specific logic

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 202104nonoyesno 
02/04/2021 202104nononoyes1
03/04/2021 202104yesnonono 
04/04/2021 202104yesnonono 
05/04/2021 202104noyesnono 
06/04/2021 202104nononoyes1
07/04/2021 202104nononoyes1
08/04/2021 202104nononoyes1
09/04/2021 202104nononoyes1
10/04/2021 202104yesnonono 
11/04/2021 202104yesnonono 
12/04/2021 202104nononoyes2
13/04/2021 202104nononoyes2
14/04/2021 202104nononoyes2
15/04/2021 202104nononoyes2
16/04/2021 202104nononoyes2
17/04/2021 202104yesnonono 
18/04/2021 202104yesnonono 
.....................
26/04/2021 202104nononoyes4
27/04/2021 202104nononoyes4
28/04/2021 202104nononoyes4
29/04/2021 202104nononoyes4
30/04/2021 202104nonoyesno 

 

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

Thank you in advance,
Jack

Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

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

     

     

1 Reply

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    5 years ago

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