什么是日期区间表
只有日期表的叫法，参考：日期表，实际上并没有对日期区间表的约定速成的叫法，但日期区间表却是在PowerBI数据建模分析中有非常重要的运用，下面先看一个例子：
可以看出，当选择年份后，这里希望迅速选择对比的粒度是月，周，日其中的一个，并迅速展开对比及可视化观察。所以要求可以直接点击选择来进行，选择周的效果如下：
以及选择日的效果如下：
这里的优势在于：不需要离开纸面，直接选择对比粒度展开对比。这里称可以动态变化的对比粒度为日期区间。
这种日期区间表和日期表显然是不同的，它可以这样理解：
如果从左到右，依次可称为：YearMonthPeriod,YearWeekPeriod以及YearDatePeriod，观察它们的PeriodName字段，名称相同但内容不同，所以CalendarPeriod应该是这三个表的纵向追加合并结果，下面给出构造方法。
用M构建日期区间表
可以自行理解，或者不必理解细节，直接复制粘贴用即可。在PowerBI查询编辑中，新建空查询，然后粘贴如下内容：
let
calendar_period_type = type function (
optional CalendarYearStart as (type number meta [
Documentation.FieldCaption = "开始年份，日期区间表从开始年份1月1日起。",
Documentation.FieldDescription = "日期区间表从开始年份1月1日起",
Documentation.SampleValues = { Date.Year( DateTime.LocalNow( ) ) - 1 } // Current Year
]),
optional CalendarYearEnd as (type number meta [
Documentation.FieldCaption = "结束年份，日期区间表至结束年份12月31日止。",
Documentation.FieldDescription = "日期区间表至结束年份12月31日止",
Documentation.SampleValues = { Date.Year( DateTime.LocalNow( ) ) } // Previous 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 = "CreateCalendarPeriod()",
Result = "当前年份日期区间表。"
],
[
Description = "返回指定年份的日期区间表",
Code = "CreateCalendarPeriod( 2017 )",
Result = "返回2017/01/01至2017/12/31之间的日期区间表。"
],
[
Description = "返回起止年份之间的日期区间表",
Code = "CreateCalendarPeriod( 2015 , 2017 )",
Result = "返回2015/01/01至2017/12/31之间的日期区间表。"
],
[
Description = "返回起止年份之间的日期区间表，并指定周二为每周的第一天",
Code = "CreateCalendarPeriod( 2015 , 2017 , ""Tuesday"" )",
Result = "2015/01/01至2017/12/31之间的日期区间表，且周二是每周的第一天。"
],
[
Description = "返回起止年份之间的日期区间表，并指定周二为每周的第一天，并使用英文显示名称。",
Code = "CreateCalendarPeriod( 2015 , 2017 , ""Tuesday"", ""en"" )",
Result = "2015/01/01至2017/12/31之间的日期区间表，且周二是每周的第一天，并使用英文显示月名称及星期几的名称。"
]
}
],
f_create_calendar_period = (
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 null , // "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 is here:
calendar_list = { Number.From ( #date( Number.From( 2016 ) , 1 , 1 ) ) .. Number.From( #date( Number.From( 2017 ) , 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) ,
// build the period for year-month
period_year_month =
let
#"Inserted PeriodType" = Table.AddColumn(#"Inserted Year", "PeriodType", each "Month" , type text),
#"Inserted Month" = Table.AddColumn(#"Inserted PeriodType", "PeriodNameOrderBy", each Date.Month([Date]), Int64.Type),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "PeriodName", each Date.MonthName([Date]), type text)
in
#"Inserted Month Name",
// build the period for year-week
period_year_week =
let
#"Inserted PeriodType" = Table.AddColumn(#"Inserted Year", "PeriodType", each "Week" , type text),
#"Inserted Week" = Table.AddColumn(#"Inserted PeriodType", "PeriodNameOrderBy", each Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Week Name" = Table.AddColumn(#"Inserted Week", "PeriodName", each "W" & Text.From( [PeriodNameOrderBy] ) , type text)
in
#"Inserted Week Name",
// build the period for year-date
period_year_date =
let
#"Inserted PeriodType" = Table.AddColumn(#"Inserted Year", "PeriodType", each "Day" , type text),
#"Inserted Date" = Table.AddColumn(#"Inserted PeriodType", "PeriodNameOrderBy", each Number.From([Date]) , Int64.Type),
#"Inserted Date Name" = Table.AddColumn(#"Inserted Date", "PeriodName", each Text.From( [Date] ) , type text)
in
#"Inserted Date Name",
combined_month_week_date_period = Table.Combine( { period_year_month , period_year_week , period_year_date } ),
#"Inserted PeriodTypeOderby" = Table.AddColumn( combined_month_week_date_period , "PeriodTypeOrderBy",
each if [PeriodType] = "Month" then 1 else if [PeriodType] = "Week" then 2 else 3 , Int64.Type )
in
#"Inserted PeriodTypeOderby"
in
Value.ReplaceType( f_create_calendar_period , calendar_period_type )
这种构造方式与日期表如出一辙，保留了灵活性可以应对复杂的需求。
这里的M构造的编写使用Visual Studio Code进行，它对编写M提供了实时的提示和着色，有兴趣自己编写M的伙伴可以尝试，如下：
日期表与日期区间表的联动
细心的话可以发现，日期区间表在每一个子表，前述的YearMonthPeriod,YearWeekPeriod以及YearDatePeriod中都存在同样的日期字段，也就是例如2016/01/01会出现3次，那么就无法使用日趋区间表的日期字段与业务事实表来关联，因为业务事实表也会存在多个相同日期，关联将导致直接多对多关系，这是不允许的。
这里可以使用日期表作为桥表，将日期区间表与业务事实表隔离，如下所示：
除了进行隔离，为了使日期区间表可以对业务事实表起到筛选作用，需要启动PowerBI提供的双向筛选器。如此一来，就可以顺利地完成两类任务：
- 使用日期表进行静态日期筛选。
- 使用日期区间表进行动态日期区间筛选。
总结
这里给出了日期表的伴侣日期区间表，它们可以在一起完美地配合工作，为从日期维度进行对比分析起到重要支持。如果发现BUG或更好方法，欢迎一起探讨。
网友评论