DAX中基于日历的时间智能如何改变自定义逻辑

用户576110558132
7 阅读6分钟

引言

随着基于日历的时间智能功能的出现，对自定义时间智能逻辑的需求已大幅减少。现在，我们可以创建自定义日历来满足时间智能计算需求。

您可能读过我关于高级时间智能的文章。大部分自定义逻辑已不再需要。但我们仍然存在需要自定义计算的场景，例如运行平均值（移动平均）。

某机构之前撰写了一篇关于计算运行平均值的文章。本文采用其中描述的原则，但方法略有不同。让我们看看如何利用新的日历功能计算过去三个月的移动平均。

使用经典时间智能

首先，我们使用标准的公历日历和经典的时间智能日期表。我采用了与下文参考文献中某机构文章类似的方法。

Running Average by Month = 
// 1. 获取当前筛选上下文中的最早和最晚日期
VAR MaxDate = MAX( 'Date'[Date] )

// 2. 生成移动平均所需的日期范围（三个月）
VAR DateRange =
 DATESINPERIOD( 'Date'[Date]
        ,MaxDate
        ,-3
        ,MONTH
    )

// 3. 根据第2步生成的日期范围筛选生成一个表
// 该表仅包含三行
VAR SalesByMonth = 
    CALCULATETABLE(
        SUMMARIZECOLUMNS(
            'Date'[MonthKey]
            , "#Sales", [Sum Online Sales]
            
        )
        ,DateRange
    )

