从数据到信息
从信息到洞察
        找回密码
热门标签： 软件更新图表更新注册账户
当前位置：PowerBI极客 > DAX 圣经 > 时间智能函数 > 正文

创建日期表

2019-11-20 分类：时间智能函数 阅读(3.22w) 评论(14) 阅读约需55分钟

创建日期表是一次性操作，而且以年为单位更新，考虑到这两个特点，我建议你不必在如何创建日期表上花太多时间，用下面给出的代码生成模板直接套用是最高效的方法。研究生成日期表的 N 种方法，就像研究茴字的四种写法一样，形式大于内容。

创建一张标准的日期表是使用时间智能函数的起点，在上一篇初识时间智能中我们介绍了日期表的意义，本文我会介绍常用的创建日期表的方法并附上源代码，你可以直接复制到自己的环境中使用。

使用 DAX 创建日期表

当数据源中没有日期表的时候，可以使用 DAX 在数据模型中直接创建，方法是使用 CALENDAR 或 CALENDARAUTO 新建表，然后向表中添加计算列。这两个函数返回一个日期数据类型的单列的表。例如，CALENDARAUTO 自动找到包含在整个数据模型中的所有日期列的最小和最大年份，并生成这些年之间包含的所有日期。

并不是所有产品都支持使用 DAX 表达式在数据模型中创建表。Excel Power Pivot 的任何一个版本目前都不支持此功能。

CALENDARAUTO

CALENDARAUTO ( [<FiscalYearEndMonth>] )

CALENDARAUTO 会扫描模型中的所有日期列，但是计算列除外。例如，如果你使用 CALENDARAUTO 在一个模型中创建日期表，该模型包含 2007 年至 2011 年的销售额，并且在产品表中有一个从 2004 年开始的 AvailableForSaleDate 列，那么函数的运行结果是 2004 年 1 月 1 日至 2011 年 12 月 31 日之间的所有天数。但是，如果数据模型包含其他日期列，它们可能会影响 CALENDARAUTO 所考虑的日期范围。例如，下图中，你可以看到日期范围从 1910 年 1 月 1 日开始，因为数据模型包含一个客户表，其中包含客户的出生日期列，其中有一个客户出生于 1910 年。

CALENDARAUTO()

你可以将月序号指定为 CALENDARAUTO 的参数。此时生成从参数的下个月的第一天到作为参数指示月份的最后一天的日期。当你的财政年度在除十二月外的某月结束时，这个参数很有用。例如，下面的表达式生成一个从 7 月 1 日开始到 6 月 30 日结束的财年的日期表，如图所示:

CALENDARAUTO ( 6 )

如你所见，CALENDARAUTO 会考虑原本想要忽略的日期列。在前面的示例中，客户表中的出生日期扩展了年份范围，虽然你永远不会将这样一个列与日期表相关联。遇到类似情况时，你可以使用 CALENDAR 函数。

CALENDAR

CALENDAR ( <StartDate>, <EndDate> )

该函数有两个参数：开始日期和结束日期。下面的表达式生成一个日期列，覆盖销售表中使用的所有年份，

CALENDAR (
          DATE ( YEAR ( MIN ( Sales[Order Date] ) ), 1, 1 ),
          DATE ( YEAR ( MAX ( Sales[Order Date] ) ), 12, 31 )
)

公式的结果从 2007 年 1 月 1 日开始，这是销售表订单日期列第一个日期的所在年份，一旦有了日期列，你需要使用 DAX 表达式为日期表创建其他列。下面是常用的表达式列表，图 7-6 是它们的结果示例:

'Date'[Year] = YEAR ( 'Date'[Date] )
'Date'[Quarter Number] = INT ( FORMAT ( [Date], "q") )
'Date'[Quarter] = "Q" & INT ( FORMAT ( [Date], "q") )
'Date'[Month Number] = MONTH ( 'Date'[Date] )
'Date'[Month] = FORMAT ( 'Date'[Date], "mmmm" )
'Date'[Week Day Number] = WEEKDAY ( 'Date'[Date] )
'Date'[Week Day] = FORMAT ( 'Date'[Date], "dddd" )
'Date'[Year Month Number] = YEAR ( 'Date'[Date] ) * 100 + MONTH ( 'Date'[Date] )
'Date'[Year Month] = FORMAT ( 'Date'[Date], "mmmm" ) & " " & YEAR ( 'Date'[Date] )
'Date'[Year Quarter Number] = YEAR ( 'Date'[Date] ) * 100 + INT ( FORMAT ( [Date], "q") )
'Date'[Year Quarter] = "Q" & FORMAT ( [Date], "q") & "-" & YEAR ( 'Date'[Date] )

