人生没有彩排
每一天都是现场直播
        找回密码
当前位置：枫言枫语 > PowerBI > 正文

54、时间智能函数（二）

2025-06-13 作者：夕枫 分类：PowerBI / 进阶篇 评论(0)

本篇文章将介绍时间智能函数中的 STARTOF 与 ENDOF 这两个系列的函数，以及由它们所衍生而来的 OPENINGBALANCE 和 CLOSINGBALANCE 这两个系列的函数。

注意，在使用时间智能函数时需要创建日期表，虽然目前的时间智能函数已经优化了很多次，许多限制都已经放开，但创建日期表仍然是一个最佳实践，能够避开许多容易出错的地方。

另外，由于时间智能函数的使用依赖于日期表，而日期表又比较特殊，因此在阅读时间智能函数系列的文章前，最好已经阅读并理解这篇前置文章：32、应用日期表时的注意事项 。

STARTOFYEAR / STARTOFQUARTER / STARTOFMONTH 函数

语法：

STARTOFYEAR ( <dates> )

STARTOFQUARTER ( <dates> )

STARTOFMONTH ( <dates> )

作用：在指定的日期字段的当前可见数据中，以最小的日期为基准，返回其所在年份、季度、月份的第一个日期。

注意事项：

1、日期参数可以接受三种类型的输入，分别为：对日期列的引用、具有单个日期列的表，以及定义单个日期列表的布尔表达式。

2、日期参数如果是对日期列的引用，那么会触发行上下文转换。

3、返回的日期只包括那些存在于第一参数指定的日期列中的日期。

4、如果参数所指定的日期字段的日期不完整或不连续，那么返回的第一个日期有可能并不是日历定义上的第一天。

辅助理解的例子：

1、下面是 STARTOF 系列函数的示例表达式与结果，为方便演示特意使用切片器挑选了部分日期来展示，实际使用的日期表是完整且连续的：

这几个函数的行为应该还是比较好理解的，但要注意，它们是以当前上下文中的最小日期为基准来判断所在的年份、季度、月份的，进而再返回对应日期范围的第一个日期，具体可以参考总计行的返回结果。

2、如果用代码来表示的话，它们的等价写法如下：

  • STARTOFYEAR函数的等价写法：
STARTOFYEAR('日期表'[Date])

等价于：

VAR tTable = CALCULATETABLE(DISTINCT('日期表'[Date]))
VAR MinDay = MINX(tTable,'日期表'[Date])
VAR DaysInYear =
    FILTER(
        ALL('日期表'[Date]),
        YEAR('日期表'[Date])=YEAR(MinDay)
    )
VAR FirstDayInYear =
    MINX(
        DaysInYear,
        '日期表'[Date]
    )
VAR Result = TREATAS({FirstDayInYear},'日期表'[Date])

RETURN
Result
  • STARTOFQUARTER函数的等价写法：
STARTOFQUARTER('日期表'[Date])

等价于：

VAR tTable = CALCULATETABLE(DISTINCT('日期表'[Date]))
VAR MinDay = MINX(tTable,'日期表'[Date])
VAR DaysInQuarter =
    FILTER(
        ALL('日期表'[Date]),
        YEAR('日期表'[Date])=YEAR(MinDay) &&
            QUARTER('日期表'[Date])=QUARTER(MinDay)
    )
VAR FirstDayInQuarter =
    MINX(
        DaysInQuarter,
        '日期表'[Date]
    )
VAR Result = TREATAS({FirstDayInQuarter},'日期表'[Date])

RETURN
Result
  • STARTOFMONTH函数的等价写法：
STARTOFMONTH('日期表'[Date])

等价于：

VAR tTable = CALCULATETABLE(DISTINCT('日期表'[Date]))
VAR MinDay = MINX(tTable,'日期表'[Date])
VAR DaysInMonth =
    FILTER(
        ALL('日期表'[Date]),
        YEAR('日期表'[Date])=YEAR(MinDay)&&
            MONTH('日期表'[Date])=MONTH(MinDay)
    )
VAR FirstDayInMonth =
    MINX(
        DaysInMonth,
        '日期表'[Date]
    )
VAR Result = TREATAS({FirstDayInMonth},'日期表'[Date])

RETURN
Result

3、这几个函数的日期参数可以接受三种输入，其中一种就是直接引用日期列，而不需要借助任何表函数，因为它等价于：

CALCULATETABLE ( DISTINCT ( '日期表'[Date] ) )

