POWER BI 生成日期表

Date Table = VAR BaseCalendar = CALENDAR(DATE(2018,1,1),DATE(2029,12,31))

RETURN GENERATE(BaseCalendar, VAR BaseDate = [Date]

VAR YearDate = Year(BaseDate)

Var MonthNum = MONTH (BaseDate)

VAR MonthName = FORMAT(BaseDate, "mmmm")

VAR YearMonthNum = YearDate * 12 + MonthNum -1

return ROW( "Year", YearDate,

"Month Number", MonthNum,

"Month", MonthName,

"Year Month Number", YearMonthNum

)

)

 

 

 

Date Table = VAR BaseCalendar = CALENDAR(DATE(2021,1,1),DATE(2029,12,31))

RETURN GENERATE(BaseCalendar, VAR BaseDate = [Date]

VAR YearDate = YEAR(BaseDate)

Var MonthNum = MONTH (BaseDate)

VAR MonthName = FORMAT(BaseDate, "mmmm")

VAR DateKey =FORMAT(BaseDate, "YYYYMMDD")

VAR YearMonthKey =FORMAT(BaseDate, "YYYY-MM")

RETURN ROW( "Year", YearDate,

"Month Number", MonthNum,

"Month", MonthName,

"DateKey", DateKey,

"YearMonthKey", YearMonthKey)

)

 

在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年的日期表