从数据到信息
从信息到洞察

自定义时间智能

正如你到目前为止所看到的,标准的 DAX 时间智能函数只支持公历,当你想要按年、季度、月和日这些标准字段分析数据时,公历很实用。但是,你可能对日期区间的定义有不同的需求,比如不是按月对应,而是基于周来计算。无论出于什么原因,当你不能使用标准的时间智能函数时,你都需要重写时间智能函数的逻辑。

本文包含四部分:

  1. 基于周的时间智能
  2. 基于自定义日历表编写累计公式
  3. 计算不连续周期
  4. 区间自定义比较

当你无法使用标准时间智能函数时,要如何进行时间智能计算呢?,最常见的做法是将业务逻辑的一部分移动到日期表。标准 DAX 时间智能函数不会从日期表中读取除日期列以外的任何信息,这是 DAX 的设计初衷,因为这样语言的行为就不依赖于定义更多的元数据来识别用于确定日期的年份、季度和月份的列 (MDX 和多维分析服务就是这种情况),你可以在代码中做更多的假设,这有助于简化代码编写,以便处理与时间相关的自定义计算。

基于周的时间智能

DAX 不提供处理周的时间智能函数,原因是对周的定义和计算方式有许多不同的标准。周经常会跨越年、季度和月的界限。你必须自己编写代码来实现个性化的基于周的日历表的定义。例如,在 ISO 标准的周日期系统中,2011 年 1 月 1 日和 1 月 2 日所在的周是 2010 年的第 52 周,2011 年的第一周从 1 月 3 日开始。

即使有不同的标准,你也可以学习一种在大多数情况下都有效的通用方法,将日期表中的周分配到月/季度/年。后续更改规则只需要更改日期表的内容,而不需要修改度量值里的 DAX 代码。

例如,你可以使用以下计算列扩展日期表对周的划分:

Date[Calendar Week Number] = WEEKNUM ( 'Date'[Date], 1 )
Date[ISO Week Number] = WEEKNUM ( [Date], 21 )
Date[ISO Year Number] =
IF (
    [ISO Week Number] < 5
        && [Calendar Week Number] > 50,
    [Calendar Year Number] + 1,
    IF (
        [ISO Week Number] > 50
            && [Calendar Week Number] < 5,
        [Calendar Year Number] - 1,
        [Calendar Year Number]
    )
)
Date[ISO Week] = "W" & [ISO Week Number] & "-" & [ISO Year Number]
Date[ISO Week Sequential] = INT ( ( 'Date'[Date] - 2 ) / 7 )
Date[ISO Year Day Number] =
COUNTROWS (
    FILTER (
        Date,
        AND (
            'Date'[ISO Year Number] = EARLIER ( 'Date'[ISO Year Number] ),
            'Date'[Date] <= EARLIER ( 'Date'[Date] )
        )
    )
)

你可以在图 7-44 中看到效果。ISO Week 列对用户是可见的,而 ISO Week Sequential Number 对客户端工具是不可见的,ISO 年的日期数是自 ISO 年开始以来的天数。这样的数字可以方便的用作不同时期的比较。

图 7-44 计算列扩展后的日期表支持 ISO 标准周

你可以通过使用 ISO Year Number 列而不是从日期中提取年份来编写当前年份的累计至今度量值。该技巧和“使用基础函数重写 DATESYTD”部分中看到的相同。增加的 IF 判断用于确保选择了单个 ISO Year 来执行 VALUES 函数,以避免报错。

[ISO YTD Sales] :=
IF (
    HASONEVALUE ( 'Date'[ISO Year Number] ),
    CALCULATE (
        [Sales Amount],
        FILTER (
            ALL ( 'Date' ),
            AND (
                'Date'[ISO Year Number] = VALUES ( 'Date'[ISO Year Number] ),
                'Date'[Date] <= MAX ( 'Date'[Date] )
            )
        )
    )
)

下图显示 2008 年第一周(包括 2007 年 12 月 31 日)ISO YTD Sales 指标的结果。

图 7-45 在 2008 年的第一周,ISO YTD Sales 包括了 2007 年 12 月 31 日

公式将当前周数与前一年相同的周数进行比较。因为天数可能并不完全相同,因此使用日期表的其他列来比较更为简单。每年的周数分布很有规律,因为每周总是 7 天,而月份有不同的天数,不适用相同的假设。在基于周的日历中,可以通过在上一年份中查找与当前筛选上下文相同选择的对应日期来简化计算。

下面的 ISO PY Sales 度量值筛选前一年中相同的天数。这种方法也适用于外部上下文包含完整周的情况,因为所选天数使用的是 ISO Year Day Number 值,而不是日期值。

[ISO PY Sales] :=
IF (
    HASONEVALUE ( 'Date'[ISO Year Number] ),
    CALCULATE (
        [Sales Amount],
        FILTER (
            ALL ( 'Date' ),
            AND (
                'Date'[ISO Year Number]
                    = VALUES ( 'Date'[ISO Year Number] ) - 1,
                CONTAINS (
                    VALUES ( 'Date'[ISO Year Day Number] ),
                    'Date'[ISO Year Day Number], 'Date'[ISO Year Day Number]
                )
            )
        )
    )
)

