Forum Discussion
Net Working Days between dates - Multiple Countries with Holidays - Different Working Days per week
Hello All, Looking to get some help on this situation.
Objective is to get Networkdays between [startdate] and [enddate] based on weekly roster and holidays mapped for a [locationcode]
Main Table
[startdate] [enddate] [locationcode]
1 Jan 2020 6 Jan 2020 4195
5 Jan 2020 9 Jan 2020 2000
6 Jan 2020 14 Jan 2020 5600
....
Holidays Table
[locationcode] [holidaydate]
4195 4 Jan 2020
2000 8 Jan 2020
.....
Additional Info for Holidays Table: 5600 does not have any holidays hence no mention in this table.
WeeklyRoster Table
[locationcode] [weekdaynotworking]
4195 5
4195 7
2000 6
....
Additional Info for Weekly Roster Table:
1. Mapped as Day number of week, 1 being Monday.
2. Location 5600 works all 7 days hence not mention in this table.
Many thanks in advance.
Hi, Anonymous , the underlying logic is very simple, i.e. excluding holidays and non-working weekdays from assigned period; but the procedure is fairly verbose. I came up with a PQ solution and a DAX solution. You might want to refer to the attached file for details.
PQ solution,
DAX solution
3 Replies
Hi, Anonymous , the underlying logic is very simple, i.e. excluding holidays and non-working weekdays from assigned period; but the procedure is fairly verbose. I came up with a PQ solution and a DAX solution. You might want to refer to the attached file for details.
PQ solution,
DAX solution
So glad my solution is of help and it's kind of you to say so!