![5a9a7ebed351a0cf3e00711b7e7d79d9.png](https://i-blog.csdnimg.cn/blog_migrate/83f124026a85cd79f89464c13115331f.jpeg)
时间作为数据分析的一个重要维度,一般都需要有一个单独的维度表,称之为日期表,经常有朋友问使用M函数怎么生成日期表,这里顺便就把日期表的生成方法做一下汇总,分别使用Excel公式、Power Query的M函数以及DAX生成日期表,总有一种你能用的到,毕竟我们使用PowerBI的时候,使用最频繁的可能就是日期表了。
下面各种环境下的公式或代码,均生成以下这个标准的日期表,
![e35af19a963c5f5bac410cc372eec0af.png](https://i-blog.csdnimg.cn/blog_migrate/88ff2aa115bb3bb68e91ef9aef784897.jpeg)
时间跨度从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
点击确定后将出现如下窗口,
![3e37dbe0336b0001ee4d36e3674f0a6e.png](https://i-blog.csdnimg.cn/blog_migrate/f000097d0b12762d49c275c7ae3e4229.jpeg)
输入开始的年份和结束的年份,点击“调用”,就会生成一个标准的日期表。
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.