图 7-6 使用 DAX 表达式补全后的日期表

出于性能原因考虑，最佳做法是创建自然层级结构。日期表中的层级结构应该使用具有唯一值的列，而不考虑层级结构中的父类。出于这个原因，你应该使用 Year Month 和 Year Quarter 列作为例如 Year-Quarter-Month- day 层级结构中的级别。同时分别使用年月序号和年季度序号列对这些列进行排序。为了使用类似将年置于列上、季度和月置于行上的数据透视表结构，你可以将季度和月设为可见，使用隐藏的排序列（Quarter Number 和 Month Number）分别对它们进行排序。

DAX 代码模板

如果你需要可以直接套用的代码，使用建模选项卡的新建表功能，将以下代码复制到公式栏即可，提供两种写法

本文隐藏内容查看价格为1G币，请先
单独购买的内容长期有效，不受时间限制（购买前先刷新当前页面）。加入VIP会员可享受全站权益，性价比更高。

使用 PowerQuery 生成日期表

切换到编辑查询界面，新建源 – 空查询，打开高级编辑器，用以下代码覆盖原有内容：

let FunDate=(起始年份 as number,结束年份 as number)=>
let
    日期序列 = {Number.From(#date(起始年份,1,1))..Number.From(#date(结束年份,12,31))},
    转到表 = Table.FromList(日期序列, Splitter.SplitByNothing(), {"日期"}),
    日期 = Table.TransformColumnTypes(转到表,{"日期", type date}),
    日期序 = Table.AddColumn(日期, "日期序", each Date.ToText([日期],"yyyyMMdd")),
    年序 = Table.AddColumn(日期序, "年序", each Date.Year([日期])),
    年份名 = Table.AddColumn(年序, "年份名", each "Y"&Date.ToText([日期],"yyyy")),
    季序 = Table.AddColumn(年份名, "季序", each Date.QuarterOfYear([日期])),
    季度名 = Table.AddColumn(季序, "季度名", each "Q"&Text.From([季序])),
    月序 = Table.AddColumn(季度名, "月序", each Date.Month([日期])),
    月序名 = Table.AddColumn(月序, "月份名", each "M"&Text.From([月序])),
    周序 = Table.AddColumn(月序名, "周序", each Date.WeekOfYear([日期],1)),
    周序名 = Table.AddColumn(周序, "周名称", each "W"&Text.From([周序])),
    年季序 = Table.AddColumn(周序名, "年季序", each Text.From([年序])&Text.From([季序])),
    年序名 = Table.AddColumn(年季序, "年季名", each "YQ"&[年季序]),
    年月序 = Table.AddColumn(年序名, "年月序", each Text.From([年序])&Text.From([月序])),
    年月名 = Table.AddColumn(年月序, "年月名", each "YM"&[年月序]),
    年周序 = Table.AddColumn(年月名, "年周序", each Text.From([年序])&Text.From([周序])),
    年周名 = Table.AddColumn(年周序, "年周名", each "YW"&[年周序]),
    日序 = Table.AddColumn(年周名, "日序号", each Date.Day([日期])),
    星期 = Table.AddColumn(日序, "星期", each Date.DayOfWeek([日期],1)+1),
    中文星期 = Table.AddColumn(星期, "中文星期", each Date.ToText([日期], "dddd","zh-cn")),
    英文星期 = Table.AddColumn(中文星期, "英文星期", each Date.ToText([日期], "ddd","en-us")),
    工作日 = Table.AddColumn(英文星期, "工作日", each if [星期]<6 then "工作日" else "休息日"),
    英文月 = Table.AddColumn(工作日, "英文月", each Date.ToText([日期],"MMM.","en-us"))
in
    英文月,
    调用日期=FunDate(2015,2016)
in
    调用日期

自定义起始年份

日期表效果

因为日期表行数比较少，无论使用 PQ 还是 DAX 生成，这两种方法在性能上没有差异

以标准日期表为基础，你可以根据自己的需要添加辅助列以丰富模型的计算能力，比如标记节假日、休息日、财年等，由于节假日不同国家和地区的习惯不同，通常不适合用公式生成，手工标记更方便。

使用 Excel 生成日期表

考虑到日期表一般在千行左右，可以用 Excel 生成模板备用，需要的时候导入 Power BI，当需要手工标注日期时，Excel 操作更简单。

任意一个空白单元格输入起始日期，使用填充功能，按下面截图中的设置可以快速生成基准日期列，随后使用 Excel 函数扩充日期列。

如何生成基准日期列

使用 SQL 生成日期表

CREATE TABLE dbo.Dim_Date
(	Calendar_Date DATE NOT NULL CONSTRAINT PK_Dim_Date PRIMARY KEY CLUSTERED, -- The date addressed in this row.
  Calendar_Date_String VARCHAR(10) NOT NULL, -- The VARCHAR formatted date, such as 07/03/2017
  Calendar_Month TINYINT NOT NULL, -- Number from 1-12
  Calendar_Day TINYINT NOT NULL, -- Number from 1 through 31
  Calendar_Year SMALLINT NOT NULL, -- Current year, eg: 2017, 2025, 1984.
  Calendar_Quarter TINYINT NOT NULL, -- 1-4, indicates quarter within the current year.
  Day_Name VARCHAR(9) NOT NULL, -- Name of the day of the week, Sunday...Saturday
  Day_of_Week TINYINT NOT NULL, -- Number from 1-7 (1 = Sunday)
  Day_of_Week_in_Month TINYINT NOT NULL, -- Number from 1-5, indicates for example that it's the Nth saturday of the month.
  Day_of_Week_in_Year TINYINT NOT NULL, -- Number from 1-53, indicates for example that it's the Nth saturday of the year.
  Day_of_Week_in_Quarter TINYINT NOT NULL, -- Number from 1-13, indicates for example that it's the Nth saturday of the quarter.
  Day_of_Quarter TINYINT NOT NULL, -- Number from 1-92, indicates the day # in the quarter.
  Day_of_Year SMALLINT NOT NULL, -- Number from 1-366
  Week_of_Month TINYINT NOT NULL, -- Number from 1-6, indicates the number of week within the current month.
  Week_of_Quarter TINYINT NOT NULL, -- Number from 1-14, indicates the number of week within the current quarter.
  Week_of_Year TINYINT NOT NULL, -- Number from 1-53, indicates the number of week within the current year.
  Month_Name VARCHAR(9) NOT NULL, -- January-December
  First_Date_of_Week DATE NOT NULL, -- Date of the first day of this week.
  Last_Date_of_Week DATE NOT NULL, -- Date of the last day of this week.
  First_Date_of_Month DATE NOT NULL, -- Date of the first day of this month.
  Last_Date_of_Month DATE NOT NULL, -- Date of the last day of this month.
  First_Date_of_Quarter DATE NOT NULL, -- Date of the first day of this quarter.
  Last_Date_of_Quarter DATE NOT NULL, -- Date of the last day of this quarter.
  First_Date_of_Year DATE NOT NULL, -- Date of the first day of this year.
  Last_Date_of_Year DATE NOT NULL, -- Date of the last day of this year.
  Is_Holiday BIT NOT NULL, -- 1 if a holiday
  Is_Holiday_Season BIT NOT NULL, -- 1 if part of a holiday season
  Holiday_Name VARCHAR(50) NULL, -- Name of holiday, if Is_Holiday = 1
  Holiday_Season_Name VARCHAR(50) NULL, -- Name of holiday season, if Is_Holiday_Season = 1
  Is_Weekday BIT NOT NULL, -- 1 if Monday-->Friday, 0 for Saturday/Sunday
  Is_Business_Day BIT NOT NULL, -- 1 if a workday, otherwise 0.
  Previous_Business_Day DATE NULL, -- Previous date that is a work day
  Next_Business_Day DATE NULL, -- Next date that is a work day
  Is_Leap_Year BIT NOT NULL, -- 1 if current year is a leap year.
  Days_in_Month TINYINT NOT NULL -- Number of days in the current month.
);

注意事项

日期表不要包含短期内用不到的年份

日期表的日期并非越多越好，虽然我们可以很容易的生成含有 100 年连续日期的日期表，但在计算时会遇到严重的性能问题，建议日期范围在覆盖事实表日期的基础上，适当增加一到两年的余量即可。

计算累计的时候首先使用 IF 排除掉无数据的日期可以提高公式计算效率，比如 IF(ISEMPTY(FactTable)>0,[度量值])

不要直接使用事实表的日期

在上文介绍日期表一节中，我们介绍过相关注意事项，本文再次重申，某些情况下基于事实表的日期列使用时间智能函数也可以得到正确的结果，但这是错误的做法。原因是时间智能函数需要基于连续不间断且完整的日期才能稳定计算，而类似销售表订单日期列这样的字段很可能是不连续的，因为没法保证每天都有销售记录，时间智能函数的内部实现方式决定了它必须使用日期连续且完整的年。

使用完整的年份

时间智能函数将日期表的最后一天视为截止日，除了财年之外，我们默认一年的截止日期都是 12 月 31 日（财年的截止日一般是某个月份的最后一天）。如果你错误的设置了截止日，比如直接用事实表的最大日期作为日期表的截止日，某些时间智能计算会得到错误结果。

举个例子：假设你的日期表最后一天是 3 月 16 日，当使用 DATEADD 计算 2 月 26 日-3 月 16 日的去年同期值时，公式实际计算的是去年 2 月 26 日-3 月 31 日的结果。严格来讲这个逻辑是正确的，因为 DAX 计算的日期范围始终是 2 月 26 日到 3 月底，问题的原因在于日期表截止日被错误的指定到 16 日，使用本文介绍的 CALENDARAUTO 或者 CALENDAR 函数可以避免此类问题。

不要使用隐藏的日期表

另一种危险做法是在 Power BI Desktop 中使用系统自动创建的隐藏日期表，Power BI 中支持自动创建日期表，比如下图中在日期列后面输入一个英文句号，可以显示隐藏日期表中的字段，不建议你使用这种方式引用日期，因为这其中隐藏了一些复杂性。在自动创建日期表的原理一文中做过详细介绍。而且保留这种自动创建的日期表在某些情况下会导致某些计算返回意料之外的结果，建议你从数据加载选项中永久关闭「自动日期/时间」功能，在下一篇文章中我们将展示一个案例。

自动创建的日期表使用带.的日期列

 

“扫一扫”将本文分享到微信
印象笔记
邮件分享
标签：

相关推荐

14
说点什么

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

CALENDERAUTO函数可以自定义财年结算月，假如我是用CALENDER函数创建日期表是不是财年结算月只能是12月？

赞0踩  回复
1 月 之前
| 查看回复 (2)
成员
153****1882

高老师，我有一些sql中查询语句的基础，还是没学过新建表，虽然代码可以直接拿来用，还是想问一下：
1.第一行的dbo.dim_date是调用了一个功能模块还是仅代表一个表名？
2.日期的起止区间在哪里确定？
3.后面的代码在定义列，如day-of-week那行中，day-of-week是列名还是已经定义好的函数？

赞0踩  回复
10 月 之前
成员
小本本

为啥这里11行[Date]加上BaseCalendar就报错了? 可以说下原因吗?

Snipaste_2022-08-31_16-37-57.jpg
赞0踩  回复
2 年 之前
| 查看回复 (2)
成员
phyowcy

复制DAX代码，为什么出来的日期表只显示工作日呢？

Capture.PNG
赞0踩  回复
2 年 之前
| 查看回复 (1)
成员
181****7536

高老师，如图，直接在数据模型中使用命令创建的日期表中的每一个列算派生列吗？有没有数据沿袭？ 记得前面说表中的计算列都算派生列。

Capture.PNG
赞0踩  回复
3 年 之前
| 查看回复 (1)
成员
注视

【DAX 代码模板】中【指定日期间隔】模版有错误，第3行和第5行代码中要删去“ YEAR ( ”
文末“自动创建的日期表使用带.的日期列”图不能正常显示。

赞0踩  回复
4 年 之前
| 查看回复 (2)
DAX 圣经
知识体系函数大全

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数