一、Web免费获取日期/节假日
api接口：https://www.mxnzp.com/doc/detail?id=1
PowerQuery添加Web查询，免费获取日期及工作日、法定节假日时间
PowerQuery Web:
https://www.mxnzp.com/api/holiday/list/year/2015?app_id=sxypjdqmgmpppvyr&app_secret=L0tobmFXQ3ppTTY4WDhwMW1jeW5VZz09
二、日期表制作
1、在Excel中制作日期表
在A1：J1中输入日期表的表头，然后在A2：J2中分别输入以下公式：
A2 = 2019-1-1
B2 = DAY(A2)
C2 = MONTH(A2)
D2 = "M"&C2
E2 = LEN(2^C2)
F2 = "Q"&E2
G2 = YEAR(A2)
H2 = G2*100+C2
I2 = G2&F2
J2 = WEEKDAY(A2,2)
然后选中A2：J2，向下公式填充731行，就可得到从2019到2020年的日期表。
2、使用PowerQuery制作日期表
进入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}}),
data_id=Table.AddColumn(table,"日", each Date.Day([日期]), type number),
month_id = Table.AddColumn(data_id, "月份", each Date.Month([日期])),
month_name=Table.AddColumn(month_id, "月份名称", each "M"& Text.From(Date.Month([日期]))),
quarter = Table.AddColumn(month_name, "季度", each Date.QuarterOfYear([日期])),
quarter_name = Table.AddColumn(quarter, "季度名称", each "Q"&Text.From(Date.QuarterOfYear([日期]))),
year_id = Table.AddColumn(quarter_name,"年度", each Date.Year([日期]), type number),
year_month_id = Table.AddColumn(year_id, "年度月份", each Date.Year([日期])*100+ Date.Month([日期]), type number),
year_quarter_id = Table.AddColumn(year_month_id, "年度季度", each Text.From([年度])&[季度名称]),
day_in_week = Table.AddColumn(year_quarter_id, "周几", each Number.Mod(Date.DayOfWeek([日期]),7)+1, type number)
in
day_in_week
3、DAX生成日期表
在PowerBI Desktop界面点击“新建表”，输入以下的其中一种DAX表达式即可。
常用的有以下几种方式生成日期表：
3.1 利用ADDCOLUMNS与CALENDAR函数生成日期表
日期表 DAX_1 =
ADDCOLUMNS (
CALENDAR (DATE(2019,1,1), DATE(2020,12,31)),
"日",FORMAT ( [Date], "D" ),
"月份", MONTH([Date]),
"月份名称", "M" & MONTH([Date]),
"季度", FORMAT ( [Date], "Q" ),
"季度名称", "Q" & FORMAT ( [Date], "Q" ),
"年度", YEAR ( [Date] ),
"年度月份", FORMAT ( [Date], "YYYYMM" ),
"年度季度", FORMAT ( [Date], "YYYY" ) & "Q" & FORMAT ( [Date], "Q" ),
"星期几", WEEKDAY ( [Date],2 )
)
3.2 利用GENERATE和CALENDAR函数生成日期表
日期表 DAX_2 =
GENERATE (
CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2020, 12, 31 ) ),
VAR currentDay = [Date]
VAR day = DAY( [Date] )
VAR month = MONTH( currentDay )
VAR monthname = "M" & MONTH( currentDay )
VAR quarter = QUARTER( currentDay )
VAR quartername = "Q" & QUARTER( currentDay )
VAR year = YEAR ( currentDay )
VAR weekid = WEEKDAY ( currentDay,2)
RETURN ROW (
"日", day,
"月份", month,
"月份名称",monthname,
"季度",quarter,
"季度名称",quartername,
"年度", year ,
"年度月份", year*100+month,
"年度季度", year&quartername,
"星期几", weekid
)
)
3.3 GENERATE与CALENDARAUTO函数
日期表 DAX_3 =
GENERATE (
CALENDARAUTO(),
VAR currentDay = [Date]
VAR day = DAY( [Date] )
VAR month = MONTH( currentDay )
VAR monthname = "M" & MONTH( currentDay )
VAR quarter = QUARTER( currentDay )
VAR quartername = "Q" & QUARTER( currentDay )
VAR year = YEAR ( currentDay )
VAR weekid = WEEKDAY ( currentDay,2)
RETURN ROW (
"日", day,
"月份", month,
"月份名称",monthname,
"季度",quarter,
"季度名称",quartername,
"年度", year ,
"年度月份", year*100+month,
"年度季度", year&quartername,
"星期几", weekid
)
)
（来源：采悟Power BI商业数据分析）
