Power BI中自定义DAX调用CALENDARAUTO返回空表问题咨询

在Power BI Desktop中，参考sqlbi.com的CALENDARAUTO示例操作时发现：单独执行Table of Dates = CALENDARAUTO()可正常生成日期表，但自行编写的包含多表日期筛选逻辑的DAX代码（无语法报错）却返回空表。已在多个pbix文件中通过「新建表」按钮测试，问题代码如下：

Dates (CALENDARAUTO) =
    VAR _FirstDate_CustomerBirth = MIN ( 'Customer'[Birth Date] )
    VAR _FirstDate_ProductAvailable = MIN ( 'Product'[Available Date] )
    VAR _FirstDate_SalesDelivery = MIN ( 'Sales'[Delivery Date] )
    VAR _FirstDate_SalesOrder = MIN ( 'Sales'[Order Date] )
    VAR _FirstDate =
    IF ( _FirstDate_SalesOrder < _FirstDate_SalesDelivery, _FirstDate_SalesOrder, _FirstDate_SalesDelivery )
    VAR _DateTable =
        FILTER (
            CALENDARAUTO(),
            YEAR ( [Date] ) >= _FirstDate -- 'CALENDARAUTO'.[Date]
        )
    RETURN
        ADDCOLUMNS(
            _DateTable,
            "Year", YEAR ( [Date] ),
            "Month", FORMAT ( [Date], "mmm" ),
            "Month Number", MONTH ( [Date] ),
            "Quarter", FORMAT ( [Date], "\QQ yyyy" )
        )

问题根源 #

代码核心错误出在FILTER函数的条件判断：

• YEAR([Date])返回的是年份数字（例如2020），而_FirstDate是完整的日期值（例如2020/01/01）
• DAX比较不同类型的值时，会将日期转换为对应的序列号（2020/01/01对应序列号43831），年份数字远小于该序列号，导致筛选条件永远不成立，最终返回空表。

修复方案 #

方案1：按年份筛选 #

提取_FirstDate的年份，与日期表中日期的年份进行比较：

Dates (CALENDARAUTO) =
    VAR _FirstDate_CustomerBirth = MIN ( 'Customer'[Birth Date] )
    VAR _FirstDate_ProductAvailable = MIN ( 'Product'[Available Date] )
    VAR _FirstDate_SalesDelivery = MIN ( 'Sales'[Delivery Date] )
    VAR _FirstDate_SalesOrder = MIN ( 'Sales'[Order Date] )
    VAR _FirstDate =
        IF ( _FirstDate_SalesOrder < _FirstDate_SalesDelivery, _FirstDate_SalesOrder, _FirstDate_SalesDelivery )
    VAR _FirstYear = YEAR(_FirstDate)
    VAR _DateTable =
        FILTER (
            CALENDARAUTO(),
            YEAR ( [Date] ) >= _FirstYear
        )
    RETURN
        ADDCOLUMNS(
            _DateTable,
            "Year", YEAR ( [Date] ),
            "Month", FORMAT ( [Date], "mmm" ),
            "Month Number", MONTH ( [Date] ),
            "Quarter", FORMAT ( [Date], "\QQ yyyy" )
        )

方案2：按完整日期筛选（更精准） #

如果需要从_FirstDate的具体日期开始生成日期表，直接比较完整日期值：

Dates (CALENDARAUTO) =
    VAR _FirstDate_CustomerBirth = MIN ( 'Customer'[Birth Date] )
    VAR _FirstDate_ProductAvailable = MIN ( 'Product'[Available Date] )
    VAR _FirstDate_SalesDelivery = MIN ( 'Sales'[Delivery Date] )
    VAR _FirstDate_SalesOrder = MIN ( 'Sales'[Order Date] )
    VAR _FirstDate =
        IF ( _FirstDate_SalesOrder < _FirstDate_SalesDelivery, _FirstDate_SalesOrder, _FirstDate_SalesDelivery )
    VAR _DateTable =
        FILTER (
            CALENDARAUTO(),
            [Date] >= _FirstDate
        )
    RETURN
        ADDCOLUMNS(
            _DateTable,
            "Year", YEAR ( [Date] ),
            "Month", FORMAT ( [Date], "mmm" ),
            "Month Number", MONTH ( [Date] ),
            "Quarter", FORMAT ( [Date], "\QQ yyyy" )
        )

额外优化：简化最小日期计算 #

可以用MINX直接取所有日期中的最小值，替代原有的IF判断逻辑：

VAR _FirstDate = MINX(
    {_FirstDate_CustomerBirth, _FirstDate_ProductAvailable, _FirstDate_SalesDelivery, _FirstDate_SalesOrder},
    [Value]
)

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