玩PowerBI必备的日期表制作方式汇总

原创采悟 PowerBI星球

上篇文章中介绍了一个DAX函数生成日期表的例子，有朋友问使用M函数怎么生成日期表，这里顺便就把日期表的生成方法做一下汇总，分别使用Excel公式、Power Query的M函数以及DAX生成日期表，总有一种你能用的到，毕竟我们使用PowerBI的时候，使用最频繁的可能就是日期表了。

下面各种环境下的公式或代码，均生成以下这个标准的日期表，

时间跨度从2017年1月1日到2018年12月31日，如果想要其他日期范围的日期表，可以手动调整代码的起止日期。

Excel公式制作日期表

在A1：H1中输入日期表的表头，然后在A2：H2中分别输入以下公式：

A2=2017-01-01
B2=YEAR(A2)
C2="Q"&LEN(2^MONTH(A2))
D2=RIGHT("0"&MONTH(A2),2)
E2=DAY(A2)
F2=B2&C2
G2=B2*100+D2
H2=WEEKDAY(A2,2)

然后选中A2：H2，向下公式填充到731行，就可得到从2017到2018年的日期表。

Power Query制作日期表

进入PQ编辑器中，新建空查询，打开高级编辑器，把里面的代码全部删除，然后把以下代码粘贴到高级编辑器中，

(optional 请输入开始年份 as number,
optional 请输入结束年份 as number)=>
let
x = 请输入开始年份,
y = if 请输入结束年份 = null
then 请输入开始年份 else 请输入结束年份,
begin_date = if x = null
then #date(Date.Year(DateTime.LocalNow()),1,1)
else #date(x,1,1),
end_date = if y = null then #date(Date.Year(DateTime.LocalNow()),12,31)
else #date(y,12,31),
list = {1..Number.From(end_date)-Number.From(begin_date)+1},
dates = List.Transform( list , (item)=> Date.AddDays(begin_date,item-1) ),
table = Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(dates,
Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "日期"}}),{{"日期", type date}}),
year_id = Table.AddColumn(table,"年度", each Date.Year([日期]), type number),
quarter_name = Table.AddColumn(year_id, "季度", each "Q"&Text.From(Date.QuarterOfYear([日期]))),
month_id = Table.AddColumn(quarter_name, "月份", each Text.PadStart(Text.From(Date.Month([日期])),2,"0")),
data_id=Table.AddColumn(month_id,"日", each Date.Day([日期]), type number),
year_quarter_id = Table.AddColumn(data_id, "年度季度", each Text.From([年度])&[季度]),
year_month_id = Table.AddColumn(year_quarter_id, "年度月份", each Date.Year([日期])*100+ Date.Month([日期]), type number),
day_in_week = Table.AddColumn(year_month_id, "星期几", each Number.Mod(Date.DayOfWeek([日期])+6,7)+1, type number)
in
day_in_week

点击确定后将出现如下窗口，

输入开始的年份和结束的年份，点击“调用”，就会生成一个标准的日期表。

DAX生成日期表

在PowerBI Desktop中新建表，输入下面的DAX代码就可以了，具体操作方式也可以参考上篇文章：PowerBI Desktop中新建表的使用场景。

利用DAX生成日期表，使用不同的函数都可以做到，常用的有以下几种组合：

1，ADDCOLUMNS与CALENDAR函数：

日期表1 =
ADDCOLUMNS (
CALENDAR (DATE(2017,1,1), DATE(2018,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 ( 2018, 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
    )
)

这里运用了VAR函数，据说这个代码可以节省内存、提升运行速度，不过日期表的量级太小，VAR的优势并没有感觉到。

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

同样使用了VAR函数，你也许注意到了，这段代码中并没有指定起止日期，这就是CALENDARAUTO函数的厉害之处，它可以自动检测模型中其他表中所有日期，然后生成涵盖这些日期的整年日期表。

比如采购表是从2016年10月1日开始有采购数据的，而销售订单表最新日期是2018年4月23日，那么这段代码自动生成从2016年1月1日到2018年12月31日的日期表。

如果模型中其他表的日期范围发生变动，这个日期表也会自动更新到新的日期范围，利用CALENDARAUTO可以很轻松的制作一个动态的日期表。

使用上面三种DAX函数生成日期表还有一个小小的bug，就是CALENDAR函数生成的日期列字段名都是英文的[Date],与其他列的中文字段放在一起好像不太协调，生成日期表以后，可以手动对这个字段重命名为中文的[日期]。

以上就是制作日期表的各种方式，有些函数现在看不懂也没有关系，需要时可以直接复制粘贴，随着PowerBI学习的深入，这些代码将逐渐变得通俗易懂。

THE END

提升技能开拓视野

继续滑动看下一个