[Power BI] DAX ALL Function Practical Series ③ Accumulation Part 3: Periodic Comparison


Analyzing Growth Gaps with Cumulative YoY (Periodic Comparison)

The most frequent question that pops up in business reporting is this: "Our cumulative sales through November hit $500k... but are we actually doing better than last year?"

Simple month-over-month YoY comparisons are heavily influenced by seasonality and promotion timing. However, Cumulative YoY compares the actual growth velocity, showing the true trajectory of your business much more accurately.

In this tutorial, you will learn how to create a Cumulative YoY (Year-over-Year) growth analysis in Power BI using the DAX ALL function and week-based filtering.

Cumulative YOY growth analysis

 

1. Practical Example Data Structure


We will use the same example from Part 2: Selective Accumulation. The accumulation baseline is set to Week 27 (W27), which marks the start of the second half of the year, rather than a specific calendar date. For a detailed look at the data structure, please refer to [Link - Part 2 Selective Accumulation].

① 2023 Sales Data (Focus on iPhone 15 Pro)

2023 Sales Data (Focus on iPhone 15 Pro)

② 2024 Sales Data (Focus on iPhone 16 Pro)

2024 Sales Data (Focus on iPhone 16 Pro)

Essential Measure: Total Sales = sumx(S_2023, [Sales]) + sumx(S_2024, [Sales])


 

2. Practical Scenario: Answering "Are we doing better than last year?"


This analysis uses week-based cumulative comparisons instead of specific dates.

Comparison Criteria:
  • 2024 W27 to Current Week
  • 2023 Corresponding Week Range
Core Logic:
  • ALL: Release current filters (Expand time)
  • Week Number Filtering: Accumulate from W27 to the current week
We use a week-based approach instead of SAMEPERIODLASTYEAR to eliminate "date noise" (e.g., comparing July 1st vs. July 2nd) across different years.


 

3. Step-by-Step DAX: How to Calculate Cumulative YoY (Week-Based)


Since we are using a week-based time axis, we implement the prior-year comparison using Week Number filtering.

How to Calculate Cumulative YoY (Week-Based)


1) Current Year H2 Cumulative (CY H2 Cumulative)


This represents this year's sales accumulated from Week 27 up to the current week.

CY H2 Cumulative =

VAR CurrentWeek = MAX('d_date'[WeekNum]) -- Identify the current week in the visual context

VAR CurrentYear = MAX('d_date'[Year]) -- Identify the currently selected year

RETURN
    CALCULATE(
        [Total Sales],
            FILTER(
            ALL('d_date'), -- Remove existing date filters to         access full range
            'd_date'[Year] = CurrentYear && -- Filter for the specific current year
            'd_date'[WeekNum] >= 27 && -- Start accumulation from the beginning of H2 (Week 27)
            'd_date'[WeekNum] <= CurrentWeek -- Accumulate up to the current week's progress
            )
        )


2) Prior Year H2 Cumulative (PY H2 Cumulative)


This accumulates data for the same week interval (W27 to the current week) based on the previous year.


PY H2 Cumulative =

VAR CurrentWeek = MAX('d_date'[WeekNum]) -- Match the same week progression as the current year

VAR CurrentYear = MAX('d_date'[Year]) -- Reference year from the slicer

RETURN
    CALCULATE(
        [Total Sales],
            FILTER(
            ALL('d_date'), -- Break the filter context to retrieve prior year data

            'd_date'[Year] = CurrentYear - 1 && -- Shift the year context to the previous year
            'd_date'[WeekNum] >= 27 && -- Align the starting point with the current year's logic
            'd_date'[WeekNum] <= CurrentWeek -- Restrict data up to the same week number for a fair comparison
                )
            )


3) Cumulative Gap and Growth Rate (Cumulative YoY)


We quantify the difference between the two cumulative lines to clearly express the growth velocity.


H2 Cumulative Growth Gap = [CY H2 Cumulative] - [PY H2 Cumulative]
-- Measures the absolute difference in accumulated value between years


H2 Cumulative YoY % =

DIVIDE(
    [H2 Cumulative Growth Gap], -- Growth gap as the numerator
    [PY H2 Cumulative], -- Prior year cumulative as the baseline
    0 -- Safety handling to avoid division by zero errors
        )

Practical Detail: Handling Future Dates

To prevent the prior year’s cumulative line from stretching out into future weeks where there are no current results, we recommend using logic that returns a BLANK if the week exceeds the latest available sales data.


 Dynamic visual control to hide future dates

IF(CurrentWeek <= CALCULATE(MAX('d_date'[WeekNum]), ALL('Sales')), [CY H2 Cumulative], BLANK())


 

4. Practical Impact: Business Signals Told by the "Gap"


While simple sales are a comparison of "points," Cumulative YoY contrasts the "slope of the lines" to diagnose the growth trajectory of the business.

KPI Interpretation chear sheet

