DAX for Semi-Additive Measures: Solving Opening Balance, Closing Balance, and Inventory Calculations with LASTNONBLANK and FIRSTNONBLANK

Introduction

Imagine you're building a financial dashboard for a retail chain. The CFO wants to see inventory levels by month, opening and closing cash balances by quarter, and stock-on-hand figures that roll up correctly across a product hierarchy. You build what seems like a perfectly reasonable measure, drop it onto a matrix visual, and then the subtotals arrive — and they're completely wrong. The quarterly total shows the sum of all month-end balances instead of the balance at the end of the last month. Your per-product totals add up when they should snapshot. The numbers look authoritative, and they're lying.

This is the semi-additive problem, and it breaks a surprising number of otherwise competent DAX developers. Most measures are fully additive — revenue, units sold, cost of goods — they can be summed across every dimension safely. But a class of critically important business metrics simply cannot be summed across time. You don't add up your bank balance at the end of each day to get your year-end balance. You don't sum inventory snapshots across months to answer the question "how much do we have in stock?" These measures need to aggregate differently across the time axis while still summing normally across other dimensions like product, region, or store. That asymmetry is what makes them semi-additive, and it's what makes them hard.

By the end of this lesson, you will have a genuinely deep understanding of how LASTNONBLANK, FIRSTNONBLANK, and their table-valued cousins LASTNOBLANKVALUE and FIRSTNONBLANKVALUE work — not just syntactically, but mechanically. You'll understand why the naïve approaches fail, how to build opening balance, closing balance, and inventory measures that survive drill-down, cross-filtering, and hierarchy rollups without lying to your users.

What you'll learn:

• Why semi-additive measures break standard aggregation and what the engine actually does differently with them
• How LASTNONBLANK and FIRSTNONBLANK evaluate their second argument and why that matters for correctness
• How to build closing balance, opening balance, and average balance measures that aggregate correctly across all dimension combinations
• How to handle sparse data — periods with no transactions — without returning blanks or incorrect carryforward values
• Advanced patterns: weighted average inventory, period-over-period comparisons for semi-additive measures, and performance trade-offs between competing approaches

---

Prerequisites

This lesson assumes you are comfortable with:

• DAX filter context and context transition — you should know the difference without looking it up
• CALCULATE, FILTER, ALL, ALLEXCEPT, and VALUES at an intermediate level
• Time intelligence functions: DATEADD, ENDOFMONTH, STARTOFMONTH, DATESYTD
• A working data model with a proper date table marked as a date table in Power BI
• Basic understanding of how row context and filter context interact in iterator functions

If any of those feel shaky, revisit the earlier DAX Mastery lessons on filter context and time intelligence before continuing here. The semi-additive patterns layer on top of those foundations — they don't replace them.

---

What Makes a Measure Semi-Additive?

Before we write a single line of DAX, we need to be precise about the problem. Additive measures are safe to aggregate using SUM across every dimension in your model. Revenue is additive: you can sum it across products, across regions, across time periods, and the result is always meaningful. Non-additive measures can't be meaningfully summed or averaged across any dimension — an exchange rate or a percentage margin, for instance. Semi-additive measures live in the middle: they're additive across some dimensions (typically everything except time) but not across time.

The canonical examples you'll encounter in practice:

• Account balances: A checking account's end-of-month balance. You can sum across accounts to get total balance. You cannot sum across months to get a "total" balance — you want the most recent balance.
• Inventory on hand: You can sum across SKUs or warehouses to get total inventory. You cannot sum January, February, and March inventory to get Q1 inventory — you want the snapshot at the end (or beginning) of the quarter.
• Headcount: You can sum across departments. You cannot sum monthly headcount to get annual headcount — you want the count at a point in time.
• Loan outstanding balance: Same pattern. Additive across loan accounts, not across time periods.

The moment you put any of these on a matrix visual with a time hierarchy, the standard SUM measure will give you mathematically nonsensical subtotals at the quarter and year level. The quarter total will be three times the right answer (roughly), and the year total will be twelve times the right answer (roughly). Your report will confidently display fiction.

Understanding why this happens mechanically is important. When Power BI evaluates a subtotal row — say, the Q1 2024 row in a matrix — the filter context expands to include all dates in Q1 rather than being restricted to a single month. A plain SUM or LASTDATE approach aggregates differently at that wider context, and unless you explicitly handle the time aggregation, you'll get the wrong result.

---

The Data Model We'll Use

Throughout this lesson, we'll work with a realistic scenario: a retail company tracking inventory snapshots and a separate financial model tracking account balances. Our data model has:

• DimDate: A standard date table with columns Date, Year, MonthNumber, MonthName, Quarter, YearMonth. It is marked as a date table.
• FactInventorySnapshot: One row per product per day (for active products). Columns: Date, ProductKey, WarehouseKey, UnitsOnHand, CostPerUnit.
• FactAccountBalance: One row per account per month-end date. Columns: Date, AccountKey, Balance, AccountType.
• DimProduct, DimWarehouse, DimAccount: Standard dimension tables.

The key thing to notice about FactAccountBalance is that it has rows only for month-end dates — it's a snapshot table. FactInventorySnapshot has daily rows but only for days when inventory changed (sparse). Both scenarios represent real-world challenges you'll encounter.

---

Why LASTDATE and MAX(Date) Fail You

The most common naïve approach to closing balance is something like this:

Closing Balance (Wrong) =
CALCULATE(
    SUM(FactAccountBalance[Balance]),
    LASTDATE(DimDate[Date])
)

Run this on a single month in a row context and it seems to work perfectly. Drill into a quarter and it falls apart. The reason is instructive.

LASTDATE returns a table containing the single last date in the current filter context. When you're looking at March 2024, the filter context contains all dates in March, and LASTDATE correctly identifies March 31. But LASTDATE doesn't evaluate the data — it evaluates the date dimension. If March 31 happens to be in your date table but has no corresponding rows in FactAccountBalance (maybe you post balances on business days only, and March 31 was a Sunday), LASTDATE will give you a date with no data, and your measure returns blank.

Now consider what happens at the Q1 level. LASTDATE returns March 31. If there's a balance record for March 31, you get the March 31 balance — which is actually correct for closing balance. But here's the subtle trap: if you're summing multiple accounts, SUM still sums all March 31 balances correctly. So LASTDATE can work for closing balance if your data is perfectly dense. The problems compound when your data is sparse.

The MAX approach has an identical issue:

Closing Balance (Also Wrong) =
CALCULATE(
    SUM(FactAccountBalance[Balance]),
    FILTER(
        ALL(DimDate),
        DimDate[Date] = MAX(DimDate[Date])
    )
)

This is syntactically more explicit but functionally equivalent. It picks the maximum date in the current filter context and filters to that date. Same brittleness with sparse data.

The core problem: Both LASTDATE and MAX(Date) operate on the date dimension, not on the fact table. They'll happily return a date that exists in your calendar but has no corresponding data.

---

Enter LASTNONBLANK: How It Actually Works

LASTNONBLANK is the right tool for this job, and its behavior is subtle enough that most explanations get it wrong. Let's be precise.

The syntax is:

LASTNONBLANK(<column>, <expression>)

The function iterates over the values in <column> (in the current filter context), evaluates <expression> for each value, and returns the last value of <column> for which <expression> is not blank and not zero.

That second point — "not zero" — is important and often overlooked. If your expression returns 0 for a given date, LASTNONBLANK will skip it. This can cause unexpected behavior when legitimate zero balances exist. We'll handle this later.

Here's what happens mechanically during iteration:

1. LASTNONBLANK receives the current filter context from wherever it's called.
2. It iterates over the column values (dates, in our case) in descending order, checking the expression for each.
3. For each date, it performs a context transition — it evaluates the expression with a row context for that date, which then transitions into a filter context.
4. The first date (scanning from the end) for which the expression returns a non-blank, non-zero value is what gets returned.
5. The returned value is a single-row table containing that date.

This is why LASTNONBLANK is fundamentally different from LASTDATE. LASTDATE ignores your data. LASTNONBLANK uses your data to determine which date is the "last" one that actually has something.

Let's build our first correct closing balance:

Closing Balance =
CALCULATE(
    SUM(FactAccountBalance[Balance]),
    LASTNONBLANK(
        DimDate[Date],
        CALCULATE(SUM(FactAccountBalance[Balance]))
    )
)

Walk through what this does:

The inner CALCULATE(SUM(FactAccountBalance[Balance])) is the expression being evaluated for each date. As LASTNONBLANK iterates over dates from last to first, it evaluates this expression in a filter context restricted to each individual date. It scans backward through the dates until it finds one where that sum is not blank (meaning: a date for which records actually exist in the fact table). It returns that date as a single-row table. The outer CALCULATE then uses that table as a filter, restricting the evaluation to only that date, and sums the balances.

This works correctly whether you're looking at a single month, a quarter, a year, or the grand total. The function finds the last date with data, regardless of what level of the hierarchy you're at.

Performance note: LASTNONBLANK iterates over every date value in the current filter context, evaluating the expression for each. Over large date ranges, this can be expensive. We'll discuss optimization strategies later in this lesson.

---

Building FIRSTNONBLANK for Opening Balance

Opening balance follows the mirror pattern. In accounting, the opening balance for a period is the closing balance of the prior period. There are two approaches: derive it from the prior period's closing balance, or query for the first date with data in the current period.

The direct approach using FIRSTNONBLANK:

Opening Balance (Direct) =
CALCULATE(
    SUM(FactAccountBalance[Balance]),
    FIRSTNONBLANK(
        DimDate[Date],
        CALCULATE(SUM(FactAccountBalance[Balance]))
    )
)

This gives you the balance as of the first date with data in the current filter context. For a monthly view, that's the first day of the month that has a balance record — which is the opening balance for that month.

But there's a subtlety here that trips people up. In accounting, the "opening balance" for March is the same as the "closing balance" for February — it's a single moment in time viewed from two perspectives. The direct FIRSTNONBLANK approach gives you the first data point within the current period, which may or may not equal the prior period's close if your data has gaps.

The more robust approach for true opening balance is to calculate the closing balance of the prior period:

Opening Balance =
CALCULATE(
    [Closing Balance],
    DATEADD(DimDate[Date], -1, MONTH)
)

This shifts the filter context back one month and computes the closing balance there. This is semantically correct: the opening balance for any period is whatever the closing balance was at the end of the prior period. It also handles the case where you have no data at the start of a period correctly — it looks at what was true at the end of last period.

Which approach should you use? Use the DATEADD approach for financial balances where opening equals prior period's closing. Use the direct FIRSTNONBLANK approach for inventory or operational measures where you want to know the actual first recorded state within the period.

---

Inventory on Hand: Handling the Cross-Dimensional Case

Inventory calculations are where semi-additive measures get genuinely tricky, because inventory should sum across products and warehouses but snapshot across time. This asymmetry requires careful design.

A naive inventory measure:

Inventory on Hand (Wrong) =
SUM(FactInventorySnapshot[UnitsOnHand])

At a daily grain with a date filter, this is fine. But in a monthly view, it sums every day's inventory for every product across the entire month — a wildly inflated number.

The correct closing inventory:

Inventory on Hand =
CALCULATE(
    SUM(FactInventorySnapshot[UnitsOnHand]),
    LASTNONBLANK(
        DimDate[Date],
        CALCULATE(SUM(FactInventorySnapshot[UnitsOnHand]))
    )
)

Now run this in a matrix with Products on rows and Months on columns. For each product-month combination, it correctly finds the last day within that month that has inventory data for that specific product, and returns the units on hand as of that day. At the product subtotal level, it finds the last day with any inventory data and sums across all products at that date — which is correct behavior for a total inventory snapshot.

Let's verify the cross-filter behavior. When your matrix drills to "All Products / Q1 2024," the filter context includes all products and all dates in Q1. LASTNONBLANK scans from March 31 backward, checking whether any product has inventory data on each date. It finds the last date with data (say, March 31), then the outer CALCULATE sums all products' inventory on March 31. This is exactly right — Q1 closing inventory is total inventory at end of Q1.

Handling Sparse Inventory Data

Real inventory systems don't always log a row every day for every product. They might only log a row when inventory changes. This creates a sparse fact table where "no row" means "same as yesterday," not "zero inventory."

The LASTNONBLANK pattern handles sparsity naturally — it scans backward until it finds a date with actual data. But this can create semantically wrong results if you're not careful. Consider a product where the last recorded inventory update was three months ago. If today's filter context includes recent months, LASTNONBLANK will scan all the way back to that three-month-old record and return it as the "current" inventory.

Whether that's correct depends on your business rules. If no update means "unchanged," that's the right answer. If no update means "we stopped tracking this product" and inventory should be zero, you need additional logic.

Inventory on Hand (with Cutoff) =
VAR LastInventoryDate =
    LASTNONBLANK(
        DimDate[Date],
        CALCULATE(SUM(FactInventorySnapshot[UnitsOnHand]))
    )
VAR DaysSinceUpdate =
    DATEDIFF(LastInventoryDate, TODAY(), DAY)
RETURN
    IF(
        DaysSinceUpdate > 90,
        BLANK(),
        CALCULATE(
            SUM(FactInventorySnapshot[UnitsOnHand]),
            LastInventoryDate
        )
    )

This pattern introduces a business rule: if the last update was more than 90 days ago, treat inventory as unknown rather than carrying forward a stale figure. Adjust the threshold to match your operational reality.

---

The Zero Value Problem and How to Fix It

Earlier I mentioned that LASTNONBLANK skips zero values. This is arguably a design flaw in the function, and it creates real problems for inventory scenarios where zero is a legitimate value (a product that has been sold out).

Consider a product that sells its last unit on January 15. On January 15, inventory drops to zero. LASTNONBLANK scanning backward through February will skip January 15 (because the inventory sum is zero) and land on January 14 (the last day with a non-zero value), reporting inventory of 1. That's wrong — the product is out of stock.

The fix is to reframe the expression inside LASTNONBLANK to return something non-blank and non-zero even for legitimate zero values:

Inventory on Hand (Zero-Safe) =
VAR LastDate =
    LASTNONBLANK(
        DimDate[Date],
        CALCULATE(COUNTROWS(FactInventorySnapshot))
    )
RETURN
    CALCULATE(
        SUM(FactInventorySnapshot[UnitsOnHand]),
        LastDate
    )

Instead of summing UnitsOnHand in the inner expression (which returns 0 for out-of-stock products), we count rows. If any row exists for that date in that product/context, COUNTROWS returns a positive number, so LASTNONBLANK doesn't skip it. Then the outer CALCULATE sums the actual UnitsOnHand — correctly returning zero for out-of-stock products.

This is the production-grade pattern you should use whenever your data can legitimately contain zero values.

Rule of thumb: In LASTNONBLANK and FIRSTNONBLANK, the inner expression should be something that is non-blank and non-zero whenever data exists, regardless of what the actual value is. COUNTROWS is almost always the right choice for the inner expression.

---

LASTNONBLANKVALUE and FIRSTNONBLANKVALUE

DAX introduced LASTNONBLANKVALUE and FIRSTNONBLANKVALUE as more ergonomic versions of the pattern above. The syntax is:

LASTNONBLANKVALUE(<column>, <expression>)
FIRSTNONBLANKVALUE(<column>, <expression>)

These functions differ from their counterparts in one important way: they return the value of the expression at the last/first non-blank date, rather than returning the date itself. This often lets you write more compact measures.

Compare:

-- Traditional pattern (two steps)
Closing Balance v1 =
CALCULATE(
    SUM(FactAccountBalance[Balance]),
    LASTNONBLANK(
        DimDate[Date],
        CALCULATE(SUM(FactAccountBalance[Balance]))
    )
)

-- Using LASTNONBLANKVALUE (one step)
Closing Balance v2 =
LASTNONBLANKVALUE(
    DimDate[Date],
    SUM(FactAccountBalance[Balance])
)

Both produce the same result in most cases. LASTNONBLANKVALUE internally finds the last date for which the expression is non-blank and returns the expression's value at that date — essentially combining the two-step pattern into one function call.

However, there are cases where the two-step pattern is necessary or preferable:

1. When you need the date itself for further calculations (e.g., computing days since last activity)
2. When you need to apply additional filters on top of the last-date filter
3. When debugging — the explicit two-step pattern makes it easier to inspect intermediate values using variables

The zero-value problem exists in LASTNONBLANKVALUE as well. Use COUNTROWS as the expression when zeros are possible:

Inventory on Hand (LNBV, Zero-Safe) =
VAR LastDate =
    LASTNONBLANK(
        DimDate[Date],
        CALCULATE(COUNTROWS(FactInventorySnapshot))
    )
RETURN
    LASTNONBLANKVALUE(
        DimDate[Date],
        IF(
            DimDate[Date] <= LastDate,
            SUM(FactInventorySnapshot[UnitsOnHand]),
            BLANK()
        )
    )

Actually, the cleanest production approach is usually still the explicit two-step:

Inventory on Hand (Production) =
VAR LastDateWithData =
    LASTNONBLANK(
        DimDate[Date],
        CALCULATE(COUNTROWS(FactInventorySnapshot))
    )
RETURN
    CALCULATE(
        SUM(FactInventorySnapshot[UnitsOnHand]),
        LastDateWithData
    )

Clear, debuggable, handles zeros correctly.

---

Average Balance: The Third Semi-Additive Pattern

Opening and closing balance get the most attention, but average balance is equally important in banking (average daily balance for interest calculation) and inventory management (average inventory for turn calculations). Average balance has a different character — it's not a snapshot but an arithmetic mean across time.

The key insight is that average balance is additive across non-time dimensions. You can average account balances across days and then sum across accounts to get total average balance. The challenge is computing the average correctly at each level of the time hierarchy.

Average Daily Balance =
AVERAGEX(
    VALUES(DimDate[Date]),
    CALCULATE(SUM(FactAccountBalance[Balance]))
)

This iterates over each date in the current filter context and computes the sum of balances on that date, then averages those daily sums. At the month level, you get the average of daily balances for that month. At the quarter level, you get the average of daily balances across the entire quarter. This is mathematically correct because AVERAGEX is computing a true average across all days, not averaging the monthly averages.

Important distinction: If you averaged the monthly average balances to get a quarterly average, you'd get the wrong answer whenever months have different numbers of days (which they always do). The AVERAGEX over dates approach correctly weights each day equally, which is what regulators and auditors expect.

For sparse data where some dates have no records (and you want to carry forward the last known balance rather than exclude those dates):

Average Daily Balance (Sparse) =
AVERAGEX(
    VALUES(DimDate[Date]),
    CALCULATE(
        LASTNONBLANKVALUE(
            DimDate[Date],
            SUM(FactAccountBalance[Balance])
        ),
        FILTER(
            ALL(DimDate),
            DimDate[Date] <= EARLIER(DimDate[Date])  -- Note: this pattern requires careful scoping
        )
    )
)

This gets complex quickly. In practice, the cleaner approach for sparse data is to preprocess in your ETL/data engineering layer — forward-fill balances so every date in the fact table has a record. This moves the complexity to where it's easier to handle and makes your DAX much simpler.

---

Period-Over-Period Comparisons for Semi-Additive Measures

A common requirement is "closing balance this month vs. closing balance last month." Standard time intelligence functions like SAMEPERIODLASTYEAR and DATEADD work here, but you need to compose them correctly with your semi-additive measure.

Wrong approach:

Closing Balance LM (Wrong) =
CALCULATE(
    SUM(FactAccountBalance[Balance]),
    DATEADD(DimDate[Date], -1, MONTH)
)

This shifts the date filter back one month but then sums balances over that shifted period rather than taking the closing balance.

Correct approach:

Closing Balance Prior Month =
CALCULATE(
    [Closing Balance],
    DATEADD(DimDate[Date], -1, MONTH)
)

By calling [Closing Balance] (which already contains the LASTNONBLANK logic) inside a CALCULATE with DATEADD, you first shift the filter context to the prior month, then the closing balance measure correctly finds the last date with data within that shifted context. This is measure composition — your semi-additive measure handles the time dimension correctly regardless of what filter context it's evaluated in.

Closing Balance MoM Change =
[Closing Balance] - [Closing Balance Prior Month]

Closing Balance MoM % Change =
DIVIDE(
    [Closing Balance] - [Closing Balance Prior Month],
    [Closing Balance Prior Month]
)

Year-to-date closing balance is a slightly different concept. For financial balances, "YTD closing balance" is just the closing balance at the end of the year-to-date period — it doesn't sum monthly closes:

Closing Balance YTD =
CALCULATE(
    [Closing Balance],
    DATESYTD(DimDate[Date])
)

Because [Closing Balance] uses LASTNONBLANK, this correctly returns the closing balance as of the last date with data in the year-to-date window, regardless of how many months that encompasses.

---

Performance Deep Dive: Optimization Strategies

LASTNONBLANK is not free. It iterates over the dates in the current filter context, and for each date it evaluates the inner expression — which itself triggers a storage engine query. In the worst case, you're looking at N storage engine queries where N is the number of dates in your filter context. For a 5-year date range with daily granularity, that's up to 1,825 evaluations.

In practice, the VertiPaq engine is smarter than this. The inner expression evaluation is often batched, and the storage engine can short-circuit once it finds the first non-blank working backward. But you should still understand the performance characteristics and mitigation strategies.

Strategy 1: Minimize the Date Range

The most effective optimization is to ensure your filter context doesn't include more dates than necessary. If your report is showing monthly data, don't let LASTNONBLANK iterate over 1,825 daily dates looking for the last one — filter your date table to month-end dates or use a separate "month-end date" table.

-- Consider a calculated column in DimDate:
Is Month End = DimDate[Date] = EOMONTH(DimDate[Date], 0)

Then base your closing balance measure on a filtered version of the date table:

Closing Balance (Optimized) =
CALCULATE(
    SUM(FactAccountBalance[Balance]),
    LASTNONBLANK(
        CALCULATETABLE(
            VALUES(DimDate[Date]),
            DimDate[Is Month End] = TRUE()
        ),
        CALCULATE(SUM(FactAccountBalance[Balance]))
    )
)

This reduces the iteration to month-end dates only, cutting the date scan from ~1,825 iterations to ~60 for a 5-year model.

Strategy 2: Use a Snapshot Table with Month-End Grain

If your source data already has month-end snapshots (as is common for account balances), don't store daily granularity at all. A FactAccountBalance with one row per account per month-end date is far more efficient for LASTNONBLANK than a daily table.

Strategy 3: Pre-Calculate in the Data Model

For very large models, consider computing the closing balance in the data transformation layer (Power Query, SQL, dbt) and storing it as a pre-aggregated fact. Your DAX then just sums a pre-computed column rather than scanning backward through history. You lose flexibility (you can't drill to arbitrary date ranges) but gain significant query performance.

Strategy 4: Understand When LASTNONBLANK Fires

LASTNONBLANK re-evaluates for every cell in your visual. A 12-month × 100-product matrix fires the measure 1,200 times (plus subtotals). Combined with the internal iteration, this can create noticeable slowness. Use DAX Studio with Server Timings enabled to profile your measures before and after optimization.

---

Advanced Pattern: Weighted Average Inventory Cost

A genuinely complex semi-additive scenario: computing weighted average inventory cost. You need to take inventory units (semi-additive — snapshot at period end) and cost per unit (non-additive — requires weighting) and combine them correctly.

Inventory Value (Period End) =
VAR LastDateWithData =
    LASTNONBLANK(
        DimDate[Date],
        CALCULATE(COUNTROWS(FactInventorySnapshot))
    )
RETURN
    CALCULATE(
        SUMX(
            FactInventorySnapshot,
            FactInventorySnapshot[UnitsOnHand] * FactInventorySnapshot[CostPerUnit]
        ),
        LastDateWithData
    )

Weighted Average Cost per Unit =
DIVIDE(
    [Inventory Value (Period End)],
    [Inventory on Hand (Production)]
)

The pattern here is to always resolve the "last date with data" first, then apply that date filter to any aggregate you need to compute. This keeps the semi-additive time logic in one place (LastDateWithData) and makes subsequent calculations straightforward.

---

When Standard Time Intelligence Breaks: Non-Standard Calendars

Many businesses use fiscal calendars that don't align with the Gregorian calendar — 4-4-5 retail calendars, fiscal years starting in July, 13-period calendars. DAX's built-in time intelligence functions assume a standard calendar and break on non-standard ones.

For LASTNONBLANK and FIRSTNONBLANK, the good news is that they don't depend on the standard calendar at all — they operate purely on the dates that exist in your filter context. As long as your custom calendar table is set up correctly and your visuals filter by your custom period columns (fiscal month, fiscal quarter, etc.), the semi-additive patterns work identically.

What you need to be careful about is the DATEADD-based prior period pattern. On a fiscal calendar, you can't use DATEADD(..., -1, MONTH) to get the prior fiscal period because fiscal periods don't correspond to calendar months. Instead, you need custom time intelligence based on your calendar table:

Closing Balance Prior Fiscal Period =
VAR CurrentFiscalPeriod = SELECTEDVALUE(DimDate[FiscalPeriod])
VAR PriorFiscalPeriodDates =
    CALCULATETABLE(
        VALUES(DimDate[Date]),
        DimDate[FiscalPeriod] = CurrentFiscalPeriod - 1
    )
RETURN
    CALCULATE(
        [Closing Balance],
        PriorFiscalPeriodDates
    )

This replaces the built-in time intelligence with a custom period lookup, but the core [Closing Balance] measure (using LASTNONBLANK) remains unchanged. Composability is the reward for writing your semi-additive measures correctly.

---

Hands-On Exercise

Set up the following in Power BI Desktop. You'll need the data model described earlier (or a similar one you have access to).

Exercise 1: Diagnose the Broken Measure

1. Create a measure Balance Sum = SUM(FactAccountBalance[Balance]) and drop it onto a matrix with Month on rows and AccountType on columns. Add a total row.
2. Verify that the quarterly totals are incorrectly summing monthly balances rather than showing period-end balances.
3. Use DAX Studio to check what filter context is in effect at the quarterly total cell. You should see all dates within the quarter in the filter, not just the last date.

Exercise 2: Build Closing Balance with LASTNONBLANK

Write the [Closing Balance] measure using the zero-safe COUNTROWS pattern described above. Verify:

• Monthly values match the last day of the month's balance
• Quarterly values match the last month of the quarter's closing balance (not the sum of monthly closes)
• The grand total matches the overall last date's total balance
• Products with zero balance at period end return 0, not BLANK

Exercise 3: Opening Balance Composition

Write an [Opening Balance] measure using the DATEADD approach. Verify that for every month, [Opening Balance] equals the previous month's [Closing Balance]. Test at the quarterly level — Q2 opening balance should equal Q1 closing balance.

Exercise 4: Month-over-Month Change

Write [Balance MoM Change] and [Balance MoM % Change] measures. Add them to a line chart showing monthly trends. Verify the values are correct for the first month in your dataset (prior month closing balance should be BLANK, so the change should also be BLANK, not an error).

Exercise 5: Performance Profiling

Using DAX Studio with Server Timings enabled:

1. Run your [Closing Balance] measure on a matrix with 3 years of monthly data and 50 products.
2. Note the storage engine (SE) and formula engine (FE) query times.
3. Implement the month-end date optimization from the performance section.
4. Compare the timings. How much did restricting the date iteration to month-end dates improve performance?

---

Common Mistakes & Troubleshooting

Mistake 1: Using LASTDATE Instead of LASTNONBLANK

Symptom: Measure returns BLANK for months/quarters where the last calendar date has no data (e.g., December 31 is a Saturday with no transactions).

Fix: Replace LASTDATE with LASTNONBLANK(..., CALCULATE(COUNTROWS(YourFact))).

Mistake 2: LASTNONBLANK Returning the Wrong Date Across Products

Symptom: A product that has no inventory in a given month shows a value carried forward from a previous month, while you expected BLANK.

Root cause: LASTNONBLANK scans backward through all dates in the current filter context. If the product has historical data outside the displayed period, it may find data from before the report's date range.

Fix: Add a boundary condition. The inner expression should only return non-blank for dates within the current period:

Inventory on Hand (Bounded) =
VAR PeriodStart = MIN(DimDate[Date])
VAR LastDateWithData =
    LASTNONBLANK(
        DimDate[Date],
        CALCULATE(COUNTROWS(FactInventorySnapshot))
    )
VAR LastDateIsInPeriod = LastDateWithData >= PeriodStart
RETURN
    IF(
        LastDateIsInPeriod,
        CALCULATE(
            SUM(FactInventorySnapshot[UnitsOnHand]),
            LastDateWithData
        ),
        BLANK()
    )

Mistake 3: Forgetting That LASTNONBLANK Skips Zeros

Symptom: Out-of-stock products show their last non-zero inventory value instead of zero.

Fix: Use COUNTROWS in the inner expression, as demonstrated throughout this lesson.

Mistake 4: Composing Semi-Additive Measures with Additive Aggregation

Symptom: You create a calculated column or a measure that sums [Closing Balance] across a dimension — the numbers are wildly inflated.

Root cause: SUM-ing a semi-additive measure is the original sin this whole lesson is about.

Fix: Semi-additive measures should always aggregate via CALCULATE([Semi-Additive Measure], <new filter>), never via SUMX(table, [Semi-Additive Measure]). If you need totals across a dimension, make sure the measure itself handles the aggregation correctly at the total level.

Mistake 5: Measure Not Behaving Correctly in DirectQuery Mode

Symptom: LASTNONBLANK returns unexpected results or is extremely slow in DirectQuery models.

Root cause: LASTNONBLANK is a formula engine operation that can't be pushed to the source database. It requires materializing the date column values and making individual queries for each. In DirectQuery, this can mean hundreds of round trips to the database.

Fix: In DirectQuery scenarios, consider using TOPN with CALCULATE instead:

Closing Balance (DirectQuery Friendly) =
CALCULATE(
    SUM(FactAccountBalance[Balance]),
    TOPN(
        1,
        SUMMARIZE(
            FactAccountBalance,
            DimDate[Date]
        ),
        DimDate[Date],
        DESC
    )
)

This generates a single SQL query with TOP 1 ORDER BY Date DESC, which the source database can execute efficiently using an index.

Mistake 6: LASTNONBLANK in a Measure Used as a Slicer or Filter Target

Symptom: Using a semi-additive measure as a visual-level filter ("show only months where closing balance > $1M") produces incorrect filtering behavior.

Root cause: When Power BI evaluates whether each row passes a visual-level filter, it uses the row's filter context, which may not contain the full time range you expect.

Fix: Semi-additive measures are designed for display values, not for filter predicates. Use calculated columns or pre-computed fields for filter targets, or restructure the requirement so the filter operates on the fact table directly.

---

Summary & Next Steps

Semi-additive measures exist at the intersection of two things DAX handles with great sophistication: arbitrary filter context manipulation and time intelligence. The key concepts from this lesson:

LASTNONBLANK and FIRSTNONBLANK evaluate their expression against the data, not just the date dimension. This is what makes them correct where LASTDATE and MAX fail — they account for sparsity in your fact table rather than assuming every calendar date has data.

The zero-value problem is real and consistent: Always use COUNTROWS (or another always-positive expression) inside LASTNONBLANK/FIRSTNONBLANK when your data can legitimately contain zero values. This affects inventory, headcount, and any scenario where "zero" is a valid state rather than an absence.

Opening balance is best derived as prior period's closing balance using CALCULATE([Closing Balance], DATEADD(...)). This compositional approach gives you correct semantics at every level of the time hierarchy automatically.

Performance scales with date range and date granularity. Restrict your LASTNONBLANK iterations to the appropriate date grain (month-end dates for monthly reporting, week-end dates for weekly reporting) and you can dramatically reduce query times.

DirectQuery requires a different approach. The formula-engine-only nature of LASTNONBLANK makes it inappropriate for DirectQuery; use TOPN-based patterns or pre-aggregate in the source system.

Where to Go Next

From here, the natural progressions are:

• DAX for Running Totals and Cumulative Measures: Many of the patterns here (especially the sparse data handling) transfer directly to running total scenarios.
• Advanced Time Intelligence with Custom Calendars: Build a complete 4-4-5 fiscal calendar implementation and port all your semi-additive measures to it.
• DAX Performance Optimization with VertiPaq Analyzer: Profile and optimize your semi-additive measures using column statistics, cardinality analysis, and materialization analysis.
• Composite Models and DirectQuery Optimization: Deep dive into making complex DAX work efficiently against direct database connections, including the architectural trade-offs between import, DirectQuery, and hybrid approaches.

The semi-additive problem is one of those areas where the difference between a good DAX developer and a great one is visible in the work. Anyone can write a measure that looks right in isolation. Writing measures that remain correct when sliced, filtered, drilled, and cross-filtered — at scale, in production — requires the kind of understanding you now have.