12-07-2018 10:27 AM
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!
12-07-2018 11:00 AM
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:
'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?
12-07-2018 12:46 PM
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.
12-07-2018 12:58 PM
@AlB Correct, I'm looking for the moving average by location and group. What additional would I need to add in order to have the average calculated for only working days?
12-07-2018 01:21 PM
Hang on... the table that you are showing, the one with the data, does it include weekends, holidays, company holidays as well? If so, what is the value of [Daily Count] on those days? Is it a blank(), a valid value that you just want to ignore?
12-07-2018 01:40 PM
I'm getting a bit confused, sorry.
Is that then the format of the table you'll be working with? It only has entries for weekdays that are not corporate holidays?
Or is that just the case with the table that you listed?
12-07-2018 01:47 PM
Not a problem, thank you for the help so far!
So the sales table that I listed in the first example, with the screenshot, does not include weekends or holidays. Essentially, I'm trying to find a way to do a moving average from a specific date and the four prior entries for a particular group. Hopefully this helps and clarifies. Let me know if you have any other questions.
12-07-2018 02:07 PM
Ok but what I am asking about is the real table that you will use in your calculations.Let's forget about the example shown.
The "real" table has entries on weekends and holidays?