建立日期表

1.DAX建立日期表

DIM_日期 = 
var t1=CALENDAR("2020-1-1","2022-12-31")
var t2=SELECTCOLUMNS(t1,"Date", [Date],
    "DATE_KEY",YEAR({[Date]})*10000+MONTH({[Date]})*100+DAY({[Date]}),
    "自然年", "Y"&YEAR([Date]),
    "自然季", "Q"&QUARTER([Date]),
    "自然月", MONTH([Date]),
    "年月",YEAR([Date]) * 100 +  MONTH([Date]),
    "财年", "F "&(IF(MONTH([Date])>6&&MONTH([Date])<=12,YEAR([Date]),YEAR([Date])-1)),
    "财季", var m=MONTH([Date]) return SWITCH(TRUE(),m=7||m=8||m=9,"Q 1",m=10||m=11||m=12,"Q 2",m=1||m=2||m=3,"Q 3",m=4||m=5||m=6,"Q 4"),
    "财月", var m=MONTH([Date]) return if(m>6&&m<=12,m-6,m+6),

    "FY_StartMonth",var m=MONTH([Date]) return if(m>6&&m<=12,YEAR([Date])*100+7,(YEAR([Date])+1)*100+7),
    "FY_EndMonth",var m=MONTH([Date]) return if(m>6&&m<=12,(YEAR([Date])+1)*100+7,YEAR([Date])*100+7)

)
return t2
日期表 = ADDCOLUMNS (
CALENDAR ( date(2017,1,1),date(2018,12,31) ),
"年", YEAR ( [Date] ),
"季度", ROUNDUP( MONTH ( [Date] )/3,0 ),
"月", MONTH ( [Date] ),
"周", WEEKNUM([Date]),
"年季度", YEAR ( [Date] ) & "Q" & ROUNDUP( MONTH ( [Date] )/3,0 ) ,
"年月", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
"年周", YEAR ( [Date] ) * 100 + WEEKNUM ( [Date] ),
"星期几", WEEKDAY([Date])
)

Dim_date = ADDCOLUMNS (
CALENDAR ( date(2017,1,1),date(2018,12,31) ),
"Year", YEAR ( [Date] ),
"Quarter", ROUNDUP( MONTH ( [Date] )/3,0 ),
"Month", MONTH ( [Date] ),
"WeekNum", WEEKNUM([Date]),
"Day",FORMAT ( [Date], "DD" ),
"YQ", YEAR ( [Date] ) & "Q" & ROUNDUP( MONTH ( [Date] )/3,0 ) ,
"YM", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
"YW", YEAR ( [Date] ) * 100 + WEEKNUM ( [Date] ),
"Weekday", WEEKDAY([Date])
)
Date = ADDCOLUMNS (
CALENDAR (min('事实表[date]),MAX('事实表[date])),
"YEAR", FORMAT( [Date],"YYYY"),
"Month",UPPER(FORMAT([Date],"MMM")), 
"Quarter",IF(MONTH([Date])<4,"JFM",IF(MONTH([Date])<7,"AMJ",IF(MONTH([Date])<10,"JAS","OND"))), 
"QuarterYear",IF(MONTH([Date])<4,"JFM",IF(MONTH([Date])<7,"AMJ",IF(MONTH([Date])<10,"JAS","OND")))&" "&FORMAT( [Date],"YYYY"), 
"QuarterOrderBy",FORMAT( [Date],"YYYY") & IF(MONTH([Date])<4,"3",IF(MONTH([Date])<7,"4",IF(MONTH([Date])<10,"1","2"))),
"QuarterOrderBy2",FORMAT( [Date],"YYYYQ") ,
"FiscalYear" , IF(MONTH([Date])>6,"FY" & RIGHT(FORMAT([Date],"YYYY"),2) & RIGHT(FORMAT([Date],"YYYY"),2)+1,"FY" & RIGHT(FORMAT([Date],"YYYY"),2)-1 &   RIGHT(FORMAT([Date],"YYYY"),2)) ,
"MonthOrderBy", SWITCH(MONTH([Date]),7,"01",8,"02",9,"03",10,"04",11,"05",12,"06",1,"07",2,"08",3,"09",4,"10",5,"11",6,"12") ,
"FY_YY_Month", IF(MONTH([Date])>6,"FY" & RIGHT(FORMAT([Date],"YYYY"),2) & RIGHT(FORMAT([Date],"YYYY"),2)+1,"FY" & RIGHT(FORMAT([Date],"YYYY"),2)-1 &   RIGHT(FORMAT([Date],"YYYY"),2)) & SWITCH(MONTH([Date]),7,"01",8,"02",9,"03",10,"04",11,"05",12,"06",1,"07",2,"08",3,"09",4,"10",5,"11",6,"12"),
"YM", FORMAT( [Date],"YYYY")&"-"&UPPER(FORMAT([Date],"MMM")),
"YM_num", FORMAT( [Date],"YYYY")&UPPER(FORMAT([Date],"MM")),
"date_num",YEAR([Date])*10000+MONTH([Date])*100+1,
"date_text", FORMAT( [Date],"YYYY")&"-"&UPPER(FORMAT([Date],"MM"))&"-"&"01",
"YM01", FORMAT( [Date],"YYYY")&"-"&UPPER(FORMAT([Date],"MM"))&"-"&"01"
)

