Posts

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

Image
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.   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 Dat...
Post a Comment
Read more

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

Image
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 Imp...
Post a Comment
Read more

[Power BI] DAX ALL Function Practical Series ③ Accumulation Part 1: Basic Running Total

Image
In our last post, we theoretically explored how the ALL function releases time filters to set the stage for accumulation analysis. Today, we move into the practical application: implementing a Running Total using real-world IT device sales data and interpreting it through the lens of business strategy. If your professional dashboard only shows monthly sales charts, it’s like checking only the "instantaneous speed" of a car. A Running Total, however, is the "odometer" that shows how far you have traveled toward your destination. Especially in organizations with annual quotas, accumulation data is more than just a performance report—it is the most powerful basis for deciding whether to commit additional resources in the second half of the year.   1. Practical Example Data Structure We will design basic measures based on the provided July to December data. Base Measure: Total Sales = SUM(Sales[Sales]) Total Volume: $3,259,000 (Total for the second half)   2. Why ...
Post a Comment
Read more

[Power BI] DAX ALL Function Practical Series ③ Accumulation Analysis Series Guide

Image
In our previous post, we explored how the ALL function restores the comparison set for ranking analysis. Today, we begin the [Power BI Accumulation Analysis Series], often referred to as the "highlight" of practical data analysis. Beyond simply adding numbers, this is a strategic analytical method for interpreting how far we have run toward our target (Run-rate) by controlling the flow of time. When building dashboards in the field, you will often find that a single accumulation chart tells a much more compelling story than simple monthly sales figures. In this post, we will first outline the overall structure and core principles of the upcoming Power BI Accumulation Analysis series.   1. The Essence of Accumulation Analysis: Why the ALL Function? 1.1 Accumulation is the act of reviving 'hidden time'  The moment you click on "March" in a Power BI report, the data for January and February disappears from the screen due to filters. However, since a Running T...
Post a Comment
Read more

[Power BI] DAX ALL Function Practical Series ② Ranking Part 5: Rank Change Analysis

Image
If you mastered Cross Ranking—which weaves together the contexts of stores and products—in Part 4, it is now time to add the flow of time. "Why did a product that was 5th last month rise to 2nd this month?" The direction and speed of change are often more important than the static position of the data. In this Part 5, we will conquer the core DAX logic for calculating month-over-month rank fluctuations and implementing an arrow-based dashboard. Power BI Ranking Analysis Series This series consists of five practical analysis parts: Part 1 — Global Ranking (RANKX + ALL)  Part 2 — Top-N & Others (Pareto) Part 3 — Relative Rank Index   Part 4 — Cross Ranking (Store × Product)  Part 5 — Rank Change Analysis  -- Current Post   1. Analysis Data Overview: H1 2024 IT Device Sales The data we are analyzing consists of monthly sales records for 5 major product lines across 4 stores.   2. Why is Rank Change Analysis Important? Strategically, "who is climbing up"...
Post a Comment
Read more