|
Power BI创建日期表的几种方式概览 几乎所有的报表模型都涉及到日期和时间，因此要创建Power BI报表，日期表就必须得有。虽然最新的Power BI版本已经可以自动为每一个时间列创建日期表。 但这种方式还是存在明显缺点的，一方面如果日期列有两个及以上且分散在不同的table中，无法使用一对多关系来管理这些数据，更何况如果一个table中出现两个时间列（如订单日期和发货日期等）时就无法处理；另一方面，如果数据量特别大，或日期列比较多，自动创建的日期会严重影响性能，因此大部分情况下使用自动智能日期是不合适的。 今天给大家介绍三个创建Power BI日期表的途径，分别对应着一种语言，Excel中的VBA语言，适用于Power BI和PowerPivot的DAX语言，适用于Power BI和PowerQuery的M语言，每一种途径都各有优势和劣势，大家可以视情况而定。 第一种是VBA语言： 直接用excel中的vba语言编写，通过添加简单的按钮可以实现一键创建日期表，并灵活修改起止日期。因为我这个项目的日期有特殊的要求，是截至到当前的，大家需要设置结束日期可以设置一个enddate来控制。
Sub date() Dim i Dim origin_date Dim ws, w As Worksheet For Each w In Worksheets If w.Name <> "使用说明" Then Application.DisplayAlerts = False w.Delete Application.DisplayAlerts = True End If Next origin_date = Sheets("使用说明").Range("G10") Set ws = Worksheets.Add ws.Name = "日期" ws.Range("A:A").NumberFormatLocal = "YYYY-MM-DD" ws.Cells(1, 1) = "日期" ws.Cells(2, 1) = origin_date For i = 3 To DateDiff("d", origin_date, Now) + 1 ws.Cells(i, 1) = ws.Cells(i - 1, 1) + 1 Next i ActiveWorkbook.SaveAs Path & "\date.xlsx", FileFormat:=xlWorkbookDefault End Sub 使用VBA来编写日期表的最大好处是完全不需要修改pbix文件，尤其是对于在线自动刷新的报表，将连接的日期表修改后，网关自动刷新，而无需重新发布报表。 第二种是DAX语言： 这是使用Power BI绕不过去的坎，需要人人掌握的。利用DAX生成日期表，使用几个不同的函数都可以做到，常用的有以下几种组合： 1、ADDCOLUMNS与CALENDAR函数： 日期表1 = ADDCOLUMNS ( CALENDAR (DATE(2017,1,1), DATE(2019,12,31)), "年度", YEAR ( [Date] ), "季度", "Q" & FORMAT ( [Date], "Q" ), "月份", FORMAT ( [Date], "MM" ), "日",FORMAT ( [Date], "DD" ), "年度季度", FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" ), "年度月份", FORMAT ( [Date], "YYYY/MM" ), "星期几", WEEKDAY ( [Date],2 )
2、GENERATE和CALENDAR函数 日期表2= GENERATE ( CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2019, 12, 31 ) ), VAR currentDay = [Date] VAR year = YEAR ( currentDay ) VAR quarter = "Q" & FORMAT ( currentDay, "Q" ) VAR month = FORMAT ( currentDay, "MM" ) VAR day = DAY( currentDay ) VAR weekid = WEEKDAY ( currentDay,2) RETURN ROW ( "年度", year , "季度",quarter, "月份", month, "日", day, "年度季度", year&quarter, "年度月份", year&month, "星期几", weekid
3、GENERATE与CALENDARAUTO函数 日期表3= GENERATE ( CALENDARAUTO(), VAR currentDay = [Date] VAR year = YEAR ( currentDay ) VAR quarter = "Q" & FORMAT ( currentDay, "Q" ) VAR month = FORMAT ( currentDay, "MM" ) VAR day = DAY( currentDay ) VAR weekid = WEEKDAY ( currentDay,2) RETURN ROW ( "年度", year , "季度",quarter, "月份", month, "日", day, "年度季度", year&quarter, "年度月份", year&month, "星期几", weekid
这一段代码中并没有指定起止日期，这就是CALENDARAUTO函数的厉害之处，它可以自动检测模型中其他表中所有日期，然后生成涵盖这些日期的整年日期表。 而且如果模型中其他表的日期范围发生变动，这个日期表也会自动更新到新的日期范围，利用CALENDARAUTO可以很轻松的制作一个动态的日期表。 使用上面三种DAX函数生成日期表还有一个小小的遗憾，就是CALENDAR函数生成的日期列字段名都是英文的[Date]，而其他列都是中文，不过可以在生成日期表后进行手动更改，这个比较简单。第三种方法是使用M语言：对于很多Power BI使用者来说，尤其是没有接触过PowerQuery的人来说，M语言比较少用，也比较难一些，在这里直接给出表达式，复制粘贴即可。首先创建两个参数，kaishiDate和jieshuDate来确定起始日期和结束日期，然后在查询编辑器中，新建一个空查询，打开高级编辑器，粘贴以下代码，回车即可。let 日期序列= {Number.From(kaishiDate)..Number.From(jieshuDate)}, 转换为表= Table.FromList(日期序列, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 更改的类型= Table.TransformColumnTypes(转换为表,{{"Column1", type date}}), 重命名的列= Table.RenameColumns(更改的类型,{{"Column1", "日期ID"}}), 年= Table.AddColumn(重命名的列, "年份序号", each Date.Year([日期ID]),type number), 月= Table.AddColumn(年, "月", each Date.Month([日期ID]),type number), 月份名称= Table.AddColumn(月, "月份名称", each Date.ToText([日期ID],"M月"),type text), 年月序号= Table.AddColumn(月份名称, "年月序号", each Date.ToText([日期ID],"yyyyMM"),type number), 季度序号= Table.AddColumn(年月序号, "季度序号", each Date.QuarterOfYear([日期ID]),type number), 日= Table.AddColumn(季度序号, "日", each Date.Day([日期ID]),type number), 星期= Table.AddColumn(日, "星期", each Date.DayOfWeek([日期ID],0),type number) in 星期
甚至更加霸道的完美版，参考佐罗老师的erBI战友联盟的文章，可以直接调用函数来创建，并且可以自定义设置。 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 ) ok，以上就是三个主要的创建日期表的途径，每一种都有自己的优缺点，具体来说 1.VBA语言最大的好处是只需要修改原始文件，无需重新发布新的报表，缺点是需要用到另一门语言； 2.DAX是最灵活的，也是日常都在用的，且用CALENDARAUTO函数可以自动识别模型中的最大最小日期，实现自动调整，缺点是需要修改相关标题； 3.M语言是最强大的，通过参数自动化设置想要的各种各样的日期格式，缺点是如果日期表设置不合理，需要重新发布新的报表。 以上三个途径都有多种表达式写法，追求简单的有简单的做法，追求完美的有完美的方式。当然，一般随着数据的越来越多，模型越来越复杂，对于日期表的需求也会不断地提升，可以适当采用添加列的方式创建更多符合业务需求的格式。 |
|
|
