Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@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
@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