PowerBI日期表创建方式

一、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商业数据分析）