PowerBI中的函数日期表
在PowerBI中，日期表的问题是始终都无法绕过的一个问题，首先是微软默认的日期表月份显示如下：
可能这种形式我们自己看起来没啥的，但是要考虑做出来的可视化报表呈现的对象绝对不仅仅是自己，那么就需要我们自己来制作自己的日期维度表。
太多方法就不说了，这里提供两种方法大家参考：
一、M函数：
1、在PQ编辑器界面，左边查询的位置，点击鼠标右键，新建空白查询：
2、点击操作界面的上方的高级属性编辑器：
3、将查询里面所有的内容清空，复制下面这段代码进去：
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 )
4、之后会变成如下界面，可以设置起始日期等参数：
5、选择调用：
这是方法一，生成了一个带有中文月份的日期表。
§§
二、表函数：
1、在建模窗口下，选择新建表格：
2、修改表名之后，复制如下代码：
日期表 =
GENERATE (
CALENDAR ( MIN ( '事实表'[日期] ), MAX ( '事实表'[日期] ) ),
VAR DA = [Date]
VAR YEAR =
YEAR ( DA )
VAR QUARTER =
"季度" & FORMAT ( DA, "Q" )
VAR MONTE =
FORMAT ( DA, "MM" ) & "月"
VAR DAY =
DAY ( DA )
VAR WEEKID =
WEEKDAY ( DA, 2 )
RETURN
ROW (
"年度", YEAR,
"季度", QUARTER,
"月份", MONTE,
"日", DAY,
"年度季度", YEAR & QUARTER,
"年度月份", YEAR & MONTE,
"星期", WEEKID
)
)
3、由于没有数据，用之前M函数生成的例子修改代码如下图：
结果如下图：
﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌
两种方法都可以生成带有中文字样的日期表，区别在于：
一、M函数生成的日期表是固定的，静态的日期表。
二、表函数运用了MIN/MAX，这样生成出来的日期表是随着事实表而变动的动态日期表。
推荐大家选择第二个。
本期就到这里，我是白茶，一个PowerBI的初学者，偶尔会不定时更新一些自己的学习心得。
QQ群：772646751
小伙伴们，GET了么？
白茶会不定期的分享一些函数卡片哦。
（文件在知识星球[PowerBI丨需求圈]）
这里是白茶，一个PowerBI的初学者。
下面这个知识星球是针对有实际需求的小伙伴，有需要的请加入下面的知识星球。
（这个星球里面有白茶之前所有的案例文件。）
Fabric丨白茶 文章被收录于专栏
数据分析进阶之路，带你深入了解可视化技巧。
投递小米集团等公司10个岗位 >
