基于DAX计算截至当年最大可用日期的年初至今(YTD)值

模型结构 #

事实表（sales） #

日期维度表（date） #

表关系 #

sales.date → date.date

现有汇总视图 #

需求 #

计算sales.quantity的年初至今(YTD)总和，仅截至当年的最大可用日期（本例中2024年最大可用日期为10月），预期结果：

• 2022: 350（1-10月总和）
• 2023: 290（1-10月总和）
• 2024: 300（1-10月总和）

要求使用时间智能函数，方便后续通过SAMEPERIODLASTYEAR创建“上年值”度量值。

DAX度量值实现 #

YTD_Quantity = 
VAR MaxAvailableDate = MAX(sales[date])
VAR MaxMonthInMaxYear = MONTH(MaxAvailableDate)
VAR CurrentYear = SELECTEDVALUE(date[year])
VAR CurrentMaxMonth = 
    IF(
        CurrentYear = YEAR(MaxAvailableDate),
        MaxMonthInMaxYear,
        12
    )
VAR FilteredDates = 
    FILTER(
        ALL(date),
        date[year] = CurrentYear && date[month] <= CurrentMaxMonth
    )
RETURN
CALCULATE(
    SUM(sales[quantity]),
    FilteredDates,
    USERELATIONSHIP(sales[date], date[date])
)

度量值说明 #

1. MaxAvailableDate：获取事实表中最新日期，确定当前数据的截止月份（本例为2024年10月）。
2. MaxMonthInMaxYear：提取最新日期对应的月份。
3. CurrentYear：获取当前上下文选中的年份。
4. CurrentMaxMonth：如果当前年份是最新数据所在年份，用最新月份作为截止；其他年份则用12月（完整年度）。
5. FilteredDates：筛选出当前年份中截止到对应月份的所有日期。
6. CALCULATE：应用筛选条件，计算符合条件的quantity总和，同时显式指定表关系确保计算正确。

验证结果 #

使用该度量值按年份汇总后，将得到预期结果：

• 2022: 350（50+30+20+10+20+50+30+50+60+30）
• 2023: 290（20+30+20+10+20+60+50+20+40+20）
• 2024: 300（30+40+30+50+60+30+20+10+10+20）

扩展：上年同期值度量值 #

基于上述YTD度量值，可快速创建上年同期值：

YTD_Quantity_LY = 
CALCULATE(
    [YTD_Quantity],
    SAMEPERIODLASTYEAR(date[date])
)

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