欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

PowerBI中的函数日期表

程序员文章站 2022-05-10 14:56:07
...

PowerBI中的函数日期表
在PowerBI中，日期表的问题是始终都无法绕过的一个问题，首先是微软默认的日期表月份显示如下：

PowerBI中的函数日期表

可能这种形式我们自己看起来没啥的，但是要考虑做出来的可视化报表呈现的对象绝对不仅仅是自己，那么就需要我们自己来制作自己的日期维度表。
PowerBI中的函数日期表

太多方法就不说了，这里提供两种方法大家参考：

一、M函数：

PowerBI中的函数日期表

1、在PQ编辑器界面，左边查询的位置，点击鼠标右键，新建空白查询：

PowerBI中的函数日期表

2、点击操作界面的上方的高级属性编辑器：

PowerBI中的函数日期表

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、之后会变成如下界面，可以设置起始日期等参数：

PowerBI中的函数日期表

5、选择调用：

PowerBI中的函数日期表

这是方法一，生成了一个带有中文月份的日期表。

§§

二、表函数：

1、在建模窗口下，选择新建表格：

PowerBI中的函数日期表

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函数生成的例子修改代码如下图：

PowerBI中的函数日期表

结果如下图：

PowerBI中的函数日期表

﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌﹌

两种方法都可以生成带有中文字样的日期表，区别在于：

一、M函数生成的日期表是固定的，静态的日期表。

二、表函数运用了MIN/MAX，这样生成出来的日期表是随着事实表而变动的动态日期表。

推荐大家选择第二个。

本期就到这里，我是白茶，一个PowerBI的初学者，偶尔会不定时更新一些自己的学习心得。

QQ群：772646751

小伙伴们，GET了么？

白茶会不定期的分享一些函数卡片哦。

（文件在知识星球[PowerBI丨需求圈]）

PowerBI中的函数日期表
这里是白茶，一个PowerBI的初学者。

下面这个知识星球是针对有实际需求的小伙伴，有需要的请加入下面的知识星球。

（这个星球里面有白茶之前所有的案例文件。）
PowerBI中的函数日期表
PowerBI中的函数日期表
PowerBI中的函数日期表

相关标签： 实用技巧

上一篇： Mac通过ssh连接阿里云服务器

下一篇： JS 去除Array中的null值示例代码_javascript技巧

推荐阅读