cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slyfox Regular Visitor
Regular Visitor

Working Days except list of dates

Dear Community,

 

Please help to figure out what is wrong with formula I have

WorkingDays =
var month = month(D_Date[LINK_Date])
var year = year(D_Date[LINK_Date])
return
CALCULATE(COUNTROWS(D_Date), filter( all(D_Date[LINK_Date]), D_Date[LINK_Date]<MAX(D_Date[LINK_Date]) &&YEAR(D_Date[LINK_Date])=year&& MONTH(D_Date[LINK_Date])=month ))

Expected result: Get a new column with Number of passed working days of Month,
Saturday and Sundays always Not working days
additionally few known holiday dates per month 
like this

LINK_Date  WeekDayName  WorkingDayCount
1-May-19Wednesday 
2-May-19Thursday1
3-May-19Friday 
4-May-19Saturday 
5-May-19Sunday 
6-May-19Monday2
7-May-19Tuesday3
8-May-19Wednesday4
9-May-19Thursday5
10-May-19Friday6
11-May-19Saturday 
12-May-19Sunday 
13-May-19Monday7
14-May-19Tuesday8
15-May-19Wednesday9
16-May-19Thursday10
17-May-19Friday11
18-May-19Saturday 
19-May-19Sunday 
20-May-19Monday12
21-May-19Tuesday13
22-May-19Wednesday14
23-May-19Thursday15
24-May-19Friday16
25-May-19Saturday 
26-May-19Sunday 
27-May-19Monday17
28-May-19Tuesday18
29-May-19Wednesday19
30-May-19Thursday20
31-May-19Friday21

pbix file   https://wetransfer.com/downloads/29acdb38499b8ddff1eefd1b6dcc6e2020190516204914/701b113e64c1c1c2ca94...
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Working Days except list of dates

You can use this Calculated Column:

WorkingDayCount = 
Var __CurrentDate = 'Table'[Date]
Var __CurrentMonth = 'Table'[MonthName]
Var __CurrentYear = YEAR( 'Table'[Date] )
Var __CurrentDay = 'Table'[DayofWeek] 
Var __List= {"Saturday" , "Sunday" }
RETURN

CALCULATE(
    COUNTROWS( 'Table' ),
    Filter( 
        ALL( 'Table'),
        __CurrentDate >= 'Table'[Date]
        && NOT 'Table'[DayofWeek] IN __List
        && NOT __CurrentDay IN __List
        && __CurrentMonth = 'Table'[MonthName]
        && __CurrentYear = Year( 'Table'[Date])
)
)

Runnign Count of Working Datys.png 

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Working Days except list of dates

You can use this Calculated Column:

WorkingDayCount = 
Var __CurrentDate = 'Table'[Date]
Var __CurrentMonth = 'Table'[MonthName]
Var __CurrentYear = YEAR( 'Table'[Date] )
Var __CurrentDay = 'Table'[DayofWeek] 
Var __List= {"Saturday" , "Sunday" }
RETURN

CALCULATE(
    COUNTROWS( 'Table' ),
    Filter( 
        ALL( 'Table'),
        __CurrentDate >= 'Table'[Date]
        && NOT 'Table'[DayofWeek] IN __List
        && NOT __CurrentDay IN __List
        && __CurrentMonth = 'Table'[MonthName]
        && __CurrentYear = Year( 'Table'[Date])
)
)

Runnign Count of Working Datys.png 

View solution in original post

edhans Super Contributor
Super Contributor

Re: Working Days except list of dates

This will tell you the weekday count (M-F), but you'd need a separate table to link to this one to explicitly call out holidays and exclude this from the data as well.

WorkingDays = 
CALCULATE(COUNTROWS(D_Date),
    FILTER(
        ALL(D_Date[LINK_Date]),
        DAY(D_Date[LINK_Date])<=D_Date[DATE_DayNumberInMonth]
        && YEAR(D_Date[LINK_Date])=D_Date[DATE_YearNumber]
        && MONTH(D_Date[LINK_Date])=D_Date[DATE_MonthNumber]
        && WEEKDAY(D_Date[LINK_Date],2) <> 6
        && WEEKDAY(D_Date[LINK_Date],2) <> 7
    )
)

I'd also be inclined to do this in Power Query - at least using that to define what is and isn't a workday returning a 1/0 or True/False.

See this post here and the related PBIX file as it is very similar to what you are describing. It has the holiday table in it.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (1,693)