值得多次複習的一個技能。
如果用 DAX 建構一個日期表很常見，本文更多的從實務的角度來給出一些建議。
構造日期表的方法
一般建構日期表的方法包括：
- 方法一：在資料源中完成，如：Excel。
- 方法二：在 Power Query 中完成。
- 方法三：在資料模型中用 DAX 完成。
這裡推薦使用第三種方法，原因如下：
- 方法一更适合對 DAX 不太熟悉的小白使用者，用來了解什麼是日期表并盡快完成模組化。
- 方法二适合構模組化闆，但在實操中往往不需要模闆提供的額外能力，修改需要查 Power Query 的邏輯，其複雜度帶來的成本超過了收益。
- 方法三最直接簡單，但需要有一定的 DAX 知識基礎。
這裡所說的 DAX 知識基礎，不僅僅是了解什麼是日期表，更多的是知道日期表如何建構可以兼顧到很多使用上的場景。
為什麼必須用日期表
作為初學者的一個問題就是為什麼必須用日期表，可以直接用交易資料中的日期嗎？
答案是：不可以。
最直接的原因是：交易中的日期可能是殘缺的。例如：某個日期是沒有交易的。導緻你想要的某日期是不存在于交易資料中的。
必須使用日期表的真正原因來自兩點：
- 資料模型的設計學
- 複用
從設計的角度看，日期序列常常是分析中表征時間變化的最小時間跨度機關。
注意
暫不考慮比日期級别還小的業務分析，它們的本質相同，隻不過不考慮這個細節程度，可以大幅度優化整個設計。
而做分析的時候，我們往往需要使用的卻不是日期級别的時間跨度，而是用諸如：
- 按年度看銷售額趨勢
- 按月份對比前後兩年的銷售額差異
- 按年度至今來比對目前目标完成度與年度總目标的差異
可見：
分析時所使用的日期區間跨度都是大于單個日期的。
更精确地說，對于某個日期，如：yyyy-MM-dd，記作 D1，其日期區間跨度為 1 日。而常用的日期區間的跨度都會大于 1 日。
為了可以得到任何範圍的日期區間跨度，就需要一個可以容納每一天日期的表，該表滿足：
包括所需的所有日期。
從設計學的角度，我們稱為了滿足随後的分析而建構的這個表叫：日期表。
日期表的設計學用途是：
當希望從某段日期區間跨度去篩選交易業務資料時，都可以從日期表作為出發點，由于日期表如上描述的設計，它必然滿足：
一定可以從日期表中找到所需要的日期區間來篩選業務資料。
再者，由于業務可能有多種明細記錄，如：
- 銷售明細表
- 采購明細表
是以，共享一個日期表，就起到了複用的目的。
日期表初始化
請思考一個問題：
作為一個日期表，應該最少包括幾列？
A - 一列，日期時間
B - 一列，日期
C - 三列，年月日
D - 四列，年季月日
通過對上述内容的了解，不難看出 B 才是正确答案。
A 不是正确答案的原因是 A 所說的日期時間已經達到了時間的明細程度，其時間跨度太低，本場景所說的分析中并不會使用到這樣級别的時間次元。
在 DAX 中，可以建構表，準确講，是一個單列的表，如下：
DAX 函數
CalendarAuto
将輪詢目前在資料模型中的每一個表中的日期類型列以便建立一個日期序列，該序列包括可以涵蓋數模模型所有日期範圍。也就意味着，完全可以通過這個序列找出某個日期區間，該日期區間可以用于篩選個業務資料表。
建構日期表的注意事項
前面講過從日期次元篩選資料時，常常不是從日期級别進行，而是從更高的時間次元進行，如：年季月日，考慮到中文本地化以及排序的問題，最佳實踐如下：
- 分兩步建構日期表
- 先建構一個基礎日期表，包括：年季月日等
- 再将其擴充出更多屬性，包括：是否本月，是否本年，是否過去等
- 起名可以暗示文本或數字
- YearName 表示文本
- YearNumber 表示數字
- 用數字協助文本進行排序
- Jan 是 1 月，但它的文本排序是晚于 Apr 4 月的
- 是以要使用對應的數字進行排序
建構一個日期表
基于上述考量，我們通過 DAX 建構日期表，如下：
Calendar =
// 從最小日期表來進一步建構一個豐富的日期表
VAR vCalendarBase =
AddColumns(
CALENDARAUTO( ) ,
"YearNum" , YEAR( [Date] ) ,
"QuarterNum" , QUARTER( [Date] ) ,
"MonthNum" , MONTH( [Date] ) ,
"DayNumInMonth" , DAY( [Date] ) ,
"WeekNumInYear" , WEEKNUM( [Date] , 2 ) ,
"DayNumInWeek" , WEEKDAY( [Date] , 2 )
)
RETURN vCalendarBase 複制
效果如下：
這裡先建構了數字。
注意
列（字段）在資料模型中是不存在特定順序的，其順序不重要。這也是初學者會常常問及的問題。
擴充這個日期表
有了基本的日期表以後，可以進一步擴充，包括：
- 名稱
- 屬性
- 财務年月
- 其他
舉例如下：
Calendar =
VAR vDateLastUpdate = MAXX( ALL( Sheet1[訂單日期] ) , [訂單日期] ) // 請修改 Sheet1[訂單日期]
// 從最小日期表來進一步建構一個豐富的日期表
VAR vCalendarBase =
AddColumns(
CALENDARAUTO( ) ,
"YearNum" , YEAR( [Date] ) ,
"QuarterNum" , QUARTER( [Date] ) ,
"MonthNum" , MONTH( [Date] ) ,
"DayNumInMonth" , DAY( [Date] ) ,
"WeekNumInYear" , WEEKNUM( [Date] , 2 ) ,
"DayNumInWeek" , WEEKDAY( [Date] , 2 )
)
VAR vNumTable =
SELECTCOLUMNS(
{ ( 1 , "一" ) , ( 2 , "二" ) ,( 3 , "三" ) ,( 4 , "四" ) ,( 5 , "五" ) ,
( 6 , "六" ) , ( 7 , "七" ) ,( 8 , "八" ) ,( 9 , "九" ) ,( 10 , "十" ) ,
( 11 , "十一" ) ,( 12 , "十二" )
} , "Num" , [Value1] , "NumCN" , [Value2] )
VAR vCalendarEx =
ADDCOLUMNS( vCalendarBase ,
"YearNameCN" , [YearNum] & "年" ,
"QuarterNameCN" , "季度" & SELECTCOLUMNS( FILTER( vNumTable , [Num] = [QuarterNum] ) , "Value" , [NumCN] ) ,
"MonthNameCN" , SELECTCOLUMNS( FILTER( vNumTable , [Num] = [MonthNum] ) , "Value" , [NumCN] ) & "月" ,
"MonthNameEN" , FORMAT( [Date] , "mmm" ) ,
"DayNameInWeekCN" , IF( [DayNumInWeek] = 7 , "周日" , "周" & SELECTCOLUMNS( FILTER( vNumTable , [Num] = [DayNumInWeek] ) , "Value" , [NumCN] ) ) ,
-- 其他屬性 --
"IsHistory" , IF( [Date] <= vDateLastUpdate , "Y" , "N" ) ,
"IsCurrentMonth" , IF( [YearNum] * 100 + [MonthNum] = YEAR( vDateLastUpdate ) * 100 + MONTH( vDateLastUpdate ) , "Y" , "N" )
)
RETURN vCalendarEx 複制
考慮到中文的顯示，這裡做了一個數字對照表進而将日期表擴充成符合中文顯示的效果。如下：
在此前的文章中，已經寫過日期表的本質以及營運及财務日期表，結合本文就可以更好的了解這裡面的設計思想了。
總結
關于日期表的講解，的确看到了很多，但本文給出的視角以及如何從這個視角進行實際操作，相信能讓很多剛剛入門不久的夥伴有快速而深入的了解。
以上 DAX 公式，你也可以直接複制粘貼使用，無需修改。