Forum Discussion
Moving average excluding weekends and holidays
Community ChampionHi 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
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!
- AlB
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?