[Power BI] DAX ALL Function Practical Series ④ Accumulation Part 4: Moving Average & Smoothing

How to Build a Moving Average in Power BI Using DAX: Rolling Average & Trend Analysis Guide

In our previous post, we looked at the speed gap compared to the past through Prior-Year Cumulative analysis (Part 3). In this Part 4, we will cover the Moving Average, a technique that filters out sudden spikes in data to reveal the true underlying health of your business.

In business reporting, weekly sales often fluctuate wildly due to events or promotions. If you only look at the figures for a single week, it’s easy to fall into short-term judgments like "We failed this week" or "We hit the jackpot." What we really need is the true trend—stripping away the "data fog" or noise. However, Power BI's default filters try to show only the data for the current row. Here, the ALL function becomes the key to breaking down those filter walls and reconnecting past data to the present.

Step 1: Anchor current position , step 2: dissolve filter walls, step 3: select moving window

"In this tutorial, you will learn how to build a Moving Average in Power BI using the DAX ALL function to smooth out noise and identify true business trends."

1. Practical Example Data

Sales in the second half of 2024 experience two major waves: the New Product Launch (September) and Black Friday (November).

  • Core Segment: 11/18 to 11/25 ($182K to $130K)
  • Analysis Task: We need to distinguish whether this decline is a collapse of the trend or a natural "soft landing" following the end of an event.
practical example data : 2024 week sales

2. Why 'Moving Average'?

A simple bar chart shows "what happened," but a moving average shows "where we are actually going."

1. Noise Removal (Smoothing): Refines spikes caused by temporary promotions or inventory issues.

2. Trend Identification: Confirms the overall direction rather than short-term fluctuations.

3. Decision Support: Distinguishes between "a dip we can endure" and "a decline we must react to."

3. How to Build a Moving Average in Power BI Using DAX

While standard average functions are trapped in the "prison" of the current row, the moving average process breaks those walls to pull in past data for calculation. For this, we use the ALL + FILTER + AVERAGEX structure.

1) Logic Flow

A moving average isn't just simple division; it’s a process of reconstructing data by moving a "Time Window." Especially in practical data where dates might be discontinuous, it is more stable to calculate based on a Week Index rather than a Date.

How to Build a Moving Average in Power BI Using DAX : logic step by step


1. STEP 1. Identify Current Week: As the calculator passes through each row of the table, it needs to know "Where am I standing right now?" We set a reference point by capturing the current row's Week Index into a variable using the MAX function.

2. STEP 2. Break the Filter Wall: Power BI basically shows only the data for the 'current row.' To see past data, we must break this wall. We use the ALL function to release the trapped data and link it to the past.

3. STEP 3. Sliding Window: Once you've pulled the full dataset, you need a 'slicing' step to cut only what you need. Through FILTER, we slice a segment where the Week Index is less than or equal to the Current Index and greater than Current Index - 5. This segment "moves" along with every row.

4. STEP 4. Calculate & Smooth: We combine the 5 sliced data points to find the average. AVERAGEX iterates through the sliced segment to find the average sales. In this process, short-term surges or drops (noise) are neutralized, creating a smooth trend line.

2) 5-Week Moving Average

To use this logic, your data model (Date table) must have a Week Index column that lists each week in order (1, 2, 3...).

5-Week Moving Average

5-Week Moving Average : using dax in power bi

Analyst's Tips: Here are some additional points to consider regarding "Handling Empty Weeks":

Here are some additional points to consider regarding "Handling Empty Weeks"

  • AVERAGEX vs. DIVIDE: AVERAGEX only includes rows with data in the calculation. If one week out of a 5-week segment has no data (not zero, but missing entirely), AVERAGEX will divide by 4. If you need to treat empty weeks as zero and divide by 5, you should consider a SUMX / 5 approach.
  • Performance Optimization (ALLSELECTED): If a user filters the report for a specific period (e.g., H2 2025) and you want the moving average to work only within that filter, using ALLSELECTED instead of ALL might be more appropriate for the business logic.
  • Data Modeling: Rather than calculating directly within the Sales table, create a separate Date table (Calendar table), assign a Week Index, and establish a relationship. This is the 'Best Practice' for maintaining Power BI performance and scalability.

4. The True Trend Revealed by Data

A moving average line complements the context of a trend that is hard to capture with bar charts alone. Since a moving average is a lagging indicator that follows past averages, comparing whether the current data (bar) is above or below the average (moving average line) becomes a powerful decision-making tool. The real beginning of analysis is reading the 'flow' rather than being buried in the 'instant' fluctuations.

moving average saes 'bar chart' in power bi visualization

Beyond the simplistic view of whether sales went up or down compared to the previous week, analyze four core segment to see what business insights emerge when the ALL function reconnects the hidden data.

Moving Average vs Actual — Practical Interpretation Framework


1. Lunch Spike (W36–W37):

  • Observation: Actual drops sharply (143K to 85K) while the MA rises gradually.
  • Insight: Actual is significantly higher than the MA. Even though performance is dropping, it remains above the moving average.
  • Conclusion: Successful Onboarding. This wasn't just a one-off event; the business has entered a higher Success Orbit.

