5 DAX Patterns Every Financial Analyst Should Know

DAX (Data Analysis Expressions) is the formula language that powers Power BI. While basic SUM and AVERAGE functions can get you started, mastering advanced DAX patterns is what separates a dashboard that shows numbers from one that drives financial decisions.

After building dashboards with 20+ DAX measures for real-world projects, here are the 5 patterns I use most frequently in financial analysis.

1 Year-over-Year (YoY) Growth

This is the single most requested metric in any financial dashboard. Stakeholders want to know: "How are we doing compared to last year?"

Revenue YoY % = 

VAR CurrentRevenue = [Total Revenue]

VAR PriorYearRevenue = 

    CALCULATE(

        [Total Revenue],

        SAMEPERIODLASTYEAR('Date'[Date])

    )

RETURN

    IF(

        NOT ISBLANK(PriorYearRevenue),

        DIVIDE(CurrentRevenue - PriorYearRevenue, PriorYearRevenue),

        BLANK()

    )

Key insight: Always wrap DIVIDE() instead of using the "/" operator. DIVIDE() handles division-by-zero gracefully and returns BLANK() instead of an error — critical when prior year data doesn't exist.

Why this matters: SAMEPERIODLASTYEAR is a time intelligence function that automatically shifts the date context back by exactly one year. It respects your date table relationships, so it works correctly with any slicer combination (quarter, month, week).

Real-world use

In my Uttarakhand Employment Dashboard, I used this pattern to show how district-level employment rates changed year-over-year, enabling policy makers to identify districts with declining workforce participation.

2 Running Total (Cumulative Sum)

Running totals are essential for tracking cumulative revenue, expenses, or any metric that needs to accumulate over time.

Cumulative Revenue = 

CALCULATE(

    [Total Revenue],

    FILTER(

        ALL('Date'),

        'Date'[Date] <= MAX('Date'[Date])

    )

)

How it works:

ALL('Date') removes any existing date filters, giving access to all dates in the table.
The FILTER then re-applies a filter: only keep dates up to the current context's maximum date.
The result: revenue accumulates from the start of your data up to whatever date the user is viewing.

Pair this with a line chart and you get the classic "revenue curve" that executives love — smooth, always going up (hopefully), and immediately shows if you're on track for your annual target.

3 Dynamic Segmentation

This pattern lets users create segments on the fly without modifying the data model. For example: "Show me customers who spent more than X amount" where X is controlled by a slicer.

-- Step 1: Create a "What-If" parameter table

Threshold = GENERATESERIES(1000, 100000, 5000)

-- Step 2: Create the segmentation measure

High Value Customers = 

VAR SelectedThreshold = SELECTEDVALUE(Threshold[Threshold])

RETURN

    COUNTROWS(

        FILTER(

            Customers,

            [Total Spend] >= SelectedThreshold

        )

    )

Power move: Combine this with conditional formatting. If the high-value customer count drops below a threshold, the card turns red. Now your dashboard doesn't just show data — it raises alerts.

4 Profit Margin with Conditional Logic

Real financial data is messy. You need DAX measures that handle edge cases: negative margins, missing cost data, partial periods.

Profit Margin % = 

VAR Revenue = [Total Revenue]

VAR Cost = [Total Cost]

VAR Profit = Revenue - Cost

RETURN

    SWITCH(

        TRUE(),

        ISBLANK(Revenue), BLANK(),

        Revenue = 0, BLANK(),

        Profit < 0, 

            FORMAT(DIVIDE(Profit, Revenue), "0.0%"),

        DIVIDE(Profit, Revenue)

    )

Why SWITCH(TRUE()) instead of nested IF:

It reads top-to-bottom like a checklist — much easier to debug
Each condition is evaluated in order; the first TRUE wins
Adding new conditions later is trivial (just add another line)

5 Moving Average (Trend Smoothing)

Raw daily or weekly data is noisy. A 30-day moving average smooths out volatility and reveals the true trend — essential for financial KPIs.

30-Day Moving Avg Revenue = 

AVERAGEX(

    DATESINPERIOD(

        'Date'[Date],

        MAX('Date'[Date]),

        -30,

        DAY

    ),

    [Daily Revenue]

)

How it works: DATESINPERIOD generates a table of dates spanning 30 days back from the current context. AVERAGEX then iterates over each of those dates and computes the average of [Daily Revenue].

This is incredibly powerful when overlaid on a line chart alongside the raw daily values. The moving average line shows the trend; the daily values show the volatility around it.

Variations: Replace -30/DAY with -12/MONTH for a 12-month rolling average (ideal for annual reports), or -4/QUARTER for quarterly smoothing. You can also use AVERAGEX with LASTDATE instead of MAX for slightly different behavior at period boundaries.

Putting It All Together

These 5 patterns — YoY Growth, Running Total, Dynamic Segmentation, Conditional Margins, and Moving Averages — form the backbone of every financial dashboard I build. Master these, and you can handle 80% of what stakeholders will ever ask for.

The remaining 20% usually involves combinations of these patterns or more specialized calculations like cohort analysis, allocation ratios, and budget variance. But those all build on the same core principles: context modification with CALCULATE, time intelligence, and iterators like AVERAGEX and SUMX.

The best way to learn? Pick a financial dataset, build a dashboard, and force yourself to answer 10 business questions using only DAX. You'll internalize these patterns faster than any tutorial.

Power BI DAX Financial Analysis Dashboards Data Modeling KPIs