创建日期表是一次性操作，而且以年为单位更新，考虑到这两个特点，我建议你不必在如何创建日期表上花太多时间，用下面给出的代码生成模板直接套用是最高效的方法。研究生成日期表的 N 种方法，就像研究茴字的四种写法一样，形式大于内容。
创建一张标准的日期表是使用时间智能函数的起点，在上一篇初识时间智能中我们介绍了日期表的意义，本文我会介绍常用的创建日期表的方法并附上源代码，你可以直接复制到自己的环境中使用。
使用 DAX 创建日期表
当数据源中没有日期表的时候，可以使用 DAX 在数据模型中直接创建，方法是使用 CALENDAR 或 CALENDARAUTO 新建表，然后向表中添加计算列。这两个函数返回一个日期数据类型的单列的表。例如，CALENDARAUTO 自动找到包含在整个数据模型中的所有日期列的最小和最大年份，并生成这些年之间包含的所有日期。
并不是所有产品都支持使用 DAX 表达式在数据模型中创建表。Excel Power Pivot 的任何一个版本目前都不支持此功能。 - 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 会考虑原本想要忽略的日期列。在前面的示例中，客户表中的出生日期扩展了年份范围，虽然你永远不会将这样一个列与日期表相关联。遇到类似情况时，你可以使用 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 代码模板
如果你需要可以直接套用的代码，使用建模选项卡的新建表功能，将以下代码复制到公式栏即可，提供两种写法
- 按年平移，公式自动生成以今天所在年份为截止，向前平移 N 年的所有日期。这种写法只保留指定年份的日期，跨年可以自动更新，不保存历史数据，适合计算相对日期的模型
- 指定年份，指定起止日期固定的日期表。适合计算绝对日期的模型
- Calendar Table =
- VAR MinDate =
- DATE ( YEAR ( TODAY () ) - 2, 1, 1 ) //控制平移年份 -2 是过去两年
- VAR MaxDate =
- DATE ( YEAR ( TODAY () ), 12, 31 )
- VAR BaseCalendar =
- CALENDAR ( MinDate, MaxDate )
- RETURN
- GENERATE (
- BaseCalendar,
- VAR BaseDate = [Date]
- VAR YearDate = YEAR ( BaseDate )
- VAR MonthNumber = MONTH ( BaseDate )
- VAR YearMonthNumber = YearDate * 100 + MonthNumber
- VAR WeekDayNumber = WEEKDAY(BaseDate,2)
- VAR WeekDayCN = SWITCH(WeekDayNumber,
- 1,"周一",
- 2,"周二",
- 3,"周三",
- 4,"周四",
- 5,"周五",
- 6,"周六",
- "周日"
- )
- RETURN
- ROW (
- "Year", YearDate,
- "Month Number", MonthNumber,
- "Month", FORMAT ( BaseDate, "mmmm" ),
- "Year Month Number", YearMonthNumber,
- "Year Month", FORMAT ( BaseDate, "yy-mmm" ),
- "WeekNum in Year", WEEKNUM ( BaseDate ),
- "WeekNum all Year", WEEKNUM ( BaseDate )
- + ( YEAR ( BaseDate ) - YEAR ( MinDate ) ) * 53,
- "WeekDay Number",WeekDayNumber, //周一为每周第一天
- "WeekDay CN",WeekDayCN,
- "Is WeekEnd",IF(WeekDayNumber>=6,"Y","N")
- )
- )
- Calendar Table =
- VAR MinDate =
- DATE ( 2019,1,1 ) //自定义起始年份
- VAR MaxDate =
- DATE ( 2019,12,31 ) //自定义截止年份
- VAR BaseCalendar =
- CALENDAR ( MinDate, MaxDate )
- RETURN
- GENERATE (
- BaseCalendar,
- VAR BaseDate = [Date]
- VAR YearDate = YEAR ( BaseDate )
- VAR MonthNumber = MONTH ( BaseDate )
- VAR YearMonthNumber = YearDate * 100 + MonthNumber
- VAR WeekDayNumber = WEEKDAY(BaseDate,2)
- VAR WeekDayCN = SWITCH(WeekDayNumber,
- 1,"周一",
- 2,"周二",
- 3,"周三",
- 4,"周四",
- 5,"周五",
- 6,"周六",
- "周日"
- )
- RETURN
- ROW (
- "Year", YearDate,
- "Month Number", MonthNumber,
- "Month", FORMAT ( BaseDate, "mmmm" ),
- "Year Month Number", YearMonthNumber,
- "Year Month", FORMAT ( BaseDate, "yy-mmm" ),
- "WeekNum in Year", WEEKNUM ( BaseDate ),
- "WeekNum all Year", WEEKNUM ( BaseDate )
- + ( YEAR ( BaseDate ) - YEAR ( MinDate ) ) * 53,
- "WeekDay Number",WeekDayNumber, //周一为每周第一天
- "WeekDay CN",WeekDayCN,
- "Is WeekEnd",IF(WeekDayNumber>=6,"Y","N")
- )
- )
公式效果图，可以根据自己需要继续补充其他列
注意：WeekNum all Year 列仅作排序使用，不反应真实的周累计数据
使用 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(COUNTROWS(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 中支持自动创建日期表，比如下图中在日期列后面输入一个英文句号，可以显示隐藏日期表中的字段，不建议你使用这种方式引用日期，因为这其中隐藏了一些复杂性。在自动创建日期表的原理一文中，我已经做过详细介绍。
自动创建的日期表使用带.的日期列 |
[复制链接]
