技巧——新建日期表

01-26

PowerBI中最常用的表是什麼？

毫無疑問，日期表！

不同行業的分析，維度表有類別之分，數據表有指標計算之別。但當談到日期時，基本是一致的。而且日期表也是我們使用時間智能的前提。

由於日期表、時間智能公式非常的好用，往往在做分析時我們都會加入一張日期表。對於我個人，因為經常會收到一些讀者發來的數據建模問題，建立日期表對我來說也是一項重複性操作。如何以最快的速度生成一張通用而且好用的日期表呢？很多人都想要找到一個適合自己的最優方案。

常見的方法有四種：

1) 用Excel製作，導入PowerBI （這是最靈活的方法，尤其在使用定製的日曆表時）

2) PowerQuery生成，利用編輯查詢器中的添加日期列功能（這是以往我比較喜歡的方式，在日曆表的使用文章中曾介紹過）

3) 複製前人寫過的PowerQueryM代碼。這種方法很快很方便，只不過對於很多人這屬於黑箱操作，並不理解語句的具體含義。

4) 寫DAX公式生成

本文想推薦的就是這第四種寫DAX公式的方法，它是所有方法中最快的。

最近在實踐中經常使用，屢試不爽。只需要兩個步驟，新建表，再複製一段DAX公式。

為什麼說這是一個非常好用的方法？我的理由有4點：

1. 上圖例子中，Calendar函數生成了一張2016年12月28日到2017年12月31日的日期表。這裡的日期可以隨意的去替換。

並且，還可以把起始日期替換成Firstdate和Lastdate，比如Firstdate(銷售數據表[訂單日期])可以得到銷售數據表中的最早訂單日期，Lastdate可以得到最近的日期，此方法生成的日期表將永遠等於數據表的日期範圍，這往往也是我們想要得到的效果。

2. Addcolumns的含義是在生成日期表基礎上添加列，這與使用Excel製表的邏輯是一樣的。利用基本的日期函數Year、Month、Weeknum和算式求得每一個日期的年季度月日，這些函數與Excel的函數相同，對Excel的使用者來講不難理解。

Marco說這個公式執行了很多重複的計算，如果利用VAR、Generate、Row函數可以調成最優。

但我認為日期表即使跨度十年也就是3650行，用寫兩倍長的代碼來提升0.0X秒的計算速度，並沒有什麼吸引力。如果說在速度和簡單上來找一個平衡，我更傾向於簡單。（當然，這只是針對日期表這個例子。Marco的那篇文章主要是為了說明Generate/Row可以更好地取代Addcolumns，這是另一個有意義的話題。）

3. 注意到在建立[年月]列時，我使用的方法是Year([Date])*100+Month([Date])數字計算，而不是利用Format函數生成「年份月份」的文本格式。這樣的好處是在後期使用中不會涉及到日期表的排序問題。（比如經常會遇到「2016年11月」會排在「2016年2月」的前面，而用201611和201602就會很好地避免了這種問題）

4. 這個公式並不難，保存下來可以複製粘貼使用。即使手工輸入也不會很費力氣。最重要的是你能夠理解此公式的含義，根據自己的需要利用Excel類的簡單日期函數做調整。

我把公式寫在了下面，供直接複製使用。

--------------------------

日期表 = ADDCOLUMNS (

CALENDAR ( date(2016,12,28),date(2017,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])

)

--------------------------

祝好，

微信公眾號

PowerBI大師