Moving average excluding weekends and holidays

[Anonymous]

I have a table within PowerPivot currently that tracks a count of customers through our sales pipeline. From (by sales location) first interaction to charged sale. So far, I’ve creates a moving 5 day average that averages each task. Below is the DAX formula I’ve created thus far and an example table.

=CALCULATE(SUM([Daily Count]),DATESINPERIOD(Table1[Date],LASTDATE(Table1[Date]),-7,DAY),ALLEXCEPT(Table1,Table1[Sales Location],Table1[Group]))/5

Where I’m struggling is being able to come up with a way to exclude weekends and company observed holidays. Additionally, if a holiday falls on a weekday I would like to remove that from the average and go back an additional day (to smooth the trend). For example, on 11/26/18 (the Monday after Thanksgiving and Black Friday) I would like to average the five business days previous (11/26/18, 11/21-11/19, and 11/16). In the example above, the moving total and average for the previous 5 days should be Intake = 41 (total) 8.2 (average), Appointment = 30 (total) 6 (average), and Sale = 13 (total) and 2.6 (average). Based on the formula currently each of these numbers are inaccurate. Is there an easy way to exclude these days?

Side note: I’ve created an ancillary table with all holidays that is related to the sales data that I have.

Thank you for the help!