Indicator Interpretation Guide (Cheat Sheet):

  • Slope: Focuses on "Is the growth energy being maintained?" It measures growth acceleration (a steeper slope indicates a successful launch).
  • Cumulative Gap ($): Focuses on "How much more did we earn than last year?" It shows the actual profit scale and the level of 'safety margin' secured.
  • Cumulative YoY (%): Focuses on "How much faster are we compared to last year?" It judges the efficiency and momentum of the business engine.

 

5. H2 Growth Trajectory Shown by Data (4-Stage Segment Analysis)


From the start of H2 (W27) to the end of the year (W50), we track the real impact that major business events have on cumulative sales and growth velocity.

[Key Milestones] H2 Core Schedule:
  • H2 Start: W27
  • New Product Launch: W37 (Aligned with the same week last year)

1) Milestone Snapshot: Cumulative Performance and Inflection Points


We identify phenomena by listing cumulative data before and after major events without pre-defining segments.
  • Early H2 (W33): Started solid by maintaining momentum from the first half. (Cumulative YoY: 29.4%)
  • Pre-Launch (W36): Growth rate plateaued due to demand waiting for the new release. (Cumulative YoY: 29.1%)
  • Post-Launch (W39): New product effect kicked in, causing the slope to jump sharply. (Cumulative YoY: 32.4%)
  • Post-Launch Adjustment (W46): Growth plummeted to the H2 low point, dropping below the initial 29.4%. (Cumulative YoY: 29.0%)
  • Year-End (W50): Growth trend bounced back through year-end peak demand. (Cumulative YoY: 31.1%)


2) Business Insights from the Data

  • Velocity Change Around Launch (W36 to W39): Starting from the new product launch (W37), cumulative YoY rebounded sharply from 29.1% to 32.4%. This shows the new product didn't just add sales; it provided a powerful acceleration to the entire H2 growth engine.
  • Adjustment Period and Resilience (W46): At the point where the launch effect faded (W46), cumulative YoY fell to 29.0%. This is lower than the H2 starting point, meaning the advantage gained from the launch was almost entirely surrendered to the previous year's pace. This segment reveals potential risks that arise when follow-up momentum is lacking.
  • Year-End Closing Phase (W50): Cumulative YoY bounced back to 31.1% during the year-end period (W47 to W50). This indicates that the temporary slowdown was overcome by year-end demand. The 'safety margin' secured during the early launch phase successfully defended against later volatility.

3) Analyst’s Tip: "Reporting Numbers That Move Executives" 

Don't just deliver facts like "H2 cumulative sales were $3.2 million." The key is to report by giving the data 'temporal context' and 'growth elasticity.'
  • [Before]: "H2 sales closed at $3.2 million, achieving our goal with 31.1% growth over last year."
  • [After - Context-Driven]: "The growth gap, which surged to 32.4% right after launch, fell to 29.0% in November (W46)—even lower than our early H2 start (29.4%)—putting us at risk of being overtaken by last year's pace. However, through year-end demand response, we finalized an overachievement of $760k (31.1%)."

Practical Tip: Creating a Dedicated 'Gap' Measure

To show the distance between the two lines more clearly, create a specific [Gap] measure and display it as a bar chart alongside the lines. Executives can then grasp exactly how much more we are earning than last year in just one second.


 

6. Power BI Visualization Strategy: Interpreting the Growth Gap


Listing simple numbers lowers the persuasiveness of a report. The core of prior-year cumulative analysis is visually emphasizing the change in the "Gap" between the two lines.

1) Recommended Chart: Line and Clustered Column Chart 

The most recommended way is to represent cumulative amounts as 'Lines' and the difference between periods as 'Columns.'
  • Solid Line (2024): Current growth trajectory (Bright primary colors recommended).
  • Dotted Line (2023): Past benchmark (Calm neutral colors or gray recommended).
  • Bars (Gap): The calculated field of [Current Cumul] - [PY Cumul] (Apply conditional formatting: Blue for positive, Red for negative).

2) Key Layout Guide

  • Highlight Cross-over Points: If this year’s cumulative line falls below last year’s, add data labels or annotations at that point to signal a clear warning.
  • Slicer Integration: Place product category or region slicers to allow for immediate drill-down into 'growth slowdowns' occurring in specific groups.
  • Tooltip Detail: When hovering over a line, show both 'Single Week YoY' and 'Cumulative YoY' simultaneously to compare short-term volatility with long-term trends at a glance.

 

7. Wrapping up


Periodic Comparison gives data 'historical context.' It’s not just about celebrating high numbers; it’s about objectively understanding your current position by comparing it to past peak records.

Core Summary:
  • Cumulative YoY shows the real growth rate by removing short-term volatility.
  • ALL acts as the provider that breaks away from current filters to summon past data.
Reading the change in the gap between the two cumulative lines is the heart of practical analysis.

In our next Part 4 — Moving Average & Smoothing, we will take cumulative data one step further. We’ll look at moving average techniques to smooth out jagged sales graphs and find the 'true trend.'

[Cumulative Analysis Series Index]
Part 1 — Basic Running Total
Part 2 — Selective Accumulation
Part 3 — Periodic Comparison (Current Post)
Part 4 — Moving Average & Smoothing (Coming Soon)


<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