OK, thanks for the updated sample file. I think it’s working just right now. Buckle in - long post ahead, but I’ll try to clearly explain what I did.
created Holiday Binary calculated column in your Public Holidays table. Set to 1 for every row in the table - this will serve as the target result for our LOOKUPVALUE function below.
Created three binary calculated columns in the Dates table:
a) Workday Binary - 1 for weekdays, 0 for weekends
Workday Binary =
b) Holiday Binary - 1 if date is found in Public Holidays lookup table, 0 if not found.
Holiday Binary DT =
VAR Holiday =
LOOKUPVALUE( 'Public Holidays'[Holiday Binary], 'Public Holidays'[Date], Dates[Date], 0)
IF( Holiday = 1, 1, 0)
c) Workday Binary w Holidays - coded 1 if workday and not a holiday, 0 if otherwise. This is the column integral to the measures that follow - the other three are just interim steps to get to this point.
Workday Binary w Holidays =
IF ( Dates[Workday Binary] = 0, 0, IF ( Dates[Holiday Binary DT] = 1, 0, 1 ) )
We can check to make sure the logic is working correctly here by putting the binary columns into a table visual:
and sure enough, it behaves exactly as we expected it to. So far, so good.
Develop the measure to calculate workdays between the actual start date and end date. This is nearly identical to the measure we created week or so ago, the only significant difference being that we are now SUMXing the workday binary incorporating the holiday logic discussed above, whereas previously we were only totaling the workday binary. (Note: per Sam’s best practice, I have put this and all the measures below into a separate Key Measures table.)
Workdays Between Actual Start and End w Holidays =
VAR Startday =
SELECTEDVALUE ( 'Project Management Data'[Start Date] )
VAR Endday =
SELECTEDVALUE ( 'Project Management Data'[End Date] )
VAR Daterange =
DATESBETWEEN ( Dates[Date], Startday, Endday )
VAR Daterangeworkdays =
NATURALLEFTOUTERJOIN ( Daterange, Dates )
Startday = BLANK (),
Endday = BLANK (),
SUMX ( Daterangeworkdays, Dates[Workday Binary w Holidays] )
Develop the measure to calculate workdays between the forecast start date and end date (Workdays Between Forecast Start and End w Holidays). Because we developed the above measure using variables, we can use the EXACT same DAX code as above, just changing the Startday variable to Forecast Start Date, instead of Actual Start Date.
Finally, create a simple measure quantifying in total work days (factoring in holidays) how much sooner (or later) the actual start date was relative to the forecast start date. In the way I’ve constructed this measure, positive numbers are “good”, reflecting an earlier than forecast start date, but if you want negative numbers to be “good”, obviously you can just flip the order of this measure expression around.
Total Workdays w Holidays Worked in Advance of Forecast Start =
[Workdays Between Actual Start and End w Holidays] - [Workdays Between Forecast Start and End w Holidays]
Here’s the table visual showing how all of this is working:
One note - you’ll notice that the first two rows of the visual have no value returned for the forecast start date measure. This is because in your data set the end date for these two tasks are earlier than the start date. Logically, this just seems to be a data entry error, but it returns an empty table in the DATESBETWEEN portion of the measure, and thus a blank value for the measure itself. Otherwise, I’ve checked the calculations against the calendar and your public holiday list and it seems to be working exactly as specified.
I hope this gets you what you need. Please give a shout if you have questions.
Full PBIX solution file posted below.
eDNA forum - workdays and holidays between.pbix (485.4 KB)