Forum Discussion
Moving average excluding weekends and holidays
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!
14 Replies
- AlB
Community Champion
Hi Anonymous
How about creating an alternative Date table that contains only the dates over which you want to calculate? Then you always will be taking the last five days in your filter (in DATESINPERIOD() for instance). This is probably not the most elegant way to do it but let's see if it's useful. I'm explaining it conceptually, without all the details:
1. You know the dates that you do not want (weekends, holidays, company holidays...). So you can filter a normal 'Date' table and eliminate those. Create another table, something like:
DateCompany= FILTER('Date';
'Date'[Date] <> Holiday && 'Date'[Date] <> Weekend && etc.)
2. You use DateCompany as Date table with a relationship to your Sales table as usual
3. If you need a full 'Date' table for calculations elsewhere too, you could have both related to Sales. One would be inactive though but you'd just activate it when needed through USERELATIONSHIP.
What do you think?
Hi AlB,
Thank youf for the help so far! So I've created the table for our corporate calendar. How would I now, based on the a formula within DAX, be able to create that rolling average based on the new corporate calendar I've created? Essentially, how do I get to the point where I can filter to where only the days from my sales table that appear on the corporate calendar will be included in the moving average. Please let me know what other info you need.
Thank!
- AlB
Anonymous
Do you want the moving average per [Sales Location] and [Group]? Cause I see you have the ALLEXCEPT for those two columns
