Building a Centralized Actuals vs Forecast Revenue Measure in Power BI

When building revenue dashboards in Power BI, one of the most common challenges is blending actuals and forecast data cleanly — showing actuals where they exist, falling back to forecast where they don't, and making sure a single measure works correctly across every visual type on the page.

This post walks through the full pattern: the problem, the pitfalls, and the final centralized DAX measure that solved it.

The Problem

A typical sales or operations revenue model has two separate fact tables:

• Actuals — confirmed revenue from your ERP or billing system, available only for closed months
• Forecast — planned and pipeline revenue from your planning system, covering all months

The natural instinct is to write a simple fallback:

Revenue =
VAR ActualValue  = SUM( 'Actuals'[Amount] )
VAR ForecastValue = SUM( 'Forecast'[Revenue] )
RETURN IF( NOT ISBLANK( ActualValue ), ActualValue, ForecastValue )

This works fine at the month level when you have a clean all-or-nothing split. But real data is messier — some months are partially closed, quarters mix closed and open months, and different visuals need different granularities. That simple IF breaks down fast.

The Core Insight: Split by Month, Not by Total

The fix is to stop asking "does this context have actuals?" and instead ask "which specific months have actuals, and which don't?" — then sum each group separately.

-- Months that HAVE actuals
VAR MonthsWithActuals =
    FILTER(
        VALUES( 'Date'[Month] ),
        CALCULATE( SUM( 'Actuals'[Amount] ) ) > 0
    )

-- Months that DON'T have actuals
VAR MonthsWithoutActuals =
    FILTER(
        VALUES( 'Date'[Month] ),
        ISBLANK( CALCULATE( SUM( 'Actuals'[Amount] ) ) )
            || CALCULATE( SUM( 'Actuals'[Amount] ) ) <= 0
    )

-- Actuals for closed months only
VAR ActualValue =
    CALCULATE( SUM( 'Actuals'[Amount] ), MonthsWithActuals )

-- Forecast for open months only
VAR ForecastValue =
    CALCULATE( SUM( 'Forecast'[Revenue] ), MonthsWithoutActuals )

This correctly handles Q2 where January is closed but February and March are still forecast — actuals pick up January, forecast picks up February and March, and the quarter total is the correct blend of both.

Challenge 1: Deal Stage Split in Bar Charts

A common stacked column chart breaks forecast down by deal stage — Committed, Upside, Pipeline — to show how confident the revenue mix is. But when actuals exist for a month, all three stages show the same actuals number — because actuals have no deal stage breakdown.

The solution is a calculated table that injects an "Actuals" entry into the deal stage list, then routes each stage to the right value:

Deal Stages Extended =
UNION(
    VALUES( 'Forecast'[Deal Stage] ),
    ROW( "Deal Stage", "Actuals" )
)

Create this as a calculated table (Modeling → New Table), not a measure.

Then in the measure, use SELECTEDVALUE on the extended table to detect which bar is being rendered:

VAR CurrentStage = SELECTEDVALUE( 'Deal Stages Extended'[Deal Stage] )

RETURN
    SWITCH(
        TRUE(),
        CurrentStage = "Actuals",   IF( NOT ISBLANK( ActualValue ), ActualValue, BLANK() ),
        CurrentStage <> "Actuals",  IF( NOT ISBLANK( ForecastValue ), ForecastValue, BLANK() ),
        BLANK()
    )

For closed months: only the Actuals bar renders, deal stage bars go blank. For open months: deal stage bars render from forecast, Actuals bar goes blank.

Challenge 2: Product or Channel Breakdown Chart

A second chart broke revenue down by product line or sales channel — a dimension that exists only in the forecast table, not in actuals. Actuals are posted at a higher level with no product detail.

Using the combined measure here inflated every product row because actuals (carrying no product dimension) bled across all rows unconstrained.

The fix: detect when a forecast-only dimension is on the axis using ISINSCOPE, and return pure forecast for that visual context:

VAR IsProductContext = ISINSCOPE( 'Forecast'[Product Line] )

RETURN
    IF( IsProductContext, SUM( 'Forecast'[Revenue] ), /* combined logic */ )

ISINSCOPE is more reliable than SELECTEDVALUE here because SELECTEDVALUE returns BLANK at subtotal rows or when a product has zero forecast — causing those rows to fall through to the combined branch and inflate. ISINSCOPE is purely structural: it's TRUE whenever that column is on the visual axis, regardless of what values are in scope.

Challenge 3: Drillthrough Table Row Inflation

The drillthrough detail page showed the correct total but every individual row showed the same inflated number. For example, a regional total of $883,474 was correct, but inside the drillthrough table, every deal row showed $111,731,859.

The cause: actuals exist at month granularity, but drillthrough rows are at deal or opportunity level. When the measure evaluated ActualValue per row, it pulled the full month's actuals into every single row, then the row's forecast was added on top.

The fix: detect row-level context using ISINSCOPE on the most granular column in the drillthrough table, and return only that row's individual forecast when at row level:

VAR IsDetailRowContext = ISINSCOPE( 'Forecast'[Opportunity Name] )

-- At row level: actuals don't exist at this granularity
-- Return only this row's individual forecast value
RETURN
    IF( IsDetailRowContext, ForecastValue, CombinedValue )

Subtotals and totals fall through to CombinedValue and sum correctly. Individual rows show only their own forecast contribution.

The Final Centralized Measure

Combining all three context checks into one SWITCH(TRUE(), ...) measure that powers every visual on the page:

Revenue Master =

VAR CurrentStage =
    SELECTEDVALUE( 'Deal Stages Extended'[Deal Stage] )

VAR IsProductContext =
    ISINSCOPE( 'Forecast'[Product Line] )

VAR IsDetailRowContext =
    ISINSCOPE( 'Forecast'[Opportunity Name] )

VAR MonthsWithActuals =
    FILTER(
        VALUES( 'Date'[Month] ),
        CALCULATE( SUM( 'Actuals'[Amount] ) ) > 0
    )

VAR MonthsWithoutActuals =
    FILTER(
        VALUES( 'Date'[Month] ),
        ISBLANK( CALCULATE( SUM( 'Actuals'[Amount] ) ) )
            || CALCULATE( SUM( 'Actuals'[Amount] ) ) <= 0
    )

VAR ActualValue =
    CALCULATE( SUM( 'Actuals'[Amount] ), MonthsWithActuals )

VAR ForecastValue =
    CALCULATE( SUM( 'Forecast'[Revenue] ), MonthsWithoutActuals )

VAR ForecastOnly =
    SUM( 'Forecast'[Revenue] )

VAR CombinedValue =
    ( IF( ISBLANK( ActualValue ),   0, ActualValue   ) )
  + ( IF( ISBLANK( ForecastValue ), 0, ForecastValue ) )

RETURN
    SWITCH(
        TRUE(),
        -- Product/channel chart → pure forecast, no actuals mixing
        IsProductContext,
            IF( ForecastOnly = 0, BLANK(), ForecastOnly ),

        -- Drillthrough detail rows → row-level forecast only
        IsDetailRowContext,
            IF( ISBLANK( ForecastValue ), BLANK(), ForecastValue ),

        -- Stacked bar: Actuals stage
        CurrentStage = "Actuals",
            IF( NOT ISBLANK( ActualValue ), ActualValue, BLANK() ),

        -- Stacked bar: Forecast stages (Committed, Upside, Pipeline)
        CurrentStage <> "Actuals" && NOT ISBLANK( CurrentStage ),
            IF( NOT ISBLANK( ForecastValue ), ForecastValue, BLANK() ),

        -- No stage context → donut / KPI card / combined total
        ISBLANK( CurrentStage ),
            IF( CombinedValue = 0, BLANK(), CombinedValue )
    )

Context Detection Summary

Key Takeaways

• Split by month, not total — FILTER(VALUES(...)) on the date table is the correct pattern for mixed-period blending. A simple IF(HasActuals, Actuals, Forecast) at the quarter level will double-count or miss data when only some months are closed.
• ISINSCOPE over SELECTEDVALUE for context detection — SELECTEDVALUE returns BLANK at subtotals and zero-value rows, causing fallthrough to the wrong branch. ISINSCOPE is structural and fires correctly regardless of what values are in scope.
• One master measure — SWITCH(TRUE(), ...) with context detection branches eliminates measure duplication and ensures consistent, validated data across every visual and drillthrough page.
• Actuals granularity is a hard constraint — if actuals only exist at month level, they cannot be meaningfully shown at row level. Detect row-level contexts with ISINSCOPE and isolate them explicitly, otherwise every row inflates to the same total.
• Calculated tables for slicers — measures cannot drive slicers. Any dynamic column needed in a slicer (like an extended deal stage list with an injected "Actuals" entry) must be a calculated table created via Modeling → New Table.