可以用簡(jiǎn)單的方式構(gòu)建日期表于颖，例如：Power Query / M 日期表。這種方式對(duì)于一般場(chǎng)景是足夠的酿箭，但并非極致，這里記錄構(gòu)建日期表的終極方案趾娃，目標(biāo)是最大限度的使用M或DAX優(yōu)雅地構(gòu)建一致可維護(hù)的日期表缭嫡。
PowerBI中日期表的構(gòu)建位置可以有兩個(gè)機(jī)會(huì)：
- 在數(shù)據(jù)模型中用DAX構(gòu)建
- 在查詢(xún)編輯中用M構(gòu)建
下面給出構(gòu)建日期表的終極方案。
在日期表的構(gòu)建通常會(huì)考慮一些額外問(wèn)題抬闷，我們會(huì)看到不同方案的對(duì)比妇蛀。
用M構(gòu)建日期表
將以下內(nèi)容復(fù)制到PowerBI查詢(xún)編輯耕突，創(chuàng)建空查詢(xún)并保存為CreateCalendar即可。
更新日志：
2018-04-23 加入中文顯示
let
CalendarType = type function (
optional CalendarYearStart as (type number meta [
Documentation.FieldCaption = "開(kāi)始年份评架，日期表從開(kāi)始年份1月1日起眷茁。",
Documentation.FieldDescription = "日期表從開(kāi)始年份1月1日起",
Documentation.SampleValues = { Date.Year( DateTime.LocalNow( ) ) - 1 } // Previous Year
]),
optional CalendarYearEnd as (type number meta [
Documentation.FieldCaption = "結(jié)束年份，日期表至結(jié)束年份12月31日止纵诞。",
Documentation.FieldDescription = "日期表至結(jié)束年份12月31日止",
Documentation.SampleValues = { Date.Year( DateTime.LocalNow( ) ) } // Current Year
]),
optional CalendarFirstDayOfWeek as (type text meta [
Documentation.FieldCaption = "定義一周開(kāi)始日上祈，從 Monday，Tuesday浙芙，Wednesday登刺，Thursday，F(xiàn)riday嗡呼，Saturday纸俭，Sunday中選擇一個(gè)，缺省默認(rèn)為Monday南窗。",
Documentation.FieldDescription = "從 Monday揍很，Tuesday，Wednesday矾瘾，Thursday女轿，F(xiàn)riday箭启，Saturday壕翩，Sunday中選擇一個(gè)，缺省默認(rèn)為Monday傅寡。",
Documentation.SampleValues = { "Monday" }
]),
optional CalendarCulture as (type text meta [
Documentation.FieldCaption = "指定日期表顯示月以及星期幾的名稱(chēng)是中文或英文放妈，en 表示英文，zh 表示中文荐操，缺省默認(rèn)與系統(tǒng)一致芜抒。",
Documentation.FieldDescription = " en 表示英文，zh 表示中文托启，缺省默認(rèn)與系統(tǒng)一致宅倒。",
Documentation.SampleValues = { "zh" }
])
)
as table meta [
Documentation.Name = "構(gòu)建日期表",
Documentation.LongDescription = "創(chuàng)建指定年份之間的日期表。并可進(jìn)行各種設(shè)置屯耸。",
Documentation.Examples = {
[
Description = "返回當(dāng)前年份日期表",
Code = "CreateCalendar()",
Result = "當(dāng)前年份日期表拐迁。"
],
[
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 = "返回起止年份之間的日期表缓淹，并指定周二為每周的第一天，并使用英文顯示名稱(chēng)。",
Code = "CreateCalendar( 2015 , 2017 , ""Tuesday"", ""en"" )",
Result = "2015/01/01至2017/12/31之間的日期表讯壶，且周二是每周的第一天料仗，并使用英文顯示月名稱(chēng)及星期幾的名稱(chēng)。"
]
}
],
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 "參數(shù)錯(cuò)誤：參數(shù)CalendarFirstDayOfWeek必須是Monday伏蚊，Tuesday罢维，Wednesday，Thursday丙挽，F(xiàn)riday肺孵，Saturday，Sunday中的一個(gè)颜阐。"
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", "周開(kāi)始日期"}, {"DateOfWeekEnd", "周結(jié)束日期"}, {"DayOfWeek", "周天"}, {"DayOfYear", "年日"}, {"DayOfWeekName", "星期幾名稱(chēng)"}, {"YearName", "年份名稱(chēng)"}, {"QuarterName", "季度名稱(chēng)"}, {"MonthName", "月份名稱(chēng)"}, {"WeekName", "周名稱(chēng)"}, {"YearQuarter", "年季"}, {"YearMonth", "年月"}, {"YearWeek", "年周"}, {"DateCode", "日期碼"}})
else #"Inserted Date Code"
in
Value.ReplaceType( CreateCalendar , CalendarType )
這里看上去比普通的構(gòu)建日期表的方式復(fù)雜了一些平窘，但它盡量考慮了幾乎能考慮到的一切。我們看看效果：
可以看出這種方法給出了四個(gè)參數(shù)來(lái)構(gòu)建靈活的日期表：
- 默認(rèn)構(gòu)建本年度的
- 可以構(gòu)建指定年份之間的
- 可以指定每周開(kāi)始的日期
- 可以指定按中文或英文顯示名稱(chēng)
另外凳怨，這里充分地使用了M的元數(shù)據(jù)功能瑰艘，可以用來(lái)做函數(shù)的提示并帶有完整的示例，代碼中還做了有效性校驗(yàn)防止用戶(hù)輸入不合理的數(shù)據(jù)肤舞，例如：
M代碼格式做了嚴(yán)格的調(diào)校紫新，不失為理解M的一個(gè)案例。
用DAX構(gòu)建日期表
當(dāng)然還可以在數(shù)據(jù)建模的時(shí)候使用DAX來(lái)構(gòu)建日期表李剖，一種方便的方法是創(chuàng)建計(jì)算表并使用如下DAX表達(dá)式：
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構(gòu)建日期表一樣的命名方式芒率，以兼容兩個(gè)日期表的字段名稱(chēng)。
總結(jié)
通過(guò)對(duì)比上述兩種構(gòu)建日期表的方式篙顺，考慮優(yōu)先使用M的構(gòu)建方式偶芍。
如果您有更好的方法，歡迎提供交流德玫。
