Power BI中如何编写DAX度量值计算指定月份的YTD累计值

Got it, let's work through how to build this DAX measure for your YTD cumulative calculation. Here's a step-by-step breakdown tailored to your specific scenario—where you need to calculate year-to-date totals up to a defined cutoff month, use actual data up to that point, and handle your FactTable's irregular update schedule.

Step 1: Grab the Cutoff Month #

First, we need to pull the target month from your single-row Cut of date table. Since it's a single-value table, SELECTEDVALUE is perfect here—it returns the only value in the End of YTD column:

VAR _CutoffMonth = SELECTEDVALUE('Cut of date'[End of YTD])

Step 2: Build the YTD Calculation #

Now we'll create the core measure, with two common scenarios depending on how you want to handle missing actual data:

Scenario 1: Sum Only Actual Data (No Forecast Fallback)

Use this if you want your YTD total to only include actual records (ignoring forecasts entirely for the cutoff period). This works great if you know all months up to the cutoff have been updated with actuals:

YTD Actual = 
VAR _CutoffMonth = SELECTEDVALUE('Cut of date'[End of YTD])
VAR _StartOfYear = DATE(YEAR(_CutoffMonth), 1, 1)
RETURN
CALCULATE(
    SUM(FactTable[Actual]),
    DATESBETWEEN(FactTable[Month], _StartOfYear, _CutoffMonth)
)

Scenario 2: Fall Back to Forecast When Actuals Are Missing

If your FactTable doesn't update on a fixed schedule (so some months up to the cutoff might not have actual data), use this measure to take actual values where available, and fill in gaps with forecast data:

YTD Actual or Forecast = 
VAR _CutoffMonth = SELECTEDVALUE('Cut of date'[End of YTD])
VAR _StartOfYear = DATE(YEAR(_CutoffMonth), 1, 1)
VAR _FilteredYTDMonths = DATESBETWEEN(FactTable[Month], _StartOfYear, _CutoffMonth)
RETURN
CALCULATE(
    SUMX(
        FactTable,
        // Use actual if it exists, else use forecast
        IF(NOT(ISBLANK(FactTable[Actual])), FactTable[Actual], FactTable[Forecast])
    ),
    _FilteredYTDMonths
)

Step 3: Adjust for Different Table Structures #

If your FactTable uses a category column (e.g., FactTable[Data Type] with values "Actual" and "Forecast") instead of separate Actual/Forecast columns, tweak the measure like this:

YTD Actual (Category Format) = 
VAR _CutoffMonth = SELECTEDVALUE('Cut of date'[End of YTD])
VAR _StartOfYear = DATE(YEAR(_CutoffMonth), 1, 1)
RETURN
CALCULATE(
    SUM(FactTable[Value]),
    DATESBETWEEN(FactTable[Month], _StartOfYear, _CutoffMonth),
    FactTable[Data Type] = "Actual"
)

This filters for only "Actual" records within the YTD window up to your cutoff month.

内容的提问来源于stack exchange，提问作者Przemyslaw Remin