12.2 创建日期表
创建一张标准的日期表是使用时间智能函数的起点，在上一篇初识时间智能中我们介绍了日期表的意义，本文我会介绍常用的创建日期表的方法并附上源代码，你可以直接复制到自己的环境中使用。在开始之前，我们重申一下，某些情况下基于事实表的日期列使用时间智能函数也可以得到正确的结果，但这是错误的做法。原因是时间智能函数需要基于连续不间断的日期才能稳定计算，而类似销售表订单日期列这样的字段很可能是不连续的，因为没法保证每天都有销售记录，时间智能函数的内部实现方式决定了它必须使用连续日期。另一种危险做法是在 Power BI Desktop 中使用系统自动创建的隐藏日期表，在自动创建日期表的原理一文中，我已经做过详细介绍。创建日期表是一次性操作，而且以年为单位更新，考虑到这两个特点，我建议你不必在如何创建日期表上花太多时间，用下面给出的代码生成模板直接套用是最高效的方法。研究生成日期表的 N 种方法，就像研究茴字的四种写法一样，形式大于内容。## 使用 DAX 创建日期表
当数据源中没有日期表的时候，可以使用 DAX 在数据模型中直接创建，方法是使用 CALENDAR 或 CALENDARAUTO 新建表，然后向表中添加计算列。这两个函数返回一个日期数据类型的单列的表。例如，CALENDARAUTO 自动找到包含在整个数据模型中的所有日期列的最小和最大年份，并生成这些年之间包含的所有日期。并不是所有产品都支持使用 DAX 表达式在数据模型中创建表。Excel Power Pivot 的任何一个版本目前都不支持此功能。### CALENDARAUTO
CALENDARAUTO ( [
CALENDARAUTO( )你可以将月序号指定为 CALENDARAUTO 的参数。此时生成从参数的下个月的第一天到作为参数指示月份的最后一天的日期。当你的财政年度在除十二月外的某月结束时，这个参数很有用。例如，下面的表达式生成一个从 7 月 1 日开始到 6 月 30 日结束的财年的日期表，如图所示:CALENDARAUTO(6)
如你所见，CALENDARAUTO 会考虑原本想要忽略的日期列。在前面的示例中，客户表中的出生日期扩展了年份范围，虽然你永远不会将这样一个列与日期表相关联。遇到类似情况时，你可以使用 CALENDAR 函数。### CALENDAR
CALENDAR ( 
公式的结果从 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)RETURNGENERATE(BaseCalendar,VAR BaseDate =[Date]VAR YearDate =YEAR(BaseDate)VAR MonthNumber =MONTH(BaseDate)VAR YearMonthNumber = YearDate 100+ MonthNumberVAR WeekDayNumber =WEEKDAY(BaseDate,2)VAR WeekDayCN =SWITCH(WeekDayNumber,1,”周一”,2,”周二”,3,”周三”,4,”周四”,5,”周五”,6,”周六”,”周日”)RETURNROW(“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)RETURNGENERATE(BaseCalendar,VAR BaseDate =[Date]VAR YearDate =YEAR(BaseDate)VAR MonthNumber =MONTH(BaseDate)VAR YearMonthNumber = YearDate 100+ MonthNumberVAR WeekDayNumber =WEEKDAY(BaseDate,2)VAR WeekDayCN =SWITCH(WeekDayNumber,1,”周一”,2,”周二”,3,”周三”,4,”周四”,5,”周五”,6,”周六”,”周日”)RETURNROW(“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[星期]<6then”工作日”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_StringVARCHAR(10)NOT NULL, — The VARCHAR formatted date, such as07/03/2017Calendar_Month TINYINT NOT NULL, — Number from1-12Calendar_Day TINYINT NOT NULL, — Number from1through31Calendar_Year SMALLINT NOT NULL, — Current year, eg:2017,2025,1984.Calendar_Quarter TINYINT NOT NULL, —1-4, indicates quarter within the current year.Day_NameVARCHAR(9)NOT NULL, — Name of the day of the week, Sunday…SaturdayDay_of_Week TINYINT NOT NULL, — Number from1-7(1= Sunday)Day_of_Week_in_Month TINYINT NOT NULL, — Number from1-5, indicates for example that it’s the Nth saturday of the month.Day_of_Week_in_Year TINYINT NOT NULL, — Number from1-53, indicates for example that it’s the Nth saturday of the year.Day_of_Week_in_Quarter TINYINT NOT NULL, — Number from1-13, indicates for example that it’s the Nth saturday of the quarter.Day_of_Quarter TINYINT NOT NULL, — Number from1-92, indicates the day# in the quarter.Day_of_Year SMALLINT NOT NULL, — Number from1-366Week_of_Month TINYINT NOT NULL, — Number from1-6, indicates the number of week within the current month.Week_of_Quarter TINYINT NOT NULL, — Number from1-14, indicates the number of week within the current quarter.Week_of_Year TINYINT NOT NULL, — Number from1-53, indicates the number of week within the current year.Month_NameVARCHAR(9)NOT NULL, — January-DecemberFirst_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, —1if a holidayIs_Holiday_Season BIT NOT NULL, —1if part of a holiday seasonHoliday_NameVARCHAR(50)NULL, — Name of holiday, if Is_Holiday =1Holiday_Season_NameVARCHAR(50)NULL, — Name of holiday season, if Is_Holiday_Season =1Is_Weekday BIT NOT NULL, —1if Monday—>Friday,0for Saturday/SundayIs_Business_Day BIT NOT NULL, —1if a workday, otherwise0.Previous_Business_Day DATE NULL, — Previous date that is a work dayNext_Business_Day DATE NULL, — Next date that is a work dayIs_Leap_Year BIT NOT NULL, —1if current year is a leap year.Days_in_Month TINYINT NOT NULL — Number of days in the current month.);## 注意事项
日期表的日期并非越多越好，虽然我们可以很容易的生成含有 100 年连续日期的日期表，但在计算时会遇到严重的性能问题，建议的日期表范围覆盖事实表日期即可。Power BI 中支持自动创建日期表，但不建议你使用，因为这其中隐藏了一些复杂性。关于此功能的详细介绍，可以参考自动创建日期表的原理
自动创建的日期表使用带.的日期列
若有收获，就点个赞吧
0 人点赞