RETURN
    // 4. 计算第3步生成表中三个值的平均值
    AVERAGEX(SalesByMonth, [#Sales])

在DAX Studio中执行此度量值时，会得到预期结果。到目前为止，一切正常。

使用标准日历

接下来，我创建了一个名为“公历日历”的日历，并修改代码以使用此日历。为便于理解，将日期表复制到名为“公历日期表”的新表中。

更改之处在于调用DATESINPERIOD()函数时，不再使用日期列，而是使用新创建的日历：

Running Average by Month = 
// 1. 获取当前筛选上下文中的最早和最晚日期
VAR MaxDate = MAX( 'Gregorian Date Table'[Date] )

// 2. 生成移动平均所需的日期范围（三个月）
VAR DateRange =
 DATESINPERIOD( 'Gregorian Calendar'
        ,MaxDate
        ,-3
        ,MONTH
    )

// 3. 根据第2步生成的日期范围筛选生成一个表
// 该表仅包含三行
VAR SalesByMonth = 
    CALCULATETABLE(
        SUMMARIZECOLUMNS(
            'Gregorian Date Table'[MonthKey]
            , "#Sales", [Sum Online Sales]
            
        )
        ,DateRange
    )

RETURN
    // 4. 计算第3步生成表中三个值的平均值
    AVERAGEX(SalesByMonth, [#Sales])

如预期，结果一致。性能表现优异，此查询在150毫秒内完成。

使用自定义日历

但是，使用自定义日历时会发生什么？例如，一个每年15个月、每月31天的日历？我在介绍基于日历的时间智能用例的文章中创建了这样一个日历。

查看度量值的代码时，会发现它有所不同：

Running Average by Month (Custom) = 
    VAR LastSelDate = MAX('Financial Calendar'[CalendarEndOfMonthDate])

    VAR MaxDateID = CALCULATE(MAX('Financial Calendar'[ID_Date])
                                ,REMOVEFILTERS('Financial Calendar')
                                ,'Financial Calendar'[CalendarEndOfMonthDate] = LastSelDate
                                )

    VAR MinDateID = CALCULATE(MIN('Financial Calendar'[ID_Date])
                                ,REMOVEFILTERS('Financial Calendar')
                                ,'Financial Calendar'[CalendarEndOfMonthDate] = EOMONTH(LastSelDate, -2)
                                )

    VAR SalesByMonth = 
        CALCULATETABLE(
            SUMMARIZECOLUMNS(
                'Financial Calendar'[CalendarYearMonth]
                , "#Sales", [Sum Online Sales]
                
            )
            ,'Financial Calendar'[ID_Date] >= MinDateID
                && 'Financial Calendar'[ID_Date] <= MaxDateID
        )

    RETURN
    AVERAGEX(SalesByMonth, [#Sales])

更改的原因是此表缺少可与DATESINPERIOD()函数一起使用的日期列。因此，必须使用自定义代码来计算ID_Date的值范围。结果显示正确。

但分析性能时，发现并不理想，计算过程耗时近半秒。

通过使用日期索引进行优化

我们可以通过消除检索最小和最大ID_Date的需求，并进行更高效的计算来提升性能。已知每月有31天，要回溯三个月，需要回溯93天。可以利用这一点创建一个更快的度量值版本：

Running Average by Month (Financial) = 
    // 步骤 1: 获取最后一个月（ID）
    VAR SelMonth = MAX('Financial Calendar'[ID_Month])
    
    // 步骤 2: 从最近93天生成日期范围
    VAR DateRange =
        TOPN(93
        ,CALCULATETABLE(
                    SUMMARIZECOLUMNS('Financial Calendar'[ID_Date])
                    ,REMOVEFILTERS('Financial Calendar')
                    ,'Financial Calendar'[ID_Month] <= SelMonth
                )
                ,'Financial Calendar'[ID_Date], DESC
            )
    
    
    // 3. 根据第2步生成的日期范围筛选生成一个表
    // 该表仅包含三行
    VAR SalesByMonth = 
        CALCULATETABLE(
            SUMMARIZECOLUMNS(
                'Financial Calendar'[ID_Month]
                , "#Sales", [Sum Online Sales]
                
            )
            ,DateRange
        )
    
    RETURN
        // 4. 计算第3步生成表中三个值的平均值
        AVERAGEX(SalesByMonth, [#Sales])

这次，使用了TOPN()函数从财务日历表中检索前93行，并将此列表用作筛选器。结果与前一版本相同。此版本仅需118毫秒即可完成。

但还能进一步优化吗？接下来，在财务日历中添加了一个新列，为行分配排名。现在，每个日期都有一个唯一的数字，与其顺序直接相关：

Running Average by Month (Financial) = 
    // 步骤 1: 获取最后一个月（ID）
    VAR MaxDateRank = MAX('Financial Calendar'[ID_Date_RowRank])
    
    // 步骤 2: 从最近93天生成日期范围
    VAR DateRange =
            CALCULATETABLE(
                        SUMMARIZECOLUMNS('Financial Calendar'[ID_Date])
                        ,REMOVEFILTERS('Financial Calendar')
                        ,'Financial Calendar'[ID_Date_RowRank] <= MaxDateRank
                            && 'Financial Calendar'[ID_Date_RowRank] >= MaxDateRank - 92
                    )
                    --ORDER BY 'Financial Calendar'[ID_Date] DESC
    
    
    // 3. 根据第2步生成的日期范围筛选生成一个表
    // 该表仅包含三行
    VAR SalesByMonth = 
        CALCULATETABLE(
            SUMMARIZECOLUMNS(
                'Financial Calendar'[ID_Month]
                , "#Sales", [Sum Online Sales]
                
            )
            ,DateRange
        )
    
    RETURN
        // 4. 计算第3步生成表中三个值的平均值
        AVERAGEX(SalesByMonth, [#Sales])

结果相同。但从执行统计数据比较来看，使用TOPN()的版本比使用RowRank列的版本稍慢。更重要的是，使用RowRank列的版本需要更多数据来完成计算。这意味着更多的内存使用。但在行数很少的情况下，差异仍然很小。可以根据偏好选择任一版本。

使用周历

最后，来看基于周的计算。这次，要计算过去三周的滚动平均。由于基于日历的时间智能允许创建基于周的日历，因此度量值与第二个非常相似：

Running Average by Week = 
// 1. 获取当前筛选上下文中的最早和最晚日期
VAR MaxDate = MAX( 'Gregorian Date Table'[Date] )

// 2. 生成移动平均所需的日期范围（三个月）
VAR DateRange =
 DATESINPERIOD( 'Week Calendar'
        ,MaxDate
        ,-3
        ,WEEK
    )

// 3. 根据第2步生成的日期范围筛选生成一个表
// 该表仅包含三行
VAR SalesByMonth = 
    CALCULATETABLE(
        SUMMARIZECOLUMNS(
            'Gregorian Date Table'[WeekKey]
            , "#Sales", [Sum Online Sales]
            
        )
        ,DateRange
    )

RETURN
    // 4. 计算第3步生成表中三个值的平均值
    AVERAGEX(SalesByMonth, [#Sales])

关键部分是在DATESINPERIOD()调用中使用了WEEK参数。仅此而已。查询结果正确，性能优异，执行时间低于100毫秒。请注意，周计算仅在使用基于日历的时间智能时才可能实现。

结论

正如所见，基于日历的时间智能让自定义逻辑变得更简单：我们只需要将日历（而不是日期列）传递给函数，并且可以计算周期间隔。

但当前功能集不包括半年间隔。当必须计算基于半年的结果时，必须使用经典时间智能或编写自定义代码。

我们仍然需要自定义逻辑，尤其是当日历表中没有日期列时。在这种情况下，无法使用标准的时间智能函数，因为它们仍然适用于日期列。

请记住：使用基于日历的时间智能时，最重要的任务是构建一致且完整的日历表。根据经验，这是最复杂的任务。

参考文献

  • 提到的某机构关于计算运行平均值的文章。
  • daxlib.org上关于时间序列函数的不同方法。
  • 解释基于日历的时间智能的上篇文章。

与之前的文章一样，使用了Contoso示例数据集。可以免费从某中心获取ContosoRetailDW数据集。根据此文档中的描述，Contoso数据可在MIT许可证下自由使用。已修改数据集，将数据转移到现代日期。

avatar
文章
阅读
粉丝