作为POWER BI用户，一定会涉及到用日期表进行日期智能分析的工作。因为不管是什么方面的数据，一定会有时间的维度，既然有时间维度就需要对数据日期相关的分析。
几乎所有的业务逻辑都会涉及到关于时间的计算，比如年累计销售额，月环比销售额，YOY（Year over Year）等等。这些功能都需要日期表的支持。
日期表定义
创建一张标准的日期表是使用时间智能函数的起点。
日期表在POWER BI中，是一张只包含一定日期范围的表，此表有一个唯一日期列，加上由唯一日期计算出的相关列所构成。此表一般在分析前通过人工方法生成。
为什么必须用日期表，可以直接用交易数据中的日期吗？
答案是：不可以。
最直接的原因是：交易中的日期可能是残缺的。例如：某个日期是没有交易的。导致你想要的某些日期是不存在于交易数据中的。
必须使用日期表的真正原因来自两点：
1、数据模型的设计学
2、复用
从设计的角度看，日期序列常常是分析中表征时间变化的最小时间跨度单位。
而做分析的时候，我们往往需要使用的却不是日期级别的时间跨度，而是用诸如：
- 按年度看销售额趋势
- 按月份对比前后两年的销售额差异
- 按年度至今来比对当前目标完成度与年度总目标的差异
可见：
分析时所使用的日期区间跨度都是大于单个日期的。
更精确地说，对于某个日期，如：yyyy-MM-dd，记作 D1，其日期区间跨度为 1 日。而常用的日期区间的跨度都会大于 1 日。
为了可以得到任何范围的日期区间跨度，就需要一个可以容纳每一天日期的表，该表满足：
包括所需要的所有日期。
从设计学的角度，我们称为了满足随后的分析而构建的这个表叫：日期表。
日期表的设计学用途是：
当希望从某段日期区间跨度去筛选交易业务数据时，都可以从日期表作为出发点，由于日期表如上描述的设计，它必然满足：
一定可以从日期表中找到所需要的日期区间来筛选业务数据。
再者，由于业务可能有多种明细记录，如：销售明细表或采购明细表，因此，共享一个日期表，就起到了复用的目的。
构建日期表
构建日期表时，通过分两步，首先是构建出某个日期范围内的唯一日期列表，然后才扩展日期其他属性列。
唯一日日期列表构建方法如下：
- 通过EXCEL表格，填充序列的方式，填充一个指定范围的日期，然后导入到POWER BI中。此方法的优点是简单易行，缺点是不够动态智能。
- 通过POWER QUERY生成日期表
1）在“主页”—“获取数据”—“空白查询”，进行POWER QUERY界面。
2）在查询界面中，选择“主页”—“高级编辑器”，
简单版，输入如下代码：
let
开始日期=#date(2022,1,1),
结束日期=#date(2022,12,31),
日期表={Number.From(开始日期)..Number.From(结束日期)}
in
日期表
接下来将生成的列表转换为表，同时转换为日期数据类型。
动态版，请输入如下代码：
(optional 请输入开始年份 as number,optional 请输入结束年份 as number)=>let
x = 请输入开始年份,
y = if 请输入结束年份 = null then 请输入开始年份 else 请输入结束年份,
begin_date = if x = null then #date(Date.Year(DateTime.LocalNow()),1,1) else #date(x,1,1),
end_date = if y = null then #date(Date.Year(DateTime.LocalNow()),12,31) else #date(y,12,31),
list = {1..Number.From(end_date)-Number.From(begin_date)+1},
dates = List.Transform( list , (item)=> Date.AddDays(begin_date,item-1) ),
table = Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "日期"}}),{{"日期", type date}}),
date_id = Table.TransformColumnTypes(Table.AddColumn(table, "日期序号", each Date.Year([日期])*10000+Date.Month([日期])*100+Date.Day([日期])),{{"日期序号", type number}}),
year_id = Table.AddColumn(date_id, "年序号", each Date.Year([日期]), type number),
year_name = Table.AddColumn(year_id, "年份名称", each "Y"&Text.From([年序号])),
quarter_id = Table.AddColumn(year_name, "季度序号", each Date.QuarterOfYear([日期]), type number),
quarter_name = Table.AddColumn(quarter_id, "季度名称", each "Q"&Text.From([季度序号])),
month_id = Table.AddColumn(quarter_name, "月份序号", each Date.Month([日期]), type number),
month_name = Table.AddColumn(month_id, "月份名称", each "M"&Text.From([月份序号])),
week_id = Table.AddColumn(month_name, "周序号", each Date.WeekOfYear([日期]), type number),
week_name = Table.AddColumn(week_id, "周名称", each "w"&Text.From([周序号])),
year_quarter_id = Table.AddColumn(week_name, "年季序号", each Date.Year([日期])*10+Date.QuarterOfYear([日期]), type number),
year_quarter_name = Table.AddColumn(year_quarter_id, "年季名称", each "YQ"&Text.From([年季序号])),
year_month_id = Table.AddColumn(year_quarter_name, "年月序号", each Date.Year([日期])*100+ Date.Month([日期]), type number),
year_month_name = Table.AddColumn(year_month_id, "年月名称", each "YM"&Text.From([年月序号])),
year_week_id = Table.AddColumn(year_month_name, "年周序号", each Date.Year([日期])*100+ Date.WeekOfYear([日期]), type number),
#"year_week-name" = Table.AddColumn(year_week_id, "年周名称", each "YW"&Text.From([年周序号])),
day_in_week_id = Table.AddColumn(#"year_week-name", "日序号", each Date.DayOfWeek([日期],0), type number),
day_in_week_name = Table.AddColumn(day_in_week_id, "周天名称", each if [日序号] = 1 then "WD1" else
if [日序号] = 2 then "WD2" else
if [日序号] = 3 then "WD3" else
if [日序号] = 4 then "WD4" else
if [日序号] = 5 then "WD5" else
if [日序号] = 6 then "WD6" else
"WD7"),
work_day = Table.AddColumn(day_in_week_name , "工作日", each if [日序号] = 6 or [日序号] = 0 then "休息日" else "工作日" )
in
work_day
3)在调用参数界面，输入开始年份和结束年份就可以得到一张日期表。
3.通过DAX代码生成日期表。
1)在POWER BI界面中，选择“建模”—“新建表”
2)通过CALENDAR函数生成固定日期表
日期表1 = CALENDAR (DATE (2022, 1, 1), DATE (2022, 12, 31))
3)通过CALENDAR函数生成动态日期表
日期表2= CALENDAR (MINX (销售表, [销售日期]), MAXX (销售表, [销售日期]))
根据销售表中的最小销售日期和最大销售日期动态生成一张日期表。
4)CALENDARAUTO函数生成固定日期表：
日期表3 = CALENDARAUTO(12)
CALENDARAUTO返回一个表，其中有一个包含一组连续日期的名为“Date”的列。 日期范围基于模型中的数据自动计算。参数表示日期的结束月份。
注意，使用此函数时，模型中必须要有某个表包含日期列才能创建成功。Excel Power Pivot 的任何一个版本目前都不支持此功能。
扩展日期表
时间智能分析需要通过日期扩展属性，以便有更多的分析维度，这时需要通过增加计算列的方式或DAX代码扩展日期表其他列。
以下代码使用DAX对日期列进行了各种属性扩展。
日期表4 =
VAR vDateLastUpdate = MAXX( ALL('员工表'[入职日期]) , [入职日期] ) // 请修改 '员工表'[入职日期],最大入职日期
// 从最小日期表来进一步构建一个丰富的日期表
VAR vCalendarBase =
AddColumns(
CALENDARAUTO( ) ,
"年份" , YEAR( [Date] ) ,
"季度" , QUARTER( [Date] ) ,
"月份" , MONTH( [Date] ) ,
"日" , DAY( [Date] ) ,
"周" , WEEKNUM( [Date] , 2 ) ,
"星期号" , WEEKDAY( [Date] , 2 )
)
VAR vNumTable =
SELECTCOLUMNS(
{ ( 1 , "一" ) , ( 2 , "二" ) ,( 3 , "三" ) ,( 4 , "四" ) ,( 5 , "五" ) ,
( 6 , "六" ) , ( 7 , "七" ) ,( 8 , "八" ) ,( 9 , "九" ) ,( 10 , "十" ) ,
( 11 , "十一" ) ,( 12 , "十二" )
} , "Num" , [Value1] , "NumCN" , [Value2] )
VAR vCalendarEx =
ADDCOLUMNS( vCalendarBase ,
"中文年" , [年份] & "年" ,
"中文季度" , "季度" & SELECTCOLUMNS( FILTER( vNumTable , [Num] = [季度] ) , "Value" , [NumCN] ) ,
"中文月份" , SELECTCOLUMNS( FILTER( vNumTable , [Num] = [月份] ) , "Value" , [NumCN] ) & "月" ,
"英文月" , FORMAT( [Date] , "mmm" ) ,
"星期" , IF( [星期号] = 7 , "周日" , "周" & SELECTCOLUMNS( FILTER( vNumTable , [Num] = [星期号] ) , "Value" , [NumCN] ) ) ,
-- 其他属性 --
"是否历史数量" , IF( [Date] <= vDateLastUpdate , "是" , "否" ) , // 日期小于最后入职日期
"是否当前月" , IF( [年份] * 100 + [月份] = YEAR( vDateLastUpdate ) * 100 + MONTH( vDateLastUpdate ) , "是" , "否" ) // 与最后日期月份相同
)
RETURN vCalendarEx
标记日期表
如果没有标记日期表，时间智能函数可能在这种情况下不能正常工作。为了更好地利用时间智能函数，必须将日期表标记出来。
在“数据”视图中，选择日期表，然后在“表工具”中选择“标记为日期表”，选择唯一日期列。日期表标记成功。
总结
通过以上步骤，我们准备好了日期表，接下来就可以使用日期智能相关函数对数据进行日期智能分析了。
好啦，恭喜你耐心看完了！看懂了吗？关注我，会有更多关于POWER BI的文章呈现给你。