图 7-46 ISO PY Sales 显示了同一周在前一年的值

基于周的日历很容易管理,因为不同年份中周的天数始终是 7 天。而月份的天数可能会有变化,因此如果你想同时使用两个层级结构(月和周),你必须为每个层级结构创建不同的时间智能计算。

周累计(WTD)公式

将 ISO YTD Sales 稍作修改,可以很方便的编写出周累计公式 ISO WTD Sales:

[ISO WTD Sales] :=
IF (
    HASONEVALUE ( 'Date'[ISO Week Sequential] ),
    CALCULATE (
        [Sales Amount],
        FILTER (
            ALL ( 'Date' ),
            AND (
                'Date'[ISO Week Sequential] = VALUES ( 'Date'[ISO Week Sequential] ),
                'Date'[Date] <= MAX ( 'Date'[Date] )
            )
        )
    )
)

基于自定义日历表编写累计公式

在之前 DATESYTD 一节中,你已经学会了如何用基础函数重写 DATESYTD 以及其他类似的时间智能函数。在本节中,我们将演示如何使用日期表的其他列替换从日期中提取的信息。

例如,观察下面这个 YTD 销售度量值:

[YTD Sales] := CALCULATE ( [Sales Amount],DATESYTD ( 'Date'[Date] ))

使用原生 DAX 函数重写后的语法:

[YTD Sales] :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date'[Date] ),
        AND (
            'Date'[Date] <= MAX ( 'Date'[Date] ),
            YEAR ( 'Date'[Date] ) = YEAR ( MAX ( 'Date'[Date] ) )
        )
    )
)

如果使用自定义日历表,你需要将对 YEAR 函数的调用替换为对年份列的调用,例如下面的 YTD Sales Custom 度量值,FILTER 迭代整张日期表,以便行上下文可以访问所有列。如果外部上下文包含多个年份,则使用 MAX 获得最后一年。

[YTD Sales Custom] :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date' ),
        AND (
            'Date'[Date] <= MAX ( 'Date'[Date] ),
            'Date'[Calendar Year Number] = MAX ( 'Date'[Calendar Year Number] )
        )
    )
)

使用同样的方式我们可以编写出 QTDMTD 度量值,唯一的区别在于将 Calendar Year Number 列替换为所需的列。

[QTD Sales Custom] :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date' ),
        AND (
            'Date'[Date] <= MAX ( 'Date'[Date] ),
            'Date'[Calendar Year Quarter Number]
                = MAX ( 'Date'[Calendar Year Quarter Number] )
        )
    )
)

[MTD Sales Custom] :=
CALCULATE (
    [Sales Amount],
    FILTER (
        ALL ( 'Date' ),
        AND (
            'Date'[Date] <= MAX ( 'Date'[Date] ),
            'Date'[Calendar Year Month Number] = MAX ( 'Date'[Calendar Year Month Number] )
        )
    )
)

你可以使用这些公式实现基于标准日历表的计算(在必须使用 DirectQuery 的情况下)和自定义日历表的计算(在非标准时间区间的情况下)。

基于不连续周期的计算

标准的 DAX 时间智能函数不支持计算非连续时间段。正如你在 DATEADD 一文中看到的,DATEADD 函数仅适用于连续日期选择。但是借助自定义 DAX 公式,你可以在这个场景下计算。在基于周的时间智能一节中,你已经看到了一种使用 CONTAINS 的写法,它可以查找上一年中对应的相同日期。但是,如果你有一套基于月的标准日历,那情况将变得更加复杂。

本文隐藏内容查看价格为5G币,请先
注:加入VIP会员可享受全站权益,性价比更高。单独购买的内容长期有效,不受时间限制。

自定义区间比较

在上一节中,你已经了解了如何在含月份的日期表中实现对前一个时间段的计算。你可以把同样的方法应用到一年中的任何时间段。但是,自定义日历可能需要应用特殊的规则才能进行比较,将一年中的特定时段(例如劳动节、端午节、复活节、新年以及任何其他可能的假日)排除在外。在本节中,你将学习一种在数据模型中处理这些规则的方法,而不必在 DAX 表达式中加入判断逻辑。

本文隐藏内容查看价格为5G币,请先
注:加入VIP会员可享受全站权益,性价比更高。单独购买的内容长期有效,不受时间限制。

7
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
6 评论数
8 被回复的评论
7 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
成员
xifeng

老师,在基于不连续周期的计算这一节中的【PM sales Custom】,它是不是不能实现DATEADD的跨临界日期的计算逻辑?

成员
easyrider

Date[ISO Year Number]这个计算列的if条件部分看不懂为什么这么写,能详细说下具体解释下吗?

成员
bowang72

您好,“基于不连续周期的计算”和“区间自定义比较”这两小节具体内容为啥看不到了呢?我是VIP会员啊

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数