Introduction
In data analysis, the moving average is a fundamental concept used to smooth out short-term fluctuations and highlight longer-term trends or cycles. However, traditional moving averages do not account for non-operational days like weekends and holidays, which can skew the results, especially in business contexts where such days have no data points or significantly different trends. In this article, we will guide you through creating an accurate moving average that excludes weekends and holidays using Power BI.
Why Exclude Weekends and Holidays?
For businesses that operate mainly on workdays, including weekends and holidays in the moving average calculation can distort the analysis. These periods often have no activity (zero values) or unusual trends (holiday sales spikes). By excluding them, the moving average becomes more reflective of the business's operational reality, providing clearer insights and better decision-making support.
Setting Up Your Data in Power BI
Creating a Calendar Table
Your journey begins with setting up a calendar table that encompasses all the dates you might need in your analysis. This table should also indicate whether a date is a workday. To include this functionality, add an IsWorkday
column with a value of 1 for workdays and 0 otherwise.
You can generate or import a calendar table in Power BI. Ensure it spans enough to cover all dates in your primary data table (Table1
), then add the IsWorkday
column. This might involve manually marking holidays or utilizing a country-specific holidays function/package if available.
The Concept
To calculate a moving average that excludes weekends and holidays, we rely on a measure in Power BI. This measure dynamically selects the top 5 most recent workdays up to the current date in Table1
and calculates the average over these days.
The Measure
Moving Avg =
VAR Last5Workdays =
SELECTCOLUMNS (
TOPN (
5,
FILTER (
DateTable,
DateTable[Date] <= EARLIER ( Table1[Date] )
&& DateTable[IsWorkday] = 1
),
DateTable[Date], DESC
),
"Workday", DateTable[Date]
)
RETURN
CALCULATE (
SUM ( Table1[Daily Count] ),
Table1[Date] IN Last5Workdays
ALLEXCEPT ( Table1, Table1[Sales Location], Table1[Group] ),
)
/ 5
This measure works as follows:
- The
TOPN
function selects the top 5 dates from the DateTable
that are workdays (IsWorkday = 1
) and are on or before the current date in Table1
. It orders these days in descending order to get the most recent dates.
SELECTCOLUMNS
is used to convert this filtered table into a list of dates (named Workday
).
- The
CALCULATE
function then computes the sum of Daily Count
from Table1
for those specific dates, adjusting for any additional dimensions like Sales Location
or Group
.
- Finally, it divides by 5 to get the average
Daily Count
for the last 5 workdays, excluding weekends and holidays.
Get a Free AI Website Audit
Automatically identify UX and content issues affecting your conversion rates with Flowpoint's comprehensive AI-driven website audit.
Real-World Application
Imagine you are analyzing sales data for a retail company. Traditional moving average might show dips in sales every weekend, making it harder to discern overall trends. By applying the illustrated measure, you get a smoother curve that accurately reflects sales performance over operational days, allowing for more actionable insights.
Conclusion
Power BI provides robust tools to refine data analysis, and understanding how to manipulate data for specific business contexts can significantly enhance your insights. Excluding weekends and holidays for moving average calculations is just one example of fine-tuning data analysis to reflect the operational reality better. Remember that clear, accurate data analysis is key to informed decision-making.
To further fine-tune your web analytics and identify all technical errors that are impacting conversion rates on your website, visit Flowpoint.ai. Flowpoint can directly generate recommendations to fix them, leveraging its core features like behavior analytics, AI-generated recommendations, and more, putting precise, actionable data at your fingertips.