Forum Discussion

NewbieJono's avatar
NewbieJono
Icon for Post Partisan rankPost Partisan
2 years ago
Solved

Working day week number

Hello i want to display 1st working day of the week as 1, 2nd as 2 etc. i

 

However I wish to exclude bank holidays and weekends.

 

For example on a week where there is a bank holiday on a Monday, Tuesday would show as a 1, wendesday as a 2 etc.

 

have a date table with weekday and bank holiday, weekend markers.

 

 

 

 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    2 years ago

    NewbieJono So that would be something like below. See PBIX attached below signature.

    Weekday Column = 
    VAR __Date = [Date]
    VAR __WeekNum = WEEKNUM(__Date,2)
    VAR __Table = 
        FILTER(
            ADDCOLUMNS( EXCEPT(CALENDAR( __Date - 7, __Date + 7), 'Holidays'), "__WeekNum", WEEKNUM([Date],2) ),
            [__WeekNum] = __WeekNum && [Date] <= __Date
        )
    VAR __Result = COUNTROWS(__Table)
RETURN
    __Result

     

     

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    NewbieJono So that would be something like below. See PBIX attached below signature.

    Weekday Column = 
    VAR __Date = [Date]
    VAR __WeekNum = WEEKNUM(__Date,2)
    VAR __Table = 
        FILTER(
            ADDCOLUMNS( EXCEPT(CALENDAR( __Date - 7, __Date + 7), 'Holidays'), "__WeekNum", WEEKNUM([Date],2) ),
            [__WeekNum] = __WeekNum && [Date] <= __Date
        )
    VAR __Result = COUNTROWS(__Table)
RETURN
    __Result