基于自定义日历的DAX历年截至上月YTD销售额计算问题求助

现有代码的核心问题 #

1. YTD1的问题：TOTALYTD函数默认遵循自然日历计算累计，完全未适配自定义公司日历的月份归属规则，导致累计周期与公司要求不符。
2. YTD SPPYs1的问题：逻辑基于自然日期的月/日过滤，而非自定义公司日历的Company Month Num。自定义日历中部分日期的归属公司月份与自然月不一致（例如自然月1号可能归到上一个公司月份），这种过滤会错误包含或排除数据。

正确实现方案 #

前提条件 #

确保Dim Date维度表包含以下核心字段：

• Company Year：日期对应的公司年份
• Company Month Num：日期对应的公司月份编号（如1-12）
• Date：与订单表关联的日期字段

1. 基础公司日历YTD度量值 #

先实现符合公司日历的当年累计到当前公司月份的销售额：

公司日历YTD销售额 = 
CALCULATE(
    [Sales],
    FILTER(
        ALL('Dim Date'),
        'Dim Date'[Company Year] = MAX('Dim Date'[Company Year]) &&
        'Dim Date'[Company Month Num] <= MAX('Dim Date'[Company Month Num])
    )
)

2. 历年截至上月的YTD度量值 #

针对当前日期（2023年10月26日），计算每一年截至对应公司上月（即公司日历9月）的累计销售额：

历年截至公司上月YTD销售额 = 
VAR 当前公司月份 = CALCULATE(MAX('Dim Date'[Company Month Num]), 'Dim Date'[Date] = TODAY())
VAR 目标公司月份 = 当前公司月份 - 1
RETURN
CALCULATE(
    [Sales],
    FILTER(
        ALL('Dim Date'),
        'Dim Date'[Company Month Num] <= 目标公司月份 &&
        'Dim Date'[Company Year] = MAX('Dim Date'[Company Year])
    )
)

特殊场景兼容（当前为1月时）

若当前是公司日历1月，需要自动切换到上一年的12月累计：

历年截至公司上月YTD销售额（兼容1月） = 
VAR 当前公司月份 = CALCULATE(MAX('Dim Date'[Company Month Num]), 'Dim Date'[Date] = TODAY())
VAR 目标公司月份 = IF(当前公司月份 = 1, 12, 当前公司月份 - 1)
VAR 目标公司年份 = IF(当前公司月份 = 1, MAX('Dim Date'[Company Year]) - 1, MAX('Dim Date'[Company Year]))
RETURN
CALCULATE(
    [Sales],
    FILTER(
        ALL('Dim Date'),
        'Dim Date'[Company Month Num] <= 目标公司月份 &&
        'Dim Date'[Company Year] = 目标公司年份
    )
)

使用说明 #

• 将品类字段拖入视觉对象的行/列区域，度量值会自动按品类分组计算。
• 确保订单表与Dim Date表通过Date字段建立正确的关系。

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