[Power BI] DAX ALL Function Practical Series ③ Accumulation Part 2: Selective Accumulation

In our previous post, we explored the structure of a standard Running Total that accumulates from the beginning of the year. While standard Power BI functions like TOTALYTD are convenient, they have limitations when an analyst needs to start accumulation from an "arbitrary point in time."

In this guide, we will explore how to create selective accumulation in Power BI using the DAX ALL function.

In practice, there are many more occasions where you need to track performance starting from the day a specific campaign or event began, such as:

• "What was the initial performance since the new product launch?"
• "How much revenue was accumulated specifically during the Black Friday campaign period?"

The core of this part is understanding the calculation logic that breaks the existing flow of time and rebuilds energy from a designated Baseline by utilizing the "Table Provider" role of the ALL function.

---

 

1. Practical Example: Capturing 'Cumulative Impact' at Specific Points

The purpose of this analysis is clear: to step outside fixed timeframes like years or quarters and track the pure cumulative flow of how sales condense from the moment a specific event (Event) occurs. To visualize this "launch energy" that isn't visible through simple sums, we set different launch dates for 2023 and 2024 as our respective accumulation starting points.

• 2023 Baseline: iPhone 15 Pro Launch Date (Sept 11)
• 2024 Baseline: iPhone 16 Pro Launch Date (Sept 09)

1) Practical Data Structure 

The true value of Selective Accumulation is revealed when baseline columns like "Week Index" or "Launch Date" are combined with general sales records.

① 2023 Sales Data (Focus on iPhone 15 Pro)

② 2024 Sales Data (Focus on iPhone 16 Pro)

③ d_date Table

④ d_Cumulative Baseline: 

A "Control Table" that manages the start points of your accumulation.

⑤ d_Product

2) Modeling 

In the provided model, each dimension table performs a unique role and relates to the fact tables through specific keys:

---

 

2. Practical Scenario

Simple calendar-based totals hinder fair comparisons between products. The goal of this analysis is to align products with different launch dates onto the same starting line and contrast their "Cumulative Slope" of initial success 1:1.

1) Analysis Design (Timeline Reset) 

We unify different calendar dates into a single analytical rhythm:

• iPhone 15 Pro (2023): Sept 11 (W37) Set as Baseline
• iPhone 16 Pro (2024): Sept 09 (W37) Set as Baseline

2) Practical Impact (Key Value)

• Noise Removal (Visual Focus): By excluding unnecessary data prior to the event, we focus solely on the "Launch Impact."
• Momentum Capture (Slope Analysis): The steepness of the cumulative curve allows for immediate identification of differences in initial market reaction (Launch Momentum).
• Scalability: When future models are released, simply adding one baseline data point automatically aligns all charts, making maintenance effortless.

---

 

3. Selective Accumulation DAX Design

Since the standard TOTALYTD function cannot create accumulations based on specific events (launches, etc.), you must use a CALCULATE + FILTER + Index structure in practice.

1) Logic Flow 

The key to this measure is the "Reconstruction of Filter Context."

• ① Extract Starting Point: Store the start date of the event selected from the slicer into a variable (StartDate).
• ② Identify Current Point: Determine the date corresponding to each row in the visual context (CurrentDate).
• ③ Reset Filters: Use ALL('d_date') to remove the date filter applied to the current row, expanding the calculation range.
• ④ Redefine Range: Re-apply filters to sum data only from the start date to the current point within the same year.

2) Optimized DAX for Event-Based Accumulation

Launch Cumul Sales =

VAR StartDate = -- 1. Event start date selected from slicer

    //SELECTEDVALUE('d_Cumulative Baseline'[Start Date])
    MIN('d_Cumulative Baseline'[Start Date])


VAR CurrentDate = -- 2. Current date in the visual/context
MAX('d_date'[Date])


--3. Variables for year condition

VAR StartYear = YEAR(StartDate)
VAR CurrentYear = YEAR(CurrentDate)


RETURN
IF(
    NOT ISBLANK(StartDate) &&
    CurrentDate >= StartDate &&
    CurrentYear = StartYear,

-- 4. Execute only when current date is in the same year as start date

CALCULATE(
    [Total Sales],
    FILTER(
        ALL('d_date'),
        'd_date'[Date] >= StartDate &&
        'd_date'[Date] <= CurrentDate &&
        YEAR('d_date'[Date]) = StartYear -- 5. Define cumulative range
                )
           )
        )

3) DAX Structural Interpretation and Practical Optimization Points

• ① Dynamic Control of Event Baselines: Using SELECTEDVALUE (or MIN for robustness) allows the accumulation starting point to change dynamically. Selecting 'iPhone 16 Launch' in a slicer immediately shifts the baseline to 2024-09-09.

