新人下载app,立即提现现金 您的好友 送你现金 去提现

Power BI创建日期表的几种方式概览

学谦PowerBI 05-21 22:13 关注

Power BI创建日期表的几种方式概览

几乎所有的报表模型都涉及到日期和时间,因此要创建Power BI报表,日期表就必须得有。虽然最新的Power BI版本已经可以自动为每一个时间列创建日期表。

但这种方式还是存在明显缺点的,一方面如果日期列有两个及以上且分散在不同的table中,无法使用一对多关系来管理这些数据,更何况如果一个table中出现两个时间列(如订单日期和发货日期等)时就无法处理;另一方面,如果数据量特别大,或日期列比较多,自动创建的日期会严重影响性能,因此大部分情况下使用自动智能日期是不合适的。


今天给大家介绍三个创建Power BI日期表的途径,分别对应着一种语言,Excel中的VBA语言,适用于Power BI和PowerPivot的DAX语言,适用于Power BI和PowerQuery的M语言,每一种途径都各有优势和劣势,大家可以视情况而定。


第一种是VBA语言:

直接用excel中的vba语言编写,通过添加简单的按钮可以实现一键创建日期表,并灵活修改起止日期。因为我这个项目的日期有特殊的要求,是截至到当前的,大家需要设置结束日期可以设置一个enddate来控制。


Power BI创建日期表的几种方式概览


Sub date()Dim iDim origin_dateDim ws, w As Worksheet

For Each w In Worksheets If w.Name <> "使用说明" Then Application.DisplayAlerts = False w.Delete Application.DisplayAlerts = True End IfNext

origin_date = Sheets("使用说明").Range("G10")Set ws = Worksheets.Addws.Name = "日期"

ws.Range("A:A").NumberFormatLocal = "YYYY-MM-DD"ws.Cells(1, 1) = "日期"ws.Cells(2, 1) = origin_dateFor 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 ))


Power BI创建日期表的几种方式概览


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 ) )


Power BI创建日期表的几种方式概览


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 ) )


Power BI创建日期表的几种方式概览


这一段代码中并没有指定起止日期,这就是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 星期


Power BI创建日期表的几种方式概览



甚至更加霸道的完美版,参考佐罗老师的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
]),

以上三个途径都有多种表达式写法,追求简单的有简单的做法,追求完美的有完美的方式。当然,一般随着数据的越来越多,模型越来越复杂,对于日期表的需求也会不断地提升,可以适当采用添加列的方式创建更多符合业务需求的格式。


3.M语言是最强大的,通过参数自动化设置想要的各种各样的日期格式,缺点是如果日期表设置不合理,需要重新发布新的报表。

2.DAX是最灵活的,也是日常都在用的,且用CALENDARAUTO函数可以自动识别模型中的最大最小日期,实现自动调整,缺点是需要修改相关标题;

ok,以上就是三个主要的创建日期表的途径,每一种都有自己的优缺点,具体来说1.VBA语言最大的好处是只需要修改原始文件,无需重新发布新的报表,缺点是需要用到另一门语言;



in Value.ReplaceType( CreateCalendar , CalendarType )

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"

) 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之间的日期表,且周二是每周的第一天,并使用英文显示月名称及星期几的名称。" ] } ],

optional CalendarCulture as (type text meta [ Documentation.FieldCaption = "指定日期表显示月以及星期几的名称是中文或英文,en 表示英文,zh 表示中文,缺省默认与系统一致。", Documentation.FieldDescription = " en 表示英文,zh 表示中文,缺省默认与系统一致。", Documentation.SampleValues = { "zh" } ])

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" } ]),

展开阅读全文

查看更多好文内容,新用户限时领现金

暂无评论,打开APP参与讨论

猜你喜欢
查看更多
相关好价
相关商品
查看更多热门商品
社区主页 文章详情
0
0
0
你已经点过赞了
新浪微博 QQ空间 微信好友 豆瓣
当前为触屏版
热门搜索