2. False Decline (W38–W40):

  • Observation: Actual declines (56K to 40K) while the MA continues to rise.
  • Insight: Actual is greater than MA, and the MA is rising. As low past values are replaced by higher recent sales, the average strength is improving.
  • Conclusion: Class Upgrade. This isn't a decline, but a New Normal formation phase. The fundamental strength of the business has leveled up.

3. Equilibrium Convergence (W41–W44):

  • Observation: Actual moves in a range (55K to 31K) while the MA gradually declines.
  • Insight: Actual is approximately equal to the MA. Due to the lagging nature of the MA, the impact of the prior spike is being removed, and the average is re-aligning to realistic levels.
  • Conclusion: Stabilization. This is not a collapse in demand, but a convergence toward Equilibrium. The average becomes realistic as data noise is removed.

4. Structural Level-Up (W45–W48):

  • Observation: Post-event pullback (182K to 130K) while the MA rises sharply.
  • Insight: Actual is greater than MA, and the MA is accelerating upward. Even though the bar dropped, it remains above the MA. Upward momentum is lifting the new baseline.
  • Conclusion: Healthy Pullback. This is a normal adjustment within an upward trend, not a trend collapse. It’s a segment where aggressive operations are still viable.

Final Advice for Analysts (Tips for Overcoming Lag)

When looking at a moving average line, always check first: "Is the bar above or below the line?"

1. Bar > Moving Average: Strong vs. average (Upward momentum intact)

2. Bar < Moving Average: Weak vs. average (Downward pressure emerging)

3. Moving Average Rising: Improving baseline performance

4. Moving Average Falling: Structural weakening trend

As long as the bar stays above the moving average, it’s a "growth acceleration" phase. The moment it sinks below, you should switch to "risk management" mode. Use the lagging nature of the moving average as an "objective scale" to judge the present.

Part 4 Core Summary

In this chart, the ALL function doesn't just make the numbers look smooth; it acts as a decision-making guide to distinguish whether "this current drop is an endurable one or one that requires a countermeasure."

1. W48: A drop you can endure (MA is still rising)

2. W50: A decline that requires action (MA is dropping alongside it)

If an executive looks at this chart and asks, "So, what do you think our final score will be by the end of the year?" how should you answer? The answer to that question is the theme of our next Part 5: Accumulation Forecasting. Shall we move on to the prediction models?

[Next Preview]

In the next Part 5 — Accumulation Forecasting, we will look at simulation techniques that predict "Based on this pace, what will the final year-end grade be?" using the slope of cumulative data.

[Cumulative Analysis Series Index]



<Other posts on the blog>




Comments

Post a Comment

Popular posts from this blog

DAX CALENDAR Function Deep Dive and Practical Usage Guide

Image
The CALENDAR function is essential for creating a date table in a data model, and it plays a vital role when performing time intelligence calculations. Through this document, you'll be able to get a detailed understanding of the CALENDAR function's syntax, how it operates, and how you can leverage it in real-world data analysis scenarios.   1. What Exactly is the CALENDAR Function? The CALENDAR function in DAX is basically your go-to tool for building a dedicated date table based on a specific date range. All you do is give it a start date and an end date, and it spits out a single-column table containing every single date within that range. This generated table is what we commonly call a 'Date Dimension Table. ' It's absolutely critical for your data model because you'll use it to set up relationships with other tables (like sales, orders, or inventory) and, more importantly, it becomes the foundation for all your time-based analysis.     1.1. Syntax ...
Read more

Standard Deviation (Part 2): Strategic Limitations and Complementary Perspectives in Standard Deviation Analysis

Image
As we've seen, standard deviation is more than just measuring 'how spread out the data is' it quantifies that spread to evaluate the stability and risk of business performance, thus serving as a key managerial analysis metric that supports strategic decision-making. However, relying solely on standard deviation for interpretation can easily lead to misjudgment. When using standard deviation for corporate, sales/profit analysis, or forecasting, the following additional factors must be considered: If you missed 'Standard Deviation' Previous contents, Please check out the below link. Standard Deviation The Complete Guide to the Core of Business Data Analysis Standard Deviation (Part 1): Measuring Data Volatility and Using the Insights for Better Strategy   1. Considering the Relationship with the Mean (Expected Value) Standard deviation represents only the absolute degree of data 'spread.' Therefore, to grasp the relative meaning of this figure, it must be cons...
Read more

Standard Deviation (Part 1): Measuring Data Volatility and Using the Insights for Better Strategy

Image
Standard Deviation is a core metric that quantifies the 'dispersion' of your data, showing—as a single number—how tightly clustered or widely spread the data points are from the mean. A larger number means the data is widely spread out, while a smaller number means it's tightly grouped. Let's explore how this concept is used strategically in analyzing sales and profits. If you missed 'Standard Deviation' first contents, Please check out the below link. Standard Deviation The Complete Guide to the Core of Business Data Analysis A nd you can also refer next contents, after reading this. Standard Deviation (Part 2): Strategic Limitations and Complementary Perspectives in Standard Deviation Analysis   1. Why We Must Read the 'Wobble' (The Trap of the Mean) It's natural to first look at average sales or profit when analyzing a company's performance. The average is the central value of the data, making it the most fundamental and useful metric for gr...
Read more