• Field Tip: If you want it to work even when multiple events are selected, use MIN('Event Table'[Start Date]).
• ② Context Reconstruction via ALL + FILTER:
• The core of accumulation is "bringing in all data prior to the current date."
• ALL('Date'): Forcibly releases monthly/daily filters to secure the entire timeline.
• Range Restriction: Defines the cumulative range from the StartDate to the CurrentDate and ensures it stays within the same Year (StartYear).
• ③ Visual Polish: IF and BLANK Handling:
• Treating data prior to the event as BLANK() goes beyond simple aesthetics—it increases report reliability.
• Before: The chart might show a long line of 0 or previous event totals from the far left (user confusion).
• After: The graph only begins to draw at the event start point, focusing all attention on the "Launch Performance."

4) Field Know-how

① Performance Optimization (Using DATESBETWEEN)

When dealing with massive volumes of data, I recommend using a method optimized for the internal engine rather than FILTER(ALL(...)): (Use CALCULATE with the DATESBETWEEN function using StartDate and CurrentDate.)

② Inter-Event Comparison (Relative Week Analysis)

Comparing products with different launch dates on a calendar basis makes it difficult to interpret trends. To solve this, apply a Relative Time Index (Week/Day Index) to align all event starting points on the same axis.

• iPhone 15 Launch + 10 Days Accumulation
• iPhone 16 Launch + 10 Days Accumulation Comparison based on time elapsed since launch rather than calendar dates.

By adding an Index column to your event table, you can overlay different events on a single chart. This allows you to capture not just simple revenue, but the true velocity and patterns of performance success.

---

 

4. The Impact Shown by Data (2023 vs. 2024 Contrast Analysis)

Selective Accumulation aligns products with different baselines onto the same "Launch + N weeks" line, allowing for a pure comparison of success velocity.

1) Cumulative Momentum Comparison by Launch Week (Contrasting Weeks Since Launch for iPhone 15 Pro and 16 Pro through Launch Surge, Stabilization, Peak Season, and Final Close.)

Wait, what does YoY (Year over Year) mean here? It is not a simple "sales compared to the same month last year." It is a method of contrasting cumulative performance after the same amount of time has elapsed since launch. It provides a strategic answer to: "How much faster are we growing compared to last year's launch?"

Link : Part 3 Periodic Comparison: YOY analysis (coming soon)

2) What the Weekly Data Tells Us: The "Real Score"

• Overwhelming Initial Momentum (Weeks 1-2): The 44.4% YoY growth signifies that supply chain management and pre-order marketing for the iPhone 16 Pro were significantly more successful than its predecessor.
• Growth Deceleration and Crisis (Weeks 4-10): The drop from 39.5% to 28.9% suggests demand is burning out faster than the previous model, marking a "strategic inflection point" where additional promotions may be needed.
• Year-End Rebound (Week 12+): Entering Black Friday, YoY rebounds to 32.5%. The product proved its endurance by strongly absorbing year-end demand.

3) Analyst’s Insights (Weekly Context)

• Inventory and Logistics: The 44% growth during the first two weeks provides a powerful justification for setting initial stock targets at least 1.4 times higher than previous models for future releases.
• Marketing Optimization: By deploying new campaigns around Week 8—the point where the cumulative growth rate began to dip—the strategy effectively maintained momentum through to the year-end peak season.
• Conclusion: This sophisticated flow, which would have been buried under months of data in a standard YTD analysis, finally reveals the true business battlefield when viewed through the "Launch Week" microscope.

---

 

5. Wrapping up

Selective Accumulation gives analysts the "freedom to place a microscope anywhere they want." It is the only way to prove the explosive power of a short-term campaign or new product reaction otherwise hidden in the forest of total sales.

Core Summary:

• Use VAR to clearly define your analysis baseline.
• ALL opens all time filters, and FILTER captures back only the specific range you need.
• A Weekly scale is the fastest unit for capturing success signals.

In our next Part 3 — Periodic Comparison, we will explore the essence of accumulation analysis: YOY (Year-over-Year) accumulation, comparing how much steeper this year's slope is compared to last year's promotions.

Writing Tip: Try creating a 'Launch Date' variable in your report right now. The 'true battlefield' buried in total performance will reveal itself through the data.


[Series Index]
Part 1 — Basic Running Total
Part 2 — Selective Accumulation (Current Post)
Part 3 — Periodic Comparison (Coming Soon)

<Other posts on the blog>

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

Mastering BLANK in Power BI - Part 2: Advanced Analytical Patterns Using BLANK Intentionally

The Hidden Hero of Data Analysis: The Mode (Part 1) Moving Beyond the "Average Trap" to Read Your "Real Customers"

[Power BI] Two Faces of the DAX ALL Function: 5 Practical Patterns You Can Use Right Away