Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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!
@Anonymous
Do you want the moving average per [Sales Location] and [Group]? Cause I see you have the ALLEXCEPT for those two columns
@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?
Thanks!
@Anonymous
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?
@AlB The table that I have listed up there only includes working days (everything but weekends and corporate holidays).
@Anonymous
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?
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.
@Anonymous
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?
@Anonymous
Ok so I got it all wrong from the beginning then.
But if it's so, what is the problem?? you just take the last five dates present in the table from the date in the current row. You do not have to skip anything then.
I guess your right. How would I go about doing that within DAX? Is there an expression I'm missing here, or just a total mental block?
Thanks!
That's an interesting approach, but you have to be careful generalizing it. Indexing the workdays is a good idea, but I'd recommend doing the indexing on your dedicated date table rather than trying to deal with ranking duplicate dates in your fact table.
Another approach would be to use the TOPN function as I suggested here.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |