Hardcoded dates in a forecast-vs-actuals report are a landmine with a delay timer. The report works perfectly in every demo, ships to production, and then breaks โ€” not with an error, but silently, with a stale cutoff โ€” the first month nobody happens to remember to update the filter buried in a measure. If a report needs a human to update a date literal for it to keep working, it isn't finished.

The core problem

Forecast-vs-actuals reporting has a structural quirk that plain actuals reporting doesn't: on any given day, part of the current period is real (actuals that already happened) and part of it is a projection (forecast that hasn't happened yet). The report needs to know, at query time, exactly where that boundary sits โ€” and that boundary moves every single day, or every time data refreshes. Hardcoding it as a DAX literal or a fixed slicer default means the report is only accurate on the day you wrote the measure.

Finding the boundary dynamically

The boundary isn't "today" in the calendar sense โ€” it's the last date for which actual data has actually landed in the model, which usually lags a day or more behind the calendar due to source system refresh timing. I resolve it directly from the fact table, not from TODAY():

Last Actual Date :=
CALCULATE(
    MAX('Fact Actuals'[Date]),
    ALL('Date')
)

Wrapping it in CALCULATE( ... , ALL('Date') ) matters โ€” without removing the existing filter context from the date table, this measure would just return the max date within whatever period the user already filtered to, which defeats the purpose. This needs to always resolve to the true global boundary regardless of what's on the report canvas.

If actuals and forecast live in a single fact table with a Scenario column rather than two separate tables, the same idea works filtered by scenario:

Last Actual Date :=
CALCULATE(
    MAX('Fact'[Date]),
    ALL('Date'),
    'Fact'[Scenario] = "Actual"
)

Splitting actuals and forecast by that boundary

Once the boundary exists as a measure, every period in the report can classify itself against it without a single hardcoded date anywhere else in the model:

Total Actuals :=
CALCULATE(
    SUM('Fact'[Amount]),
    'Fact'[Scenario] = "Actual"
)

Total Forecast :=
CALCULATE(
    SUM('Fact'[Amount]),
    'Fact'[Scenario] = "Forecast"
)

Actual or Forecast :=
VAR CutoffDate = [Last Actual Date]
VAR CurrentDate = MAX('Date'[Date])
RETURN
    IF(
        ISBLANK(CurrentDate),
        BLANK(),
        IF(
            CurrentDate <= CutoffDate,
            [Total Actuals],
            [Total Forecast]
        )
    )

That last measure is the one that actually goes on a trend chart โ€” it silently switches source depending on whether the date on the axis is in the past (use actuals) or future (use forecast), with the switch point recalculating itself every time the model refreshes. Nobody touches a filter to make that happen.

The blended current-period problem

The harder case, and the one that trips up a lot of "dynamic" forecast reports that only handle the trend line correctly, is a single KPI card for "this month" or "this quarter" where part of the period is actual and part is forecast. A naive SUM over the current month either double-counts (if both scenarios have rows for the same days) or undercounts (if it only pulls actuals and silently ignores the rest of the month). The fix is a blended measure that sums actuals for the days that have happened and forecast for the days that haven't, within the same period:

Blended Total :=
VAR CutoffDate = [Last Actual Date]
RETURN
    CALCULATE(
        SUM('Fact'[Amount]),
        'Fact'[Scenario] = "Actual",
        'Fact'[Date] <= CutoffDate
    )
    +
    CALCULATE(
        SUM('Fact'[Amount]),
        'Fact'[Scenario] = "Forecast",
        'Fact'[Date] > CutoffDate
    )

This is the measure that actually answers "what will this month land at," combining what's already happened with what's still projected โ€” and it's the number executives usually mean when they ask for "this month's number" mid-month, even if they don't phrase the question that way.

Handling mixed-period comparisons

Forecast-vs-actuals almost never stays inside a single calendar convention. Finance frequently wants fiscal year, operations wants calendar month, and forecasts are sometimes loaded at a coarser grain (monthly) than actuals (daily). A few practices keep this from turning into a mess of special-case measures:

  • Build one Date dimension marked as the model's official date table, with both fiscal and calendar attributes as columns on it, so every measure can reference the same table regardless of which calendar convention a given report uses.
  • If forecast is loaded monthly and actuals daily, don't force forecast rows down to a fake daily grain. Let the fact tables sit at their natural grain and roll both up to month in the visual โ€” comparing at a coarser grain the source data can't actually support daily precision on isn't a compromise, it's the correct grain for a mid-run forecast anyway.
  • Use TOTALYTD with the fiscal year-end parameter explicitly set when the business runs on a non-calendar fiscal year, rather than assuming a December year-end:
Actuals YTD :=
TOTALYTD(
    [Total Actuals],
    'Date'[Date],
    "6/30"
)

That third argument is the one people forget, and it's exactly the kind of hardcoded assumption this whole approach is trying to eliminate elsewhere โ€” so if the fiscal year-end ever changes, it changes in one place, ideally driven from a parameter table rather than a literal string buried in a measure.

The test I use before calling a forecast-vs-actuals report finished: open it a year from now, with no one touching a single measure, filter, or parameter, and it still draws the actual/forecast line in the right place. If that requires someone remembering to update anything, it isn't dynamic โ€” it's just correct today.

None of this is exotic DAX. It's a handful of measures that all defer to one source of truth for "where is the boundary today" instead of each independently guessing. That's the entire trick โ€” and it's the difference between a report that needs annual maintenance and one that doesn't.