Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

SO Ex.JPG

 

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 14
AlB
Super User
Super User

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?

Anonymous
Not applicable

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

Anonymous
Not applicable

@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?  

Anonymous
Not applicable

@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? 

Anonymous
Not applicable

@AlB

 

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
Not applicable

@AlB

 

The table I will use for calculations does not contain weekends or 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.

Anonymous
Not applicable

@AlB

 

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!

Hi @Anonymous

Have a look at this for a nice explanation on how to do it.

Cheers

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.