加入 PowerBI自己学 知识星球 可以：下载源文件，边学边练；遇到问题，提问交流，有问必答。
几乎每个模型都会用到日期表。模型中用不用日期表、如何生成日期表、日期表的列是否具备多功能，都在一定程度上体现了PowerBI报告开发者的专业水平。
首先，用不用日期表。PowerBI具备自动日期/时间功能，能够在后台自动生成一个隐藏的日期表和相应的日期层次结构，但此功能只能满足基本的日期相关分析。
建议在选项菜单中关闭自动日期/时间功能，如果有涉及日期相关计算，用自己生成的多功能日期表。
其次，如何生成日期表。多种方法均可生成日期表，通过源文件导入、PowerQuery、DAX等方法都可以生成日期表。考虑到源文件相对缺少灵活性，DAX不能满足数据加载前调用日期表且DAX计算表占用运行内存可能会影响报表性能，更重要的是生成日期表的过程本身就属于数据获取和清洗的范畴，所以建议在PowerQuery中生成日期表。只需要通过复制粘贴现成的PowerQuery代码，就可以快速生成一个多功能日期表。
最后，日期表中的列是不是多功能，直接影响后期分析需求的实现。本文中的日期表代码支持按年份区间等参数创建日期表，代码生成的日期表包含财年、中英文月名称/星期名称、按刷新时的北京时间生成的年/财年/年季/年月/年周/周/日期的定位序号、当前时点标记（当前年、当前财年、当前季、当前月、当前年周、当前天）等日期表常用列，能够满足大部分日期相关分析需求。
操作步骤
STEP 1 点击菜单栏主页下的获取数据，选择空白查询。
STEP 2 进入PowerQuery后，将复制好的日期表代码（见文末）粘贴到fx公式栏中，然后点击fx前面的对号按钮。
STEP 3 按需输入参数，包括：开始年份、结束年份，建议使用较大的范围满足当前和未来数据的需要，毕竟日期表行数有限，占用的空间很小；财年开始月份，比如2023年10月是新财年的开始，录入10以后，财年列会在2023年10月开始变成FY23/24；每周第一天是星期几，国内通常选择周一，不选择默认就是周一。
STEP 4 点击调用按钮，并为生成的日期表命名，此处命名为Dim_Date。如果后期需要对日期表进行调整，可以选中日期表在fx公式栏中修改参数。
拓展
如果有更多的个性化需求，可以继续修改代码或者在生成的日期表中添加列，比如可以尝试将财年格式FY23/24改为FY2024。日期表代码使用的是M语言，相对简单易读。通过对日期表代码的调校，可以学习很多M语言的基础知识，比如日期函数、格式转换、查询步骤运行机制等。
日期表代码
= let
CalendarType = type function (
optional CalendarYearStart as (type number meta [
Documentation.FieldCaption = "请输入日期表的开始年份",
Documentation.SampleValues = { Date.Year(Date.From(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),8)) ) - 1 }
]),
optional CalendarYearEnd as (type number meta [
Documentation.FieldCaption = "请输入日期表的结束年份",
Documentation.SampleValues = { Date.Year(Date.From(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),8)) ) }
]),
optional CalendarFiscalYearStart as (type number meta [
Documentation.FieldCaption = "请输入财年开始月份，1-12之间的数字，默认为1。",
Documentation.SampleValues = { 1 }
]),
optional CalendarFirstDayOfWeek as (type text meta [
Documentation.FieldCaption = "请输入每周第一天，Monday/Tuesday/Wednesday/Thursday/Friday/Saturday/Sunday，默认为Monday。",
Documentation.SampleValues = { "Monday" }
])
)
astable meta [
Documentation.Name = "使用PowerQuery创建日期表",
Documentation.LongDescription = "按年份区间等参数创建日期表，包含财年、中英文月名称/星期名称、按北京时间刷新生成的年/财年/年季/年月/年周/周/日期的定位序号、当前年、当前财年、当前季、当前月、当前年周、当前天等日期表常用列，可按需要更改此查询中的代码。"
],
CreateCalendar = ( optional CalendarYearStart as number, optionalCalendarYearEnd as number, optional CalendarFiscalYearStart as number, optionalCalendarFirstDayOfWeek as text) => let
begin_year = CalendarYearStart,
end_year = CalendarYearEnd,
begin_fiscalyear = if CalendarFiscalYearStart<>null thenCalendarFiscalYearStart else 1,
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 ifCalendarFirstDayOfWeek <> null then error "参数错误：每周第一天必须是Monday，Tuesday，Wednesday，Thursday，Friday，Saturday，Sunday中的一个。"
else Day.Monday,
today = Date.From(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),8)),
y1 = if begin_year <> null then begin_year else if end_year<> null then end_year else Date.Year( today ) ,
y2 = if end_year <> null then end_year else if begin_year <>null then begin_year else Date.Year( today ) ,
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", typedate}}),
#"Renamed Columns" = Table.RenameColumns(#"ChangedType",{{"Column1", "Date"}}),
#"Added Year" = Table.AddColumn(#"Renamed Columns","Year", each Date.Year([Date]), Int64.Type),
#"Added Quarter" = Table.AddColumn(#"Added Year","Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
#"Added Month" = Table.AddColumn(#"Added Quarter","Month", each Date.Month([Date]), Int64.Type),
#"Added FiscalYear" = Table.AddColumn(#"AddedMonth", "FiscalYear", each if [Month] < begin_fiscalyear then"FY" & Text.End(Text.From([Year]-1),2) & "/" &Text.End(Text.From([Year]),2) else "FY" &Text.End(Text.From([Year]),2) & "/" &Text.End(Text.From([Year]+1),2), type text),
#"Added Week of Year" = Table.AddColumn(#"AddedFiscalYear", "WeekOfYear", each Date.WeekOfYear( [Date] ,first_day_of_week ), Int64.Type),
#"Added Week of Month" = Table.AddColumn(#"Added Week ofYear", "WeekOfMonth", each Date.WeekOfMonth( [Date], first_day_of_week ), Int64.Type),
#"Added Start of Week" = Table.AddColumn(#"Added Week ofMonth", "DateOfWeekStart", each Date.StartOfWeek( [Date],first_day_of_week ), type date),
#"Added End of Week" = Table.AddColumn(#"Added Start ofWeek", "DateOfWeekEnd", each Date.EndOfWeek([Date],first_day_of_week), type date),
#"Added Day" = Table.AddColumn(#"Added End of Week","DayOfMonth", each Date.Day([Date]), Int64.Type),
#"Added Day of Week" = Table.AddColumn(#"Added Day","DayOfWeek", each Date.DayOfWeek( [Date] , first_day_of_week )+1,Int64.Type),
#"Added Day of Year" = Table.AddColumn(#"Added Day ofWeek", "DayOfYear", each Date.DayOfYear([Date]), Int64.Type),
#"Added Year Quarter" = Table.AddColumn(#"Added Day ofYear", "YearQuarter", each [Year] * 100 + [Quarter] ,Int64.Type),
#"Added Year Month" = Table.AddColumn(#"Added YearQuarter", "YearMonth", each [Year] * 100 + [Month] ,Int64.Type),
#"Added Year Week" = Table.AddColumn(#"Added YearMonth", "YearWeek", each [Year] * 100 + [WeekOfYear] ,Int64.Type),
#"Added Date Code" = Table.AddColumn(#"Added YearWeek", "DateCode", each [Year] * 10000 + [Month] * 100 +[DayOfMonth] , Int64.Type),
#"Added DayEN Name" = Table.AddColumn(#"Added DateCode", "DayOfWeekName_EN", each Text.Start(Date.DayOfWeekName([Date] , "en" ),3), type text),
#"Added DayCN Name" = Table.AddColumn(#"Added DayENName", "DayOfWeekName_CN", each Text.Start(Date.DayOfWeekName([Date] , "zh" ),3), type text),
#"Added Year Name" = Table.AddColumn(#"Added DayCNName", "YearName", each "Y" & Text.From( [Year]) , type text),
#"Added Quarter Name" = Table.AddColumn(#"Added YearName", "QuarterName", each "Q" & Text.From([Quarter] ) , type text),
#"Added YearQuarter Name" = Table.AddColumn(#"AddedQuarter Name", "YearQuarterName", each Text.End(Text.From([Year] ),2) & [QuarterName] , type text),
#"Added MonthEN Name" = Table.AddColumn(#"AddedYearQuarter Name", "MonthName_EN", eachText.Start(Date.MonthName( [Date] , "en" ),3), type text),
#"Added MonthCN Name" = Table.AddColumn(#"Added MonthENName", "MonthName_CN", each Text.Start(Date.MonthName( [Date] ,"zh" ),3), type text),
#"Added YearMonth Name" = Table.AddColumn(#"Added MonthCNName", "YearMonthName", each Text.End(Text.From( [Year] ),2)& [MonthName_EN] , type text),
#"Added Week Name" = Table.AddColumn(#"Added YearMonthName", "WeekName", each "W" & Text.From([WeekOfYear] ) , type text),
#"Added YearWeek Name" = Table.AddColumn(#"Added WeekName", "YearWeekName", each Text.End(Text.From( [Year] ),2)& [WeekName] , type text),
#"Added Year Offset" = Table.AddColumn(#"Added YearWeekName", "Year_Offset", each [Year] - Date.Year(today) ,Int64.Type),
#"Added FisacalYear Offset" = Table.AddColumn(#"AddedYear Offset", "FiscalYear_Offset", each if[Month]
#"Added YearQuarter Order" = Table.AddColumn(#"AddedFisacalYear Offset", "YearQuarter_Order", each[Quarter]+4*([Year]-List.Min(#"Added Year" [Year])) , Int64.Type),
#"Added YearQuarter Offset" = Table.AddColumn(#"AddedYearQuarter Order", "YearQuarter_Offset", each[YearQuarter_Order] - (Date.QuarterOfYear(today) + 4*(Date.Year(today)-List.Min(#"AddedYear" [Year]))), Int64.Type),
#"Added YearMonth Order" = Table.AddColumn(#"AddedYearQuarter Offset", "YearMonth_Order", each[Month]+12*([Year]-List.Min(#"Added Year" [Year])) , Int64.Type),
#"Added YearMonth Offset" = Table.AddColumn(#"AddedYearMonth Order", "YearMonth_Offset", each [YearMonth_Order] -(Date.Month(today)+12*(Date.Year(today)-List.Min(#"Added Year"[Year]))) , Int64.Type),
#"Added YearWeek Order" = Table.AddColumn(#"AddedYearMonth Offset", "YearWeek_Order", each[WeekOfYear]+53*([Year]-List.Min(#"Added Year" [Year])) ,Int64.Type),
#"Added YearWeek Offset" = Table.AddColumn(#"AddedYearWeek Order", "YearWeek_Offset", each[YearWeek_Order]-53*(Date.Year(today)-List.Min(#"Added Year"[Year]))-Date.WeekOfYear( today , first_day_of_week ), Int64.Type),
#"Added Week Order" = Table.AddColumn(#"Added YearWeekOffset", "Week_Order", eachDuration.TotalDays([DateOfWeekStart]-List.Min(#"Added Start of Week"[DateOfWeekStart]))/7+1, Int64.Type),
#"Added Week Offset" = Table.AddColumn(#"Added WeekOrder", "Week_Offset", each[Week_Order]-(Duration.TotalDays(Date.StartOfWeek( today, first_day_of_week)-List.Min(#"Added Start of Week" [DateOfWeekStart]))/7+1),Int64.Type),
#"Removed Order Columns" = Table.RemoveColumns(#"AddedWeekOffset",{"YearMonth_Order","YearQuarter_Order","YearWeek_Order","Week_Order"}),
#"Added Date Offset" = Table.AddColumn(#"Removed OrderColumns", "Date_Offset", each Number.From([Date] - today),Int64.Type),
#"Added Year_CurrentYearMark" = Table.AddColumn(#"AddedDate Offset", "Year_CYMark", each if [Year_Offset]=0 then "当前年"
else Text.From([Year]), type text),
#"Added Year_CurrentFicalYearMark" =Table.AddColumn(#"Added Year_CurrentYearMark","FiscalYear_CYMark", each if [FiscalYear_Offset]=0 then "当前财年"
else [FiscalYear], type text),
#"Added YearQuarter_CurrentQuarterMark" =Table.AddColumn(#"Added Year_CurrentFicalYearMark","YearQuarter_CQMark", each if [YearQuarter_Offset]=0 then "当前季"
else Text.From([YearQuarter]), type text),
#"Added YearMonth_CurrentMonthMark" =Table.AddColumn(#"Added YearQuarter_CurrentQuarterMark","YearMonth_CMMark", each if [YearMonth_Offset]=0 then "当前月"
else Text.From([YearMonth]), type text),
#"Added YearWeek_CurrentYearWeekMark" =Table.AddColumn(#"Added YearMonth_CurrentMonthMark","YearWeek_CWMark", each if [YearWeek_Offset]=0 then "当前年周"
else Text.From([YearWeek]), type text),
#"Added Date_CurrentDateMark" = Table.AddColumn(#"AddedYearWeek_CurrentYearWeekMark", "Date_CDMark", each if[Date_Offset]=0 then "当前天" else Text.From([Date]), type
text)
in
#"Added Date_CurrentDateMark"
in
Value.ReplaceType( CreateCalendar , CalendarType )