2.Powerquery建立日期表

简单粗暴,先上代码:

(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}}),
    date_id = Table.TransformColumnTypes(Table.AddColumn(table, "日期序号", each Date.Year([日期])*10000+Date.Month([日期])*100+Date.Day([日期])),{{"日期序号", type number}}),
    year_id = Table.AddColumn(date_id, "年序号", each Date.Year([日期]), type number),
    year_name = Table.AddColumn(year_id, "年份名称", each "Y"&Text.From([年序号])),
    quarter_id = Table.AddColumn(year_name, "季度序号", each Date.QuarterOfYear([日期]), type number),
    quarter_name = Table.AddColumn(quarter_id, "季度名称", each "Q"&Text.From([季度序号])),
    month_id = Table.AddColumn(quarter_name, "月份序号", each Date.Month([日期]), type number),
    month_name = Table.AddColumn(month_id, "月份名称", each "M"&Text.From([月份序号])),
    week_id = Table.AddColumn(month_name, "周序号", each Date.WeekOfYear([日期]), type number),
    week_name = Table.AddColumn(week_id, "周名称", each "w"&Text.From([周序号])),
    year_quarter_id = Table.AddColumn(week_name, "年季序号", each Date.Year([日期])*10+Date.QuarterOfYear([日期]), type number),
    year_quarter_name = Table.AddColumn(year_quarter_id, "年季名称", each "YQ"&Text.From([年季序号])),
    year_month_id = Table.AddColumn(year_quarter_name, "年月序号", each Date.Year([日期])*100+ Date.Month([日期]), type number),
    year_month_name = Table.AddColumn(year_month_id, "年月名称", each "YM"&Text.From([年月序号])),
    year_week_id = Table.AddColumn(year_month_name, "年周序号", each Date.Year([日期])*100+ Date.WeekOfYear([日期]), type number),
    #"year_week-name" = Table.AddColumn(year_week_id, "年周名称", each "YW"&Text.From([年周序号])),
    day_in_week_id = Table.AddColumn(#"year_week-name", "日序号", each Date.DayOfWeek([日期],0), type number),
    day_in_week_name = Table.AddColumn(day_in_week_id, "周天名称", each if [日序号] = 1 then "WD1" else
if [日序号] = 2 then "WD2" else
if [日序号] = 3 then "WD3" else
if [日序号] = 4 then "WD4" else
if [日序号] = 5 then "WD5" else
if [日序号] = 6 then "WD6" else
"WD7"),
    work_day = Table.AddColumn(day_in_week_name , "工作日", each if [日序号] = 6 or [日序号] = 0 then "休息日" else "工作日" )
in
    work_day

使用方法:

1、新建一个空查询,点击高级编辑器,将上边的代码替换里边内容,如下图:

image

2、输入起始日期,点击调用,如 2015、2017:

image

3、调用后,我们看下我们的日期表生成完毕,即可上传到 PowerPivot 做建模分析:

image

本篇分享目的:让你多学习一种日期表的饿创建方法,当然,如果您会使用 M 语言的话,可以将日期表修整成更适合自己分析习惯的格式。

如果你想更直接一些,那么,直接使用下边的代码。

let

date=(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}}),
    date_id = Table.TransformColumnTypes(Table.AddColumn(table, "日期序号", each Date.Year([日期])*10000+Date.Month([日期])*100+Date.Day([日期])),{{"日期序号", type number}}),
    year_id = Table.AddColumn(date_id, "年序号", each Date.Year([日期]), type number),
    year_name = Table.AddColumn(year_id, "年份名称", each "Y"&Text.From([年序号])),
    quarter_id = Table.AddColumn(year_name, "季度序号", each Date.QuarterOfYear([日期]), type number),
    quarter_name = Table.AddColumn(quarter_id, "季度名称", each "Q"&Text.From([季度序号])),
    month_id = Table.AddColumn(quarter_name, "月份序号", each Date.Month([日期]), type number),
    month_name = Table.AddColumn(month_id, "月份名称", each "M"&Text.From([月份序号])),
    week_id = Table.AddColumn(month_name, "周序号", each Date.WeekOfYear([日期]), type number),
    week_name = Table.AddColumn(week_id, "周名称", each "W"&Text.From([周序号])),
    year_quarter_id = Table.AddColumn(week_name, "年季序号", each Date.Year([日期])*10+Date.QuarterOfYear([日期]), type number),
    year_quarter_name = Table.AddColumn(year_quarter_id, "年季名称", each "YQ"&Text.From([年季序号])),
    year_month_id = Table.AddColumn(year_quarter_name, "年月序号", each Date.Year([日期])*100+ Date.Month([日期]), type number),
    year_month_name = Table.AddColumn(year_month_id, "年月名称", each "YM"&Text.From([年月序号])),
    year_week_id = Table.AddColumn(year_month_name, "年周序号", each Date.Year([日期])*100+ Date.WeekOfYear([日期]), type number),
    #"year_week-name" = Table.AddColumn(year_week_id, "年周名称", each "YW"&Text.From([年周序号])),
    day_in_week_id = Table.AddColumn(#"year_week-name", "日序号", each Date.DayOfWeek([日期],0), type number),
    day_in_week_name = Table.AddColumn(day_in_week_id, "周天名称", each if [日序号] = 1 then "WD1" else
if [日序号] = 2 then "WD2" else
if [日序号] = 3 then "WD3" else
if [日序号] = 4 then "WD4" else
if [日序号] = 5 then "WD5" else
if [日序号] = 6 then "WD6" else
"WD7"),
    work_day = Table.AddColumn(day_in_week_name , "工作日", each if [日序号] = 6 or [日序号] = 0 then "休息日" else "工作日" )
in
    work_day

in 
    date(2017,2018)

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 215,012评论 6 497
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 91,628评论 3 389
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 160,653评论 0 350
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 57,485评论 1 288
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 66,574评论 6 386
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 50,590评论 1 293
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 39,596评论 3 414
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 38,340评论 0 270
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 44,794评论 1 307
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,102评论 2 330
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,276评论 1 344
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 34,940评论 5 339
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 40,583评论 3 322
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,201评论 0 21
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,441评论 1 268
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 47,173评论 2 366
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 44,136评论 2 352