可以用简单的方式构建日期表，例如：Power Query / M 日期表。这种方式对于一般场景是足够的，但并非极致，这里记录构建日期表的终极方案，目标是最大限度的使用M或DAX优雅地构建一致可维护的日期表。
PowerBI中日期表的构建位置可以有两个机会：
- 在数据模型中用DAX构建
- 在查询编辑中用M构建
下面给出构建日期表的终极方案。
在日期表的构建通常会考虑一些额外问题，我们会看到不同方案的对比。
用M构建日期表
将以下内容复制到PowerBI查询编辑，创建空查询并保存为CreateCalendar即可。
更新日志：
2018-04-23 加入中文显示
let
CalendarType = type function (
optional CalendarYearStart as (type number meta [
Documentation.FieldCaption = "开始年份，日期表从开始年份1月1日起。",
Documentation.FieldDescription = "日期表从开始年份1月1日起",
Documentation.SampleValues = { Date.Year( DateTime.LocalNow( ) ) - 1 } // Previous Year
]),
optional CalendarYearEnd as (type number meta [
Documentation.FieldCaption = "结束年份，日期表至结束年份12月31日止。",
Documentation.FieldDescription = "日期表至结束年份12月31日止",
Documentation.SampleValues = { Date.Year( DateTime.LocalNow( ) ) } // Current Year
]),
optional CalendarFirstDayOfWeek as (type text meta [
Documentation.FieldCaption = "定义一周开始日，从 Monday，Tuesday，Wednesday，Thursday，Friday，Saturday，Sunday中选择一个，缺省默认为Monday。",
Documentation.FieldDescription = "从 Monday，Tuesday，Wednesday，Thursday，Friday，Saturday，Sunday中选择一个，缺省默认为Monday。",
Documentation.SampleValues = { "Monday" }
]),
optional CalendarCulture as (type text meta [
Documentation.FieldCaption = "指定日期表显示月以及星期几的名称是中文或英文，en 表示英文，zh 表示中文，缺省默认与系统一致。",
Documentation.FieldDescription = " en 表示英文，zh 表示中文，缺省默认与系统一致。",
Documentation.SampleValues = { "zh" }
])
)
as table meta [
Documentation.Name = "构建日期表",
Documentation.LongDescription = "创建指定年份之间的日期表。并可进行各种设置。",
Documentation.Examples = {
[
Description = "返回当前年份日期表",
Code = "CreateCalendar()",
Result = "当前年份日期表。"
],
[
Description = "返回指定年份的日期表",
Code = "CreateCalendar( 2017 )",
Result = "返回2017/01/01至2017/12/31之间的日期表。"
],
[
Description = "返回起止年份之间的日期表",
Code = "CreateCalendar( 2015 , 2017 )",
Result = "返回2015/01/01至2017/12/31之间的日期表。"
],
[
Description = "返回起止年份之间的日期表，并指定周二为每周的第一天",
Code = "CreateCalendar( 2015 , 2017 , ""Tuesday"" )",
Result = "2015/01/01至2017/12/31之间的日期表，且周二是每周的第一天。"
],
[
Description = "返回起止年份之间的日期表，并指定周二为每周的第一天，并使用英文显示名称。",
Code = "CreateCalendar( 2015 , 2017 , ""Tuesday"", ""en"" )",
Result = "2015/01/01至2017/12/31之间的日期表，且周二是每周的第一天，并使用英文显示月名称及星期几的名称。"
]
}
],
CreateCalendar = ( optional CalendarYearStart as number, optional CalendarYearEnd as number, optional CalendarFirstDayOfWeek as text, optional CalendarCulture as text) => let
begin_year = CalendarYearStart ,
end_year = CalendarYearEnd ,
first_day_of_week = if Text.Lower( CalendarFirstDayOfWeek ) = "monday" then Day.Monday
else if Text.Lower( CalendarFirstDayOfWeek ) = "tuesday" then Day.Tuesday
else if Text.Lower( CalendarFirstDayOfWeek ) = "wednesday" then Day.Wednesday
else if Text.Lower( CalendarFirstDayOfWeek ) = "thursday" then Day.Thursday
else if Text.Lower( CalendarFirstDayOfWeek ) = "friday" then Day.Friday
else if Text.Lower( CalendarFirstDayOfWeek ) = "saturday" then Day.Saturday
else if Text.Lower( CalendarFirstDayOfWeek ) = "sunday" then Day.Sunday
else if CalendarFirstDayOfWeek <> null then error "参数错误：参数CalendarFirstDayOfWeek必须是Monday，Tuesday，Wednesday，Thursday，Friday，Saturday，Sunday中的一个。"
else Day.Monday ,
culture = if CalendarCulture <> null then CalendarCulture else "zh" , // "en" , "zh"
y1 = if begin_year <> null then begin_year else if end_year <> null then end_year else Date.Year( DateTime.LocalNow() ) ,
y2 = if end_year <> null then end_year else if begin_year <> null then begin_year else Date.Year( DateTime.LocalNow() ) ,
calendar_list = { Number.From ( #date( Number.From( y1 ) , 1 , 1 ) ) .. Number.From( #date( Number.From( y2 ) , 12, 31 ) ) },
calendar_list_table = Table.FromList(calendar_list, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(calendar_list_table,{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"Inserted Year" = Table.AddColumn(#"Renamed Columns", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Year", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Quarter", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Month", "WeekOfYear", each Date.WeekOfYear( [Date] , first_day_of_week ), Int64.Type),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "WeekOfMonth", each Date.WeekOfMonth( [Date] ), Int64.Type),
#"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "DateOfWeekStart", each Date.StartOfWeek( [Date] ), type date),
#"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "DateOfWeekEnd", each Date.EndOfWeek([Date]), type date),
#"Inserted Day" = Table.AddColumn(#"Inserted End of Week", "DayOfMonth", each Date.Day([Date]), Int64.Type),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "DayOfWeek", each Date.DayOfWeek( [Date] , first_day_of_week ), Int64.Type),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "DayOfYear", each Date.DayOfYear([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "DayOfWeekName", each Date.DayOfWeekName( [Date] , culture ), type text),
#"Inserted Year Name" = Table.AddColumn(#"Inserted Day Name", "YearName", each "Y" & Text.From( [Year] ) , type text ),
#"Inserted Quarter Name" = Table.AddColumn(#"Inserted Year Name", "QuarterName", each "Q" & Text.From( [Quarter] ) , type text ),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Quarter Name", "MonthName", each Date.MonthName( [Date] , culture ), type text),
#"Inserted Week Name" = Table.AddColumn(#"Inserted Month Name", "WeekName", each "W" & Text.From( [WeekOfYear] ) , type text ),
#"Inserted Year Quarter" = Table.AddColumn(#"Inserted Week Name", "YearQuarter", each [Year] * 100 + [Quarter] , Int64.Type ),
#"Inserted Year Month" = Table.AddColumn(#"Inserted Year Quarter", "YearMonth", each [Year] * 100 + [Month] , Int64.Type ),
#"Inserted Year Week" = Table.AddColumn(#"Inserted Year Month", "YearWeek", each [Year] * 100 + [WeekOfYear] , Int64.Type ),
#"Inserted Date Code" = Table.AddColumn(#"Inserted Year Week", "DateCode", each [Year] * 10000 + [Month] * 100 + [DayOfMonth] , Int64.Type )
in
if culture = "zh"
then Table.RenameColumns( #"Inserted Date Code" ,{{"Date", "日期"}, {"Year", "年"}, {"Quarter", "季"}, {"Month", "月"}, {"WeekOfYear", "周"}, {"WeekOfMonth", "月周"}, {"DayOfMonth", "月日"}, {"DateOfWeekStart", "周开始日期"}, {"DateOfWeekEnd", "周结束日期"}, {"DayOfWeek", "周天"}, {"DayOfYear", "年日"}, {"DayOfWeekName", "星期几名称"}, {"YearName", "年份名称"}, {"QuarterName", "季度名称"}, {"MonthName", "月份名称"}, {"WeekName", "周名称"}, {"YearQuarter", "年季"}, {"YearMonth", "年月"}, {"YearWeek", "年周"}, {"DateCode", "日期码"}})
else #"Inserted Date Code"
in
Value.ReplaceType( CreateCalendar , CalendarType )
这里看上去比普通的构建日期表的方式复杂了一些，但它尽量考虑了几乎能考虑到的一切。我们看看效果：
可以看出这种方法给出了四个参数来构建灵活的日期表：
- 默认构建本年度的
- 可以构建指定年份之间的
- 可以指定每周开始的日期
- 可以指定按中文或英文显示名称
另外，这里充分地使用了M的元数据功能，可以用来做函数的提示并带有完整的示例，代码中还做了有效性校验防止用户输入不合理的数据，例如：
M代码格式做了严格的调校，不失为理解M的一个案例。
用DAX构建日期表
当然还可以在数据建模的时候使用DAX来构建日期表，一种方便的方法是创建计算表并使用如下DAX表达式：
Calendar =
------------------------------------------------------------------------
VAR WeekNumberFlag = 2 // 1 - sunday , 2 - monday
// 1 - Sunday (1) and ends on Saturday (7). numbered 1 through 7.
// 2 - Monday (1) and ends on Sunday (7).
// 3 - Monday (0) and ends on Sunday (6).numbered 1 through 7
VAR WeekDayFlag = 2
VAR CalendarYearStart = 2016
VAR CalendarYearEnd = 2017
-------------------------------------------------------------------------
VAR CalendarBase = CALENDAR( DATE( CalendarYearStart , 1 , 1 ) , DATE( CalendarYearEnd , 12 , 31 ) )
RETURN
GENERATE (
CalendarBase,
VAR CalendarCurrentDate = [Date]
VAR CalendarYear = YEAR ( CalendarCurrentDate )
VAR CalendarMonth = MONTH ( CalendarCurrentDate )
VAR CalendarQuarter = SWITCH( TRUE() ,
CalendarMonth <= 3 , 1 ,
CalendarMonth <= 6 , 2 ,
CalendarMonth <= 9 , 3 ,
4
)
VAR CalendarYearMonth = CalendarYear * 100 + CalendarMonth
VAR CalendarYearWeek = CalendarYear * 100 + WEEKNUM( CalendarCurrentDate )
VAR CalendarDayOfWeek = WEEKDAY( CalendarCurrentDate , WeekDayFlag )
VAR CalendarWeekOfYear = WEEKNUM( CalendarCurrentDate , WeekNumberFlag )
VAR CalendarDayOfMonth = DAY( CalendarCurrentDate )
RETURN ROW (
"Year" , CalendarYear ,
"Quarter" , CalendarQuarter ,
"Month" , CalendarMonth ,
"WeekOfYear" , CalendarWeekOfYear ,
"DayOfMonth" , CalendarDayOfMonth ,
"DayOfWeek" , CalendarDayOfWeek ,
"DayOfWeekName" , FORMAT( CalendarCurrentDate , "aaaa" ) ,
"YearName" , "Y" & CalendarYear ,
"QuarterName" , "Q" & CalendarQuarter ,
"MonthName", FORMAT ( CalendarCurrentDate, "mmm" ) ,
"WeekName", "W" & CalendarWeekOfYear ,
"YearQuarter", CalendarYear * 100 + CalendarQuarter ,
"YearMonth" , CalendarYearMonth ,
"YearWeek" , CalendarYear * 100 + CalendarWeekOfYear ,
"DateCode" , CalendarYear * 10000 + CalendarMonth * 100 + CalendarDayOfMonth
)
)
这里使用了与上述M构建日期表一样的命名方式，以兼容两个日期表的字段名称。
总结
通过对比上述两种构建日期表的方式，考虑优先使用M的构建方式。
如果您有更好的方法，欢迎提供交流。
