用 DAX 快速構建一個日期表
值得多次復習的一個技能。
如果用 DAX 構建一個日期表很常見，本文更多的從實務的角度來給出一些建議。
構造日期表的方法
一般構建日期表的方法包括：
方法一：在數(shù)據(jù)源中完成，如：Excel。
方法二：在 Power Query 中完成。
方法三：在數(shù)據(jù)模型中用 DAX 完成。
這里推薦使用第三種方法，原因如下：
方法一更適合對 DAX 不太熟悉的小白用戶，用來理解什么是日期表并盡快完成建模。
方法二適合構建模板，但在實操中往往不需要模板提供的額外能力，修改需要查 Power Query 的邏輯，其復雜度帶來的成本超過了收益。
方法三最直接簡單，但需要有一定的 DAX 知識基礎。
這里所說的 DAX 知識基礎，不僅僅是理解什么是日期表，更多的是知道日期表如何構建可以兼顧到很多使用上的場景。
為什么必須用日期表
作為初學者的一個問題就是為什么必須用日期表，可以直接用交易數(shù)據(jù)中的日期嗎？
答案是：不可以。
最直接的原因是：交易中的日期可能是殘缺的。例如：某個日期是沒有交易的。導致你想要的某日期是不存在于交易數(shù)據(jù)中的。
必須使用日期表的真正原因來自兩點：
數(shù)據(jù)模型的設計學
復用
從設計的角度看，日期序列常常是分析中表征時間變化的最小時間跨度單位。
注意
暫不考慮比日期級別還小的業(yè)務分析，它們的本質相同，只不過不考慮這個細節(jié)程度，可以大幅度優(yōu)化整個設計。
而做分析的時候，我們往往需要使用的卻不是日期級別的時間跨度，而是用諸如：
按年度看銷售額趨勢
按月份對比前后兩年的銷售額差異
按年度至今來比對當前目標完成度與年度總目標的差異
可見：
分析時所使用的日期區(qū)間跨度都是大于單個日期的。
更精確地說，對于某個日期，如：yyyy-MM-dd，記作 D1，其日期區(qū)間跨度為 1 日。而常用的日期區(qū)間的跨度都會大于 1 日。
為了可以得到任何范圍的日期區(qū)間跨度，就需要一個可以容納每一天日期的表，該表滿足：
包括所需的所有日期。
從設計學的角度，我們稱為了滿足隨后的分析而構建的這個表叫：日期表。
日期表的設計學用途是：
當希望從某段日期區(qū)間跨度去篩選交易業(yè)務數(shù)據(jù)時，都可以從日期表作為出發(fā)點，由于日期表如上描述的設計，它必然滿足：
一定可以從日期表中找到所需要的日期區(qū)間來篩選業(yè)務數(shù)據(jù)。
再者，由于業(yè)務可能有多種明細記錄，如：
銷售明細表
采購明細表
因此，共享一個日期表，就起到了復用的目的。
日期表初始化
請思考一個問題：
作為一個日期表，應該最少包括幾列？
A - 一列，日期時間
B - 一列，日期
C - 三列，年月日
D - 四列，年季月日
通過對上述內容的理解，不難看出 B 才是正確答案。
A 不是正確答案的原因是 A 所說的日期時間已經(jīng)達到了時間的明細程度，其時間跨度太低，本場景所說的分析中并不會使用到這樣級別的時間維度。
在 DAX 中，可以構建表，準確講，是一個單列的表，如下：
DAX 函數(shù)?CalendarAuto?將輪詢目前在數(shù)據(jù)模型中的每一個表中的日期類型列以便創(chuàng)建一個日期序列，該序列包括可以涵蓋數(shù)模模型所有日期范圍。也就意味著，完全可以通過這個序列找出某個日期區(qū)間，該日期區(qū)間可以用于篩選個業(yè)務數(shù)據(jù)表。
構建日期表的注意事項
前面講過從日期維度篩選數(shù)據(jù)時，常常不是從日期級別進行，而是從更高的時間維度進行，如：年季月日，考慮到中文本地化以及排序的問題，最佳實踐如下：
分兩步構建日期表
先構建一個基礎日期表，包括：年季月日等
再將其擴展出更多屬性，包括：是否本月，是否本年，是否過去等
起名可以暗示文本或數(shù)字
YearName 表示文本
YearNumber 表示數(shù)字
用數(shù)字協(xié)助文本進行排序
Jan 是 1 月，但它的文本排序是晚于 Apr 4 月的
所以要使用對應的數(shù)字進行排序
構建一個日期表
基于上述考量，我們通過 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效果如下：
這里先構建了數(shù)字。
注意
列（字段）在數(shù)據(jù)模型中是不存在特定順序的，其順序不重要。這也是初學者會常常問及的問題。
擴展這個日期表
有了基本的日期表以后，可以進一步擴展，包括：
名稱
屬性
財務年月
其他
舉例如下：
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考慮到中文的顯示，這里做了一個數(shù)字對照表進而將日期表擴展成符合中文顯示的效果。如下：
在此前的文章中，已經(jīng)寫過日期表的本質以及運營及財務日期表，結合本文就可以更好的理解這里面的設計思想了。
總結
關于日期表的講解，的確看到了很多，但本文給出的視角以及如何從這個視角進行實際操作，相信能讓很多剛剛入門不久的伙伴有快速而深入的理解。
以上 DAX 公式，你也可以直接復制粘貼使用，無需修改。