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

创建日期表

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

在开始之前,我们重申一下,某些情况下基于事实表的日期列使用时间智能函数也可以得到正确的结果,但这是错误的做法。原因是时间智能函数需要基于连续不间断的日期才能稳定计算,而类似销售表订单日期列这样的字段很可能是不连续的,因为没法保证每天都有销售记录,时间智能函数的内部实现方式决定了它必须使用连续日期。

另一种危险做法是在 Power BI Desktop 中使用系统自动创建的隐藏日期表,在自动创建日期表的原理一文中,我已经做过详细介绍。

创建日期表是一次性操作,而且以年为单位更新,考虑到这两个特点,我建议你不必在如何创建日期表上花太多时间,用下面给出的代码生成模板直接套用是最高效的方法。研究生成日期表的 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 代码模板

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

此处为隐藏内容 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 年连续日期的日期表,但在计算时会遇到严重的性能问题,建议的日期表范围覆盖事实表日期即可。

Power BI 中支持自动创建日期表,但不建议你使用,因为这其中隐藏了一些复杂性。关于此功能的详细介绍,可以参考自动创建日期表的原理

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

 

2
说点什么

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

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

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组函数

连接函数

集合函数

其他常见函数