Power BI
Advanced 16 min read Power BI

DAX Patterns and Calculations

Named recipes you can assemble instead of reinventing

DAX has recurring recipes: running totals, moving averages, semi-additive balances, top-N, and dynamic groupings. Master ten patterns and most business questions become assembly, not invention.

DAX patternsrunning totalmoving averagesemi-additivetop Ndynamic groupingRANKX
Prerequisites: DAX and the Measure Context
Before we start

What you will learn

  • Recognize when a problem matches a known DAX pattern
  • Implement running totals, moving averages and semi-additive balances
  • Use RANKX and dynamic groups safely
Introduction

What is this?

A DAX pattern is a tested recipe for a recurring calculation: running total, moving average, period-over-period, top-N, semi-additive inventory, dynamic segmentation, and a dozen more. Pattern books exist precisely because these formulas are subtle — plus the recipe solves the edge cases (first row, blank window, totals) you would otherwise hit live.

Motivation

Why do you need it?

  • Business analysts ask the same seven questions everywhere; patterns are the reusable answers.
  • Edge cases (blank months, double totals) are already handled inside a known pattern.
  • Patterns are easy to review, so code reviews and audits go faster.
  • Using canonical pattern code on GitHub lowers the risk of subtle context bugs.
Think about it

Mental model

Imagine

Standard library functions in a programming language

Think of

DAX patterns

You wouldn't hand-write a sort key in most contexts — you call the pattern. Running totals, YTDs and top-N are the sort-key equivalents of DAX.

Mechanics

How it works

  • A running total opens a window from current row to the start of the visible dates.
  • A moving average uses DATESINPERIOD with a range like -7 DAY centered or trailing.
  • A semi-additive measure (inventory balance) takes the last non-blank value within the period instead of summing.
  • Top-N uses RANKX to order, then keeps the wanted slice by filter or by visual filter.
  • Dynamic groups bucket a measure into ranges using SWITCH with intervals.

Chaining pattern building blocks.

Raw sum
window
modifier
Measure result
Hands-on

Examples

Four pattern skeletons

Four pattern skeletons
Revenue = SUM(Orders[Amount])
 
Running Total =
  CALCULATE( [Revenue],
    FILTER( ALL('Date'[Date]),
            'Date'[Date] <= MAX('Date'[Date]) ) )
 
Moving 30d Avg =
  CALCULATE( AVERAGEX( VALUES('Date'[Date]), [Revenue] ),
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -30, DAY) )
 
Inv Balance =
  CALCULATE( SUM(Inventory[qty]),
    LASTDATE('Date'[Date]) )

The running total pins FILTER ALL to 'up to the visible max date'; the moving average walks the trailing 30 days; the inventory balance takes the last dated quantity, turning a non-additive sum into a period-end snapshot.

Real world

In production

  • A finance model shows a running cashflow with a blank-safe first row, authored straight from the canonical pattern.
  • A warehouse team measures 28-day moving fulfillment using DATESINPERIOD, surviving leap edges.
  • A retail squad hides the long tail with a Top 10 + 'Others' RANKX pattern on a bar chart.
Guidelines

Best practices

  • Keep a shared library file of your 10 most-used patterns.
  • Test a pattern at the total row and at the first row of data before shipping.
  • Name measures after the pattern ('Running Total Cash', not 'RT_X').
  • Version patterns tied to an underlying fact with comments describing assumptions.
Watch out

Common mistakes

  • Writing a 'running total' that restarts at every row because the filter wasn't fixed to top of the range.
  • Using semi-additive sums on a balance, tripling inventory across months.
  • Hand-coding date windows instead of using DATESINPERIOD, breaking at month boundaries.
  • RANKX on a percentage column directly, producing a meaningless ranking of per-row ratios.
Root cause analysis

Common issues

Error
Running total resets at month boundaries
Root cause

The total filter binds inside the month rather than whole range

Fix

Use a RANKX/MIN date window at the start of the visible range, not the month.

Error
Moving average ignores the first days
Root cause

Window shorter than the offset at the series start

Fix

Accept partial windows at edges or pivot the window with a full prior range.

Error
Inventory balance sums across months
Root cause

A sum over a semi-additive fact

Fix

Use LASTNONBLANK or the semi-additive pattern keyed to each period end.

When things break

Debugging tips

  • Test a pattern against a 6-row table you can compute by hand first.
  • Compare the running-total last row against the plain SUM of the whole range.
  • Use card visuals to isolate single measures during pattern work.
  • Check a moving average's edge rows (first 3 rows) for partial windows by design.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which pattern is right for a month-end inventory balance?

2

A moving 30-day average should include all historical data in its first window.

Go deeper

Official documentation

DAX Patterns
docs
RANKX function reference
docs