Power BI日期表创建方式汇总

最新推荐文章于 2025-06-10 12:57:56 发布

原创最新推荐文章于 2025-06-10 12:57:56 发布 · 9k 阅读

23 ·

CC 4.0 BY-SA版权

文章标签：

#bi

0013-BI工具专栏收录该内容

10 篇文章

订阅专栏

本文总结了Power BI中创建日期表的三种方法：Excel公式法、Power Query M函数法和DAX函数法。通过Excel公式可以快速生成基础日期表，Power Query则提供了自定义年份范围的灵活性，而DAX提供了多种函数组合，如ADDCOLUMNS与CALENDAR，GENERATE与CALENDAR，以及GENERATE与CALENDARAUTO，用于生成和优化日期表。

在日常使用PBI做数据分析时，最长用到的就是日期维表了，这里把日期表的生成方法做一下汇总，分别使用Excel公式、Power Query的M函数以及DAX生成日期表
下图为我们要生成的日期表样式：

Excel公式法
在A1：H1中输入日期表的表头，然后在A2：H2中分别输入以下公式：
```
A2=2020-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行，就可得到从2020到2021年的日期表

Power Query M函数法
进入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函数法
用DAX生成日期表，使用不同的函数都可以做到（在PowerBI Desktop中新建表，输入下面的DAX代码就可以了），常用的有以下几种组合：

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

② 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函数，据说这个代码可以节省内存、提升运行速度

③ 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函数可以自动检测模型中其他表中所有日期，然后生成涵盖这些日期的整年日期表