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 is 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!