[Power BI] DAX ALL Function Practical Series ③ Accumulation Part 5 : Accumulation Forecasting

Simply listing past performance is no longer enough to drive executive decision-making. According to search trends frequently monitored via Google Search Console, BI professionals are increasingly seeking robust solutions for 'Practical Sales Forecasting' that go beyond simple linear trends.

In this final part of our series, we unlock the full potential of the DAX ALL function to construct a sophisticated 'Rolling Forecast' model. We will move past the pitfalls of simple averages to design a trajectory that reflects the true 'run-rate' of your business. From visualizing target gaps to establishing strategic decision ranges, discover the precise DAX patterns used by top-tier data analysts to map out the future.

In our previous Part 4, we used the Moving Average to filter out data noise and identify the "true health" of the business. Now, it is time to answer the most critical question:

"If we maintain our current pace, can we reach our year-end goal of $1.8M?"

If we cannot answer this, all analysis loses its meaning. In Part 5, we will design a DAX-based Forecasting model using a Week Index—reliable even in practical environments with sparse or irregular date data—and derive strategic insights to achieve our goals. As in previous parts, the ALL function plays a decisive role here by releasing current filters to include "future empty spaces" within the calculation range.

Decision -Driven accumulation forecasting

1. Practical Example Data

We possess performance data from July (W27) to November 25 (W48), 2024. Based on this 22-week flow, we will predict the results for the remaining weeks of December (W49–W52).

  • • Actual: W27 ~ W48 (The confirmed past)
  • • Forecast: W49 ~ W52 (The future expansion segment)

  • Practical Example Data

2. Why ‘Accumulation Forecasting’?

Simply repeating past data is not effective in practice. Accurate forecasting must follow a logical structure:

Forecast = Actual Cumulative + (Average Run-rate × Remaining Period)

This approach enables the following strategic decision-making:

  • • Goal Tracking: Pre-determining the possibility of achieving targets.
  • • Timing Optimization: Deciding precisely when to invest resources.
  • • Scenario Planning: Facilitating decision-making based on various simulated outcomes.

3. Logic Design (Forecasting Model)

While many advanced models exist, this article focuses on a "Minimum Viable Model" that can be immediately applied to business decisions.

Scenario Structure:

  • • Baseline Forecast: An average including all data (Optimistic).
  • • Conservative Forecast: An average after removing specific events (Defensive).
  • Forecast common logic (core engine)

1) Core Engine (3 Steps)

The cumulative forecasting structure is identical for both scenarios:

  1. STEP 1. Last Actual Anchor: Identify the last week where actual sales exist (W48) and finalize the cumulative total up to that point ("The Confirmed Past").
  2. STEP 2. Run Rate Calculation: Determine which data to include in the average. Should we keep or remove temporary spikes?
  3. STEP 3. Projection: For future dates, apply [Average Speed × Elapsed Duration] to create a single, continuous cumulative curve.

2) Scenario Split

Since the future segment (W49–W52) has no actual sales, values may disappear due to the filter context. The core technique is using REMOVEFILTERS to reference past averages even in future periods.

DAX Forecasting scenario breakdown

  • • ① Baseline Forecast (Total Average): Reflects the high-sales effects of Launch Spikes (W36–37) and Black Friday (W45–48). It answers: "What if this explosive momentum continues?"
  • • ② Conservative Forecast (Normal Range): Based on pure business strength, excluding abnormal peak periods (W36–38, W45–48). It represents the "Base strength after removing event bubbles."

3) Summary of Core Differences

  • • Data Range: Baseline includes all spikes (Spikes Included) / Conservative removes outliers (Outliers Removed).
  • • Average Level: Baseline is relatively high (Aggressive) / Conservative is lower (Defensive).
  • • Strategic Character: Baseline assumes growth continuation (Optimistic) / Conservative sets the risk management baseline (Conservative).
  • • Utilization Point: Baseline for aggressive marketing and goal expansion / Conservative for minimum inventory and defense mechanisms.

These two scenarios are not just about comparing numbers; they are tools for establishing a "Decision Range."

  • • Baseline shows the 'Best-case Scenario' we can reach, providing a basis for aggressive resource allocation.
  • • Conservative presents the 'Minimum Guideline' that must be defended under any circumstances.

4. DAX Design for Forecasting

When dates are discontinuous or irregular in practice, using a Week Index-based calculation is essential. This method ensures a stable forecast even in environments with missing data points.

1) Total Sales

This is the foundational aggregation measure for all calculations.

• Total Sales = SUM('Sales'[Weekly sales])

Total Sales = SUM('Sales'[Weekly sales])


2) Current Cumulative Sales (Running Total)

We add an IF condition so that the line does not extend into the future where actual data is missing.

  1. Identify the last week where actual sales exist using MAX and ALL.
  2. Get the current week index from the visual's filter context.
  3. Compute cumulative sales only if the current week is within the actual data range.
Current Cumulative Sales (Running Total)

3) Average Weekly Sales (Run-rate)

To compare the two scenarios, we create separate measures using REMOVEFILTERS and ALL.

  • • Baseline (Total Average): Calculates the average by including all data points, including spikes.
Average Weekly Sales (Run-rate)


  • • Conservative (Normal Range Average): Represents "sustainable business strength" by excluding specific outlier Week Indexes.
  • Conservative (Normal Range Average)


4) Final Forecast Accumulation: 'Rolling Forecast' Pattern

This creates a seamless curve where actuals and predictions meet. As new data is entered, the "Last Actual Week" updates automatically.

  1. Anchor: Secure the cumulative value at the last actual week (e.g., W48).
  2. Gap Calculation: Determine the number of weeks since the last actual week.
  3. Extension: Add [Average Run-rate × Weeks Gap] to the last actual cumulative value.
Forecast cumulative (Baseline)


Forecast cumulative (Conservative)

Analyst's Tip: Insights from the Field

In executive reports, the question "Is this a simple average or does it reflect recent trends?" is most common. Management trusts trend-adjusted forecasts more.

During a retail project, a simple average overestimated results by 18% post-Black Friday. Since then, I always present:

  • • Baseline Forecast (Run-rate based)
  • • Trend Adjusted Forecast (Based on Part 4's Moving Average)
  • Link : Trend Adjusted Forecast (Moving Average) - coming soon

This approach significantly increases the credibility of decision-making data.

5. Gap Analysis: Predicted Results

  • Gap Analysis - predict result


  • • Current Cumulative (W48): $1,396,000
  • • Average Run-rate: ~$63,455 ($1,396,000 ÷ 22 weeks)
  • • Year-End Forecast (Baseline): $1,396,000 + ($63,455 × 4) = $1,649,820

Reporting Insights for Executives

  1. As-Is (Status): Structural Target Shortfall. Even the optimistic Baseline scenario falls 8% short of the $1.8M goal.
  2. Implication: Execution Gap. Natural growth will not reach the target orbit.
  3. Action: Immediate Acceleration Required. Weekly sales must be pushed to $81,000 (+27%) to hit the goal.

Series Final Summary: 5-Step Analysis Completed by ALL Function

Through this 5-part journey, we have navigated the entire business analysis process using the ALL function.

  • • Part 1 (Basic): Theme: Basic / ALL's Role: Releasing total time filters / Effect: Establishing accumulation foundation.
  • • Part 2 (Selective): Theme: Selective / ALL's Role: Removing filters except specific points / Effect: Focused event analysis.
  • • Part 3 (Periodic): Theme: Periodic / ALL's Role: Recalling past time data / Effect: YoY growth speed comparison.
  • • Part 4 (Smoothing): Theme: Smoothing / ALL's Role: Supplying past n-period data / Effect: Noise removal & trend identification.
  • • Part 5 (Forecast): Theme: Forecast / ALL's Role: Processing future data segments / Effect: Goal attainment prediction.

This series was a process of mastering 'Five Powerful Patterns' for real business needs by understanding DAX's core engine, the ALL function.

  • • In Part 1~2, we broke filter barriers to see the entire flow.
  • • In Part 3~4, we compared with the past and identified true business strength by removing noise.
  • • In Part 5, we drew a map of the future by trusting the data's trajectory.

Ultimately, accumulation analysis is a sophisticated storytelling process that reads the direction and shifts of a business over time.

<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