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

     

     

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