Nice work. I adapted your solution to subtract workday holiday days.
Step 1: Added Public Holidays table to model (no physical relationship)
Step 2: Added Calc Column to Date Table designating dates that are holidays as 1, otherwise 0
VAR IsHoliday =
'Public Holidays'[Holiday Binary],
IF( IsHoliday = 1, 1, 0)
Step 3: Added calculated column to remove workday holidays from total:
Workday Not Holiday =
Dates[DayInWeek] = 5, 0,
Dates[DayInWeek] = 6, 0,
Dates[Holiday] =1, 0,
Then, adapted your measures to calculate total days using the Workday Not Holiday field from 3) above in place of of your workday field. Here’s the outcome:
Full solution file posted below.
Hope this is helpful.
eDNA_Total working days not incl holidays solution.pbix (185.9 KB)
P.S. I used Australian holidays in my Public Holiday table, because that’s what I had available from the previous example I worked up. However, it’s fairly straightforward to generate these by web scraping from sites like this
using the Power BI web connector.