因此，如果第一参数是直接引用日期列，那么还会触发行上下文转换。

4、除了直接引用日期列，日期参数还可以接受具有单个日期列的表，以及定义单个日期列表的布尔表达式，如下图所示：

使用这两种写法时可能会出现红色波浪线的警告，这可能是智能感知功能出了Bug，语法是没问题的，忽略即可。另外，定义单列表的布尔表达式其实也是一个语法糖，它跟CALCULATE函数的布尔筛选是一样的，完整写法如下：

STARTOFMONTH('日期表'[Date] = dt"2016-2-3")

等价于：

STARTOFMONTH(FILTER(ALL('日期表'[Date]),'日期表'[Date] = dt"2016-2-3"))

需要注意的是，当第一参数不是直接引用日期列时，是不会触发行上下文转换的。

ENDOFYEAR / ENDOFQUARTER / ENDOFMONTH 函数

语法：

ENDOFYEAR ( <dates> )

ENDOFQUARTER ( <dates> )

ENDOFMONTH ( <dates> )

作用：在指定的日期字段的当前可见数据中，以最大的日期为基准，返回其所在年份、季度、月份的最后一个日期。

注意事项：

1、日期参数可以接受三种类型的输入，分别为：对日期列的引用、具有单个日期列的表，以及定义单个日期列表的布尔表达式。

2、日期参数如果是对日期列的引用，那么会触发行上下文转换。

3、返回的日期只包括那些存在于第一参数指定的日期列中的日期。

4、如果参数所指定的日期字段的日期不完整或不连续，那么返回的最后一个日期有可能并不是日历定义上的最后一天。

辅助理解的例子：

1、下面是 ENDOF 系列函数的示例表达式与结果，为方便演示特意使用切片器挑选了部分日期来展示，实际使用的日期表是完整且连续的：

这几个函数的行为与STARTOF系列函数的行为基本一致，只不过它们是返回最后一个日期。但要注意，它们是以当前上下文中的最大日期为基准来判断所在的年份、季度、月份的，进而再返回对应日期范围的最后一个日期，具体可以参考总计行的返回结果。

2、如果用代码来表示的话，它们的等价写法如下：

  • ENDOFYEAR函数的等价写法：
ENDOFYEAR('日期表'[Date])

等价于：

VAR tTable = CALCULATETABLE(DISTINCT('日期表'[Date]))
VAR MaxDay = MAXX(tTable,'日期表'[Date])
VAR DaysInYear =
    FILTER(
        ALL('日期表'[Date]),
        YEAR('日期表'[Date])=YEAR(MaxDay)
    )
VAR LastDayInYear =
    MAXX(
        DaysInYear,
        '日期表'[Date]
    )
VAR Result=TREATAS({LastDayInYear},'日期表'[Date])

RETURN
Result
  • ENDOFQUARTER函数的等价写法：
ENDOFQUARTER('日期表'[Date])

等价于：

VAR tTable = CALCULATETABLE(DISTINCT('日期表'[Date]))
VAR MaxDay = MAXX(tTable,'日期表'[Date])
VAR DaysInQuarter =
    FILTER(
        ALL('日期表'[Date]),
        YEAR('日期表'[Date])=YEAR(MaxDay) &&
            QUARTER('日期表'[Date])=QUARTER(MaxDay)
    )
VAR LastDayInQuarter =
    MAXX(
        DaysInQuarter,
        '日期表'[Date]
    )
VAR Result=TREATAS({LastDayInQuarter},'日期表'[Date])

RETURN
Result
  • ENDOFMONTH函数的等价写法：
ENDOFMONTH('日期表'[Date])

等价于：

VAR tTable = CALCULATETABLE(DISTINCT('日期表'[Date]))
VAR MaxDay = MAXX(tTable,'日期表'[Date])
VAR DaysInMonth =
    FILTER(
        ALL('日期表'[Date]),
        YEAR('日期表'[Date])=YEAR(MaxDay) &&
            MONTH('日期表'[Date])=MONTH(MaxDay)
    )
VAR LastDayInMonth =
    MAXX(
        DaysInMonth,
        '日期表'[Date]
    )
VAR Result = TREATAS({LastDayInMonth},'日期表'[Date])

RETURN
Result

3、这几个函数的日期参数可以接受三种输入，其中一种就是直接引用日期列，而不需要借助任何表函数，因为它等价于：

CALCULATETABLE ( DISTINCT ( '日期表'[Date] ) )

因此，如果第一参数是直接引用日期列，那么还会触发行上下文转换。

4、除了直接引用日期列，日期参数还可以接受具有单个日期列的表，以及定义单个日期列表的布尔表达式，如下图所示：

使用这两种写法时可能会出现红色波浪线的警告，这可能是智能感知功能出了Bug，语法是没问题的，忽略即可。另外，定义单列表的布尔表达式其实也是一个语法糖，它跟CALCULATE函数的布尔筛选是一样的，完整写法如下：

ENDOFMONTH('日期表'[Date] = dt"2016-2-3")

等价于：

ENDOFMONTH(FILTER(ALL('日期表'[Date]),'日期表'[Date] = dt"2016-2-3"))

需要注意的是，当第一参数不是直接引用日期列时，是不会触发行上下文转换的。

OPENINGBALANCE 系列

语法：

OPENINGBALANCEYEAR ( <expression>, <dates> [,<filter>] )
OPENINGBALANCEQUARTER ( <expression>, <dates> [,<filter>] )
OPENINGBALANCEMONTH ( <expression>, <dates> [,<filter>] )

作用：在当前计值上下文中，使用最小日期所对应日期范围的第一个日期来计算第一参数的表达式。

该系列的函数是由STARTOF系列的函数衍生而来的，是与CALCULATE函数进行封装后的函数，使用频率较低，具体的行为或作用请参考下面的等价写法：

OPENINGBALANCEYEAR ( <expression>, <dates> [,<filter>] )
等价于：
CALCULATE ( <expression>, STARTOFYEAR ( <dates> ) [,<filter>] )

OPENINGBALANCEQUARTER ( <expression>, <dates> [,<filter>] )
等价于：
CALCULATE ( <expression>, STARTOFQUARTER ( <dates> ) [,<filter>] )

OPENINGBALANCEMONTH ( <expression>, <dates> [,<filter>] )
等价于：
CALCULATE ( <expression>, STARTOFMONTH ( <dates> ) [,<filter>] )

CLOSINGBALANCE 系列

语法：

CLOSINGBALANCEYEAR ( <expression>, <dates> [,<filter>] )
CLOSINGBALANCEQUARTER ( <expression>, <dates> [,<filter>] )
CLOSINGBALANCEMONTH ( <expression>, <dates> [,<filter>] )

作用：在当前计值上下文中，使用最大日期所对应日期范围的最后一个日期来计算第一参数的表达式。

该系列的函数是由ENDOF系列的函数衍生而来的，是与CALCULATE函数进行封装后的函数，使用频率较低，具体的行为或作用请参考下面的等价写法：

CLOSINGBALANCEYEAR ( <expression>, <dates> [,<filter>] )
等价于：
CALCULATE ( <expression>, ENDOFYEAR ( <dates> ) [,<filter>] )

CLOSINGBALANCEQUARTER ( <expression>, <dates> [,<filter>] )
等价于：
CALCULATE ( <expression>, ENDOFQUARTER ( <dates> ) [,<filter>] )

CLOSINGBALANCEMONTH ( <expression>, <dates> [,<filter>] )
等价于：
CALCULATE ( <expression>, ENDOFMONTH ( <dates> ) [,<filter>] )

总结

本篇文章主要介绍了时间智能函数中的 STARTOF 与 ENDOF 这两个系列的函数，它们能够很方便的获取到对应日期范围的第一个或最后一个日期，是很常用到的时间智能函数，建议掌握。

未经允许不得转载：夕枫 » 54、时间智能函数（二）
标签：
夕枫

夕枫

贤者以其昭昭，使人昭昭

相关推荐

订阅评论
提醒
guest
0 评论
最新
最久 最赞
内联反馈
查看所有评论
强烈推荐

每日热点新闻订阅

输入正确邮箱即可免费订阅每日热点新闻速览，

订阅成功后每天早晚的七点三十分将收到邮件。

提示：输入相同邮箱，并选择否，即可取消订阅！

小游戏

最新发布

知识标签

PowerBI (77)DAX (40)SQL (39)筛选调节器 (6)博客搭建 (6)Hexo (6)筛选上下文 (6)MySQL (6)Linux (4)Fluid (4)跨事务任务流 (3)行上下文 (3)计算组 (3)能力测试 (2)筛选器交互 (2)CALCULATE (2)时间智能函数 (2)Auto-Exists (2)表函数 (2)基础函数 (1)计算列 (1)度量值 (1)查询 (1)新建表 (1)