在PowerBI中基于日期进行相对化的分析至少需要点击3次，甚至还要输入1次，如下：
这种交互体验不够简单，所以实现简单常用的相对日期如下：
相对日期
Power Query M 实现方法
let
CalendarPeriodRelativeType = type function (
optional TheDateRelative as (type date meta [
Documentation.FieldCaption = "相对日期",
Documentation.FieldDescription = "如果为空，默认为今日",
Documentation.SampleValues = { Date.From( DateTime.LocalNow( ) ) }
]),
optional IncludingTheDateRelative as (type logical meta [
Documentation.FieldCaption = "是否包括该日期",
Documentation.FieldDescription = "如果为空，默认为包括",
Documentation.SampleValues = { true }
]),
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" }
])
)
as table meta [
Documentation.Name = "构建相对日期区间表",
Documentation.LongDescription = "创建以指定日期为基准的相对日期区间。并可进行各种设置。",
Documentation.Examples = {
[
Description = "返回以当前日期为基准的相对日期区间表",
Code = "CreateCalendarPeriodRelative()",
Result = "返回以当前日期为基准的相对日期区间表"
],
[
Description = "返回以某日期为基准的相对日期区间表",
Code = "CreateCalendarPeriodRelative( #date(2017,2,1) )",
Result = "返回以 2017/02/01 为基准的相对日期区间表。"
],
[
Description = "返回以某日期为基准的相对日期区间表",
Code = "CreateCalendarPeriodRelative( #date(2017,2,1) , false )",
Result = "返回以 2017/02/01 为基准的相对日期区间表，且日期区间不包括该日期。"
],
[
Description = "返回以某日期为基准的相对日期区间表",
Code = "CreateCalendarPeriodRelative( #date(2017,2,1) , false , ""Tuesday"")",
Result = "返回以 2017/02/01 为基准的相对日期区间表，且日期区间不包括该日期，且周二是每周的第一天。"
]
}
],
fCreateCalendarPeriodRelative = (
optional TheDateRelative as date,
optional IncludingTheDateRelative as logical,
optional CalendarFirstDayOfWeek as text ) =>
let
IncludingTheDate = if IncludingTheDateRelative is null then true else IncludingTheDateRelative,
TheDate =
if TheDateRelative is null
then Date.AddDays( Date.From( DateTime.LocalNow() ) , if IncludingTheDate then 0 else -1 )
else Date.AddDays( TheDateRelative , if IncludingTheDate then 0 else -1 ) ,
YearStart = Date.Year( TheDate ) - 1 ,
YearEnd = Date.Year( TheDate ) ,
FirstDayOfWeek = 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 ,
// calendar list is here:
CalendarList = { Number.From ( #date( Number.From( YearStart ) , 1 , 1 ) ) .. Number.From( #date( Number.From( YearEnd ) , 12, 31 ) ) },
CalendarListTable = Table.FromList(CalendarList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes( CalendarListTable ,{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
Source = #"Renamed Columns",
// build the Current Date
CurrentDateTable =
let
DateStart = TheDate ,
DateEnd = TheDate ,
#"Added Custom" = Table.AddColumn(Source, "PeriodName", each if [Date] >= DateStart and [Date] <= List.Min( { DateEnd , TheDate } ) then "CurrentDate" else null , type text),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([PeriodName] <> null)),
#"Added PeriodNameCN" = Table.AddColumn(#"Filtered Rows", "PeriodNameCN", each "本日" , type text),
#"Added PeriodTypeName" = Table.AddColumn(#"Added PeriodNameCN", "PeriodTypeName", each "Current", type text),
#"Added PeriodTypeNameCN" = Table.AddColumn(#"Added PeriodTypeName", "PeriodTypeNameCN", each "本期", type text),
#"Added PeriodTypeNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameCN", "PeriodTypeNameOrderBy", each 1 , Int64.Type ),
#"Added PeriodNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameOrderBy", "PeriodNameOrderBy", each 1 , Int64.Type )
in
#"Added PeriodNameOrderBy",
// build the Previous Date
PreviousDateTable =
let
DateStart = Date.AddDays( TheDate , -1 ) ,
DateEnd = Date.AddDays( TheDate , -1 ) ,
#"Added Custom" = Table.AddColumn(Source, "PeriodName", each if [Date] >= DateStart and [Date] <= List.Min( { DateEnd , TheDate } ) then "PreviousDate" else null , type text),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([PeriodName] <> null)),
#"Added PeriodNameCN" = Table.AddColumn(#"Filtered Rows", "PeriodNameCN", each "昨日" , type text),
#"Added PeriodTypeName" = Table.AddColumn(#"Added PeriodNameCN", "PeriodTypeName", each "Previous", type text),
#"Added PeriodTypeNameCN" = Table.AddColumn(#"Added PeriodTypeName", "PeriodTypeNameCN", each "上期", type text),
#"Added PeriodTypeNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameCN", "PeriodTypeNameOrderBy", each 2 , Int64.Type ),
#"Added PeriodNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameOrderBy", "PeriodNameOrderBy", each 1 , Int64.Type )
in
#"Added PeriodNameOrderBy",
// build the Current Week
CurrentWeekTable =
let
DateStart = Date.StartOfWeek( TheDate , FirstDayOfWeek ) ,
DateEnd = Date.EndOfWeek( TheDate , FirstDayOfWeek ) ,
#"Added Custom" = Table.AddColumn(Source, "PeriodName", each if [Date] >= DateStart and [Date] <= List.Min( { DateEnd , TheDate } ) then "CurrentWeek" else null , type text),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([PeriodName] <> null)),
#"Added PeriodNameCN" = Table.AddColumn(#"Filtered Rows", "PeriodNameCN", each "本周" , type text),
#"Added PeriodTypeName" = Table.AddColumn(#"Added PeriodNameCN", "PeriodTypeName", each "Current", type text),
#"Added PeriodTypeNameCN" = Table.AddColumn(#"Added PeriodTypeName", "PeriodTypeNameCN", each "本期", type text),
#"Added PeriodTypeNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameCN", "PeriodTypeNameOrderBy", each 1 , Int64.Type ),
#"Added PeriodNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameOrderBy", "PeriodNameOrderBy", each 2 , Int64.Type )
in
#"Added PeriodNameOrderBy",
// build the Previous Week
PreviousWeekTable =
let
DateStart = Date.StartOfWeek( Date.AddDays( TheDate , -7 ) , FirstDayOfWeek ) ,
DateEnd = Date.EndOfWeek( Date.AddDays( TheDate , -7 ) , FirstDayOfWeek ) ,
#"Added Custom" = Table.AddColumn(Source, "PeriodName", each if [Date] >=DateStart and [Date] <= DateEnd then "PreviousWeek" else null , type text),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([PeriodName] <> null)),
#"Added PeriodNameCN" = Table.AddColumn(#"Filtered Rows", "PeriodNameCN", each "上周" , type text),
#"Added PeriodTypeName" = Table.AddColumn(#"Added PeriodNameCN", "PeriodTypeName", each "Previous", type text),
#"Added PeriodTypeNameCN" = Table.AddColumn(#"Added PeriodTypeName", "PeriodTypeNameCN", each "上期", type text),
#"Added PeriodTypeNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameCN", "PeriodTypeNameOrderBy", each 2 , Int64.Type ),
#"Added PeriodNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameOrderBy", "PeriodNameOrderBy", each 2 , Int64.Type )
in
#"Added PeriodNameOrderBy",
// build the Current Month
CurrentMonthTable =
let
DateStart = Date.StartOfMonth( TheDate ) ,
DateEnd = Date.EndOfMonth( TheDate ) ,
#"Added Custom" = Table.AddColumn(Source, "PeriodName", each if [Date] >=DateStart and [Date] <= List.Min( { DateEnd , TheDate } ) then "CurrentMonth" else null , type text),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([PeriodName] <> null)),
#"Added PeriodNameCN" = Table.AddColumn(#"Filtered Rows", "PeriodNameCN", each "本月" , type text),
#"Added PeriodTypeName" = Table.AddColumn(#"Added PeriodNameCN", "PeriodTypeName", each "Current", type text),
#"Added PeriodTypeNameCN" = Table.AddColumn(#"Added PeriodTypeName", "PeriodTypeNameCN", each "本期", type text),
#"Added PeriodTypeNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameCN", "PeriodTypeNameOrderBy", each 1 , Int64.Type ),
#"Added PeriodNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameOrderBy", "PeriodNameOrderBy", each 3 , Int64.Type )
in
#"Added PeriodNameOrderBy",
// build the Previous Month
PreviousMonthTable =
let
DateStart = Date.StartOfMonth( Date.AddMonths( TheDate , -1 ) ) ,
DateEnd = Date.EndOfMonth( Date.AddMonths( TheDate , -1 ) ) ,
#"Added Custom" = Table.AddColumn(Source, "PeriodName", each if [Date] >=DateStart and [Date] <= List.Min( { DateEnd , TheDate } ) then "PreviousMonth" else null , type text),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([PeriodName] <> null)),
#"Added PeriodNameCN" = Table.AddColumn(#"Filtered Rows", "PeriodNameCN", each "上月" , type text),
#"Added PeriodTypeName" = Table.AddColumn(#"Added PeriodNameCN", "PeriodTypeName", each "Previous", type text),
#"Added PeriodTypeNameCN" = Table.AddColumn(#"Added PeriodTypeName", "PeriodTypeNameCN", each "上期", type text),
#"Added PeriodTypeNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameCN", "PeriodTypeNameOrderBy", each 2 , Int64.Type ),
#"Added PeriodNameOrderBy" = Table.AddColumn(#"Added PeriodTypeNameOrderBy", "PeriodNameOrderBy", each 3 , Int64.Type )
in
#"Added PeriodNameOrderBy",
// combine table parts
CombinedTable = Table.Combine( { CurrentDateTable , PreviousDateTable , CurrentWeekTable , PreviousWeekTable , CurrentMonthTable , PreviousMonthTable } )
in
CombinedTable
in
Value.ReplaceType( fCreateCalendarPeriodRelative , CalendarPeriodRelativeType )
使用方法
复制上述M代码直接使用即可，如下：
即可。
网友评论