Getting data from a particular column in a sharepoint list based on two conditions in another list

[APD_Karl]

I am trying to remove weekends and public holidays from a date range. I have the weekend removal working but the formula for holidays is proving beyond my understanding. I have a "counif" formula at the end that would work if I just had one set of public holidays, but unfortunately my organisation crosses multiple regions with different holidays. (See my formula so far at the end of this post).

I have 2 SharePoint Lists - (1) APD Staff List and (2) APD Public Holidays. The first list has all our Staff Names and their Location (NSW, NT, QLD, SA, VIC, WA, NZ) and I need to get the dates from the corresponding "Location" Column in the second list. (See Images Below).

I'm not even sure which function I need to use - "If/Else", "Calculate" or "column=condition1 && condition2". Please help, I'm losing sleepp over this now.

APD Staff List

APD Public Holidays

RoundDown(DateDiff('Leave Start Date_DatePicker'.SelectedDate, 'Leave End Date_DatePicker'.SelectedDate, Days) / 7, 0) * 5 +
If(Weekday('Leave End Date_DatePicker'.SelectedDate) >= Weekday('Leave Start Date_DatePicker'.SelectedDate),
If(Weekday('Leave End Date_DatePicker'.SelectedDate) = Weekday('Leave Start Date_DatePicker'.SelectedDate) && (Weekday('Leave End Date_DatePicker'.SelectedDate) = 1 || Weekday('Leave End Date_DatePicker'.SelectedDate) = 7),
0,
If(RoundDown((Weekday('Leave End Date_DatePicker'.SelectedDate) - Weekday('Leave Start Date_DatePicker'.SelectedDate))/5,0) >= 1,
5,
If(Weekday('Leave End Date_DatePicker'.SelectedDate) = 7,
Weekday('Leave End Date_DatePicker'.SelectedDate) - Weekday('Leave Start Date_DatePicker'.SelectedDate),
If(Weekday('Leave Start Date_DatePicker'.SelectedDate) = 1,
Weekday('Leave End Date_DatePicker'.SelectedDate) - Weekday('Leave Start Date_DatePicker'.SelectedDate),
Mod(Weekday('Leave End Date_DatePicker'.SelectedDate) - Weekday('Leave Start Date_DatePicker'.SelectedDate), 5) + 1)))),
(7 - Weekday('Leave Start Date_DatePicker'.SelectedDate) + Weekday('Leave End Date_DatePicker'.SelectedDate) - 1)) 
- CountIf('APD Public Holidays', Date >= 'Leave Start Date_DatePicker'.SelectedDate, Date <= 'Leave End Date_DatePicker'.SelectedDate)

[Greg_Deckler]

Maybe Net Work Days? https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

If not, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[APD_Karl]

Thanks @Greg_Deckler, but that unfortunately doesn't solve my problem. It shows how to remove weekends (Which I've done) and remove a single date dd/mm/yyyy. It doesn't show how to detime which colomn of dates to reference in a second list based on criteria from a first list. That's the part that is confusing me the most.

I am building a Leave Application Form in Power Apps.

1. The User selects their [Staff Name] from a Sharepoint List "APD Staff List".
2. This same list has a column [Location] which tells me which region that staff member works in.
3. A second SharePoint List "APD Public Holidays" has dates in columns with headings based on the location value in the first List.

So from list "A" - I know (1), I need to determine (2) and use that value to get the dates out of list "B" where the column heading = value (3).

Then I need to combine that with the CountIf statement.

This will show how little I know but Something like... 

- CountIf (( If 'APD Staff List', [Staff Name] Then 'APD Staff List', [Location] = "value" && 'APD Public Holidays', [value] = "Date") Date >= 'Leave Start Date_DatePicker'.SelectedDate, Date <= 'Leave End Date_DatePicker'.SelectedDate)

[Greg_Deckler]

I have the removal of Holidays in there. Unless I am mistaken, you want EXCEPT.

Not sure if I understand your issue fully honestly. Sample data (text), expected output would be very helpful. 

[APD_Karl]

Hi @Greg_Deckler 

Sorry, it probably my inexperience that's the issue. I have been searching on this for a while and every post I've seen so far has used a differnet function and did not really address my situation. The more I read and the more code I see, the more I get confused.

If you think your code from the previous link is the answer, would you be able to put comments in it so I can better understand what's happening in each section?

The Leave Form (Below) allows the user to choose a start date and end date for their requested leave and the "Number of Days" field is calculated for them based on that date range minus weekends and public holidays in the region they're located in. The output for that field is a whole number. The Staff member's "location" is not displayed in the form (Not the same as base office), but is in the same data list as "Staff name".

The CountIf statement (Last line in the code from my original post) works if all staff members had the same public holidays.

[Greg_Deckler]

Well, the best version and a detailed explanation is in my book DAX Cookbook. Basically what you are doing is you are creating a table variable in memory __Calendar that spans all the dates of your data in context. Various techniques for ensuring that but you end up with a CALENDAR function in the end as the base of your table variable and you use ADDCOLUMNS to add a column about what weekday it is. (WEEKDAY([Date,2]). You use the 2 parameter here so that Saturday's are 6 and Sundays are 7. You then wrap this with an EXCEPT of this table as the first table and the second table being your Holidays table. Now you are left with only dates that are NOT holidays. Finally, you return a COUNTX of the FILTER of the table where the Weekday column is < 6. So, what you end up with is a count of days between the maximum date in context and the minimum date in context that excludes holidays and is only weekdays.