bi power 两个日期挑较早的日期_PowerBI中构建日期表的终极方法

可以用简单的方式构建日期表，例如：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的构建方式。

如果您有更好的方法，欢迎提供交流。