PowerBI 引入時間智慧

標籤：

 

簡介

Power BI Desktop -是一款由微軟發布的自助商業智慧工具，功能強大、便於使用。其中還可以通過微軟雲連多個資料來源並且使用資料來源來建立可視化錶盤。

但是幾乎所有的BI都需要展示如何隨時間改變KPI。因此我將會介紹一個協助我們使用事件元素來分析資料的關鍵功能。在PowerBI Desktop 中叫做“time intelligence”。應用這種時域分析法能是商業智慧中基本的資料表現形式。畢竟公司想要知道的無非就是今年的業績相比去年如何以及取得了何種進步。

“Time intelligence”將需要一個日期表，花費一定的時間去建立一個成功資料模型的核心就是這個表。然後日期表必須與主要資料中隨時間變化的日期欄位進行關聯。需要作如下幾種事情：

• YearToDate, QuarterToDate, 和MonthToDate 的計算
• 比較之前的年、季、月
• 復原一段時間的彙總，比如最近三個月的累加。
• 比較平行時間段，比如與之前一年相同的月份。

當使用隨時間進行的資料分析的時候，很可能要使用DAX函數。為了更好地理解，我們將介紹如何建立日期表，然後看一下幾種不同的分析時間的計算，最後加入這些類型道資料模型中。為了測試我會使用一個excel作為PowerBI Desktop 的檔案資料來源。

建立並且應用日期表

對於智能時間，至少需要一個包含不間斷時間範圍的日期表，並且開始時間的最小值是來源資料中的最小日期，結束日期至少等於來源資料中的最大值。實踐中，需要建立一個表，開始日期是最早日期的1月1日而最大日期應該是資料來源日期的上一年的12月31日。一旦你建立了這個表，就能串連資料模型中的含有時間欄位的表，然後拓展時間相關的分析函數。

1.建立日期表

應用時間的前提就是建立日期表。下面步驟說明使用DAX建立表的過程：

1 - 開啟PowerBI Desktop檔案C:\PowerBiDesktopSamples\PowerBIDataModel.Pbix.

2 - 切換左側的tab，選擇第二個Data如所示。

3 - 點擊左上方的Modeling按鈕，然後點擊建立表按鈕。運算式“表=”將出現在公式欄裡面，

4 - 把Table替換成DateDimension

5 - 輸入DAX函數CALENDAR("1/1/2012","31/12/2016"),然後斷行符號或者對勾。前一個時間是dates表的開始時間，後一個時間是結束時間，公式欄內容：DateDimension = CALENDAR( "1/1/2012", "31/12/2016" ).

6 - 斷行符號後，建立了一個單列表，表中的內容就是2012-01-01到2016-12-31，所有日期。

7 - 編輯表頭，改列名稱為DateKey，結果如下所示：

8 - 點擊添加新列按鈕或者右鍵添加新列，新列將會出現在現存列右側。

9 - 在公示欄輸入“FullYear = YEAR([DateKey])”。

10 -  再添加如下19個列公式。如下：

欄位標題	公式	注釋
ShortYear	VALUE(Right(Year([DateKey]),2))	取後兩位元字年
MonthNumberFull	FORMAT([DateKey], "MM")	月份取兩位元，不足的前面補0
MonthFull	FORMAT([DateKey], "MMMM")	月份展示名稱
WeekNumber	WEEKNUM([DateKey])	以下自行測試
MonthAbbr	FORMAT([DateKey], "MMM")
WeekNumberFull	FORMAT(Weeknum([DateKey]), "00")
DayOfMonth	DAY([DateKey])
DayOfMonthFull	FORMAT(Day([DateKey]),"00")
DayOfWeek	WEEKDAY([DateKey])
DayOfWeekFull	FORMAT([DateKey],"dddd")
DayOfWeekAbbr	FORMAT([DateKey],"ddd")
ISODate	[FullYear] & [MonthNumberFull] & [DayOfMonthFull]
FullDate	[DayOfMonth] & " " & [MonthFull] & " " & [FullYear]
QuarterFull	"Quarter " & ROUNDDOWN(MONTH([DateKey])/4,0)+1
QuarterAbbr	"Qtr " &ROUNDDOWN(MONTH([DateKey])/4,0)+1
Quarter	"Q" &ROUNDDOWN(MONTH([DateKey])/4,0)+1
QuarterNumber	ROUNDDOWN(MONTH([DateKey])/4,0)+1
QuarterAndYear	DateDimension[Quarter and Year]
MonthAndYearAbbr	DateDimension[MonthAbbr] & " " & [FullYear]
QuarterAndYearNumber	[FullYear] & [QuarterNumber]
YearAndWeek	VALUE([FullYear] &[WeekNumberFull])
YearAndMonthNumber	Value(DateDimension[FullYear] & DateDimension[MonthNumberFull])

 

展示如下：

 

建立所有這些表現時間的的目的就是早晚有一天會用到這些日期來展示報表、彙總指標、展示資料。任何有時間元素的表都可以按照這個新增表中的時間轉換彙總來可視化資料。這裡你不需要擔心是否需要額外的列，因為還可以動態添加你需要的時間元素。

在日期表中引入列排序

現在需要看一下如何排序。典型的例子就是月份排序。如果你打算展示MonthFull 或者MonthAbbr 列，那麼將看到月份（month）出現在軸標籤裡面或者按字母排序的列裡面。

為了避免最後再去調整日期表，可以通過應用特定的日期元素來排列其他列，如下:

1 - 點擊打算使用其他的列來排序的列（比如Monthfull） ；

2 - 點擊Modeling下方的排序按鈕，其他列的名稱將會出現，如所示：

3 - 選擇打算按照排序的列（MonthNumber）;

這裡並不能立即顯示出任何不同，但是當在儀錶盤中使用任何你已經調整過的日期列時，它們將會根據排序列進行資料排序。

下表提供給你需要的資訊來擴充你建立的資料表以便於所有的日期元素都能被正確排序。

Column	Sort By Column
MonthAbbr	MonthNumber
DayOfWeekFull	DayOfWeek
DayOfWeekAbbr	DayOfWeek
Quarter And Year	QuarterAndYearNumber
FullDate	DateKey
MonthAndYearAbbr	YearAndMonthNumber
MonthAndYear	YearAndMonthNumber

 

日期表技巧

當引入時間智慧後，一定要遵守兩個基礎原則。

• 日期範圍必須是連續的。
• 在資料模型中資料範圍一定是包含所有使用的其他表中的日期。

一旦你知道你資料中的最大值和最小值日期就可以使用CALENDAR來產生日期，即使兩個值在不同的表裡面如下：

DateDimension = CALENDAR(MIN(‘Stock‘[PurchaseDate]),  MAX(‘Invoices‘[InvoiceDate]))

或者，你可能更喜歡日期維度通過全年的資料，在這種情況下，公式可以這樣建立表：

DateDimension = CALENDAR(STARTOFYEAR(MIN(‘Stock‘[PurchaseDate])), ENDOFYEAR(MAX(‘Invoices‘[InvoiceDate])))

這個公式擴充了DAX的計算，兩個計算年的公式也是極其有協助的：

• StartOfYear() - 這個公式得出最小的年份。
• EndOfYear() - 這個公式得出最大的年份

注意

這種日期範圍的主要優點在於隨著資料來源的變化自動更新。因此如果Stock 或 Invoices表資料來源擴充了新的資料並且在原有日期外的，那麼這個時間維度表也會自動變化來包含這部分新增資料的部分。

這裡的可以給大家一個小技巧，不需要每次都去建立這個日期表，可先建立一個空的模型，裡面只有日期表，結束和開始日期是手填寫的，然後加入所有其他列，接下來複製這個模板檔案，以後每次使用都以這個模板檔案為基礎建立。只需要替換手動填寫的日期即可。

向資料模型中加入日期表

現在你有了一個日期表，可以與你的資料模型進行整合以便於開始應用這些智能時間。

1 - 點擊關係視圖的圖表來展示資料模型中的表

2 - 點擊管理關聯性按鈕，對話方塊會出現。

3 - 點擊建立按鈕，建立關係。

4 - 在對話方塊頂部選擇時間維度表。

5 - 點擊DateKey列選擇。

6 - 在時間維度表下面的下拉框中選擇Invoice表。

7 - 再點擊InvoiceDate列選擇，對話庫如下：

8 - 點擊Ok，新的表關係就建立了

9 - 點擊關閉，時間維度表就與Invoice表建立了關係。

注意 為了時間智慧在PowerBI中能夠正確使用一定要保證日期表和資料表中的資料類型是date或者datetime。

應用時間智慧

所有的準備工作都已經完成了，接下來就看如何使用DAX實現隨著時間變化來計算指標。

YearToDate, QuarterToDate, 和MonthToDate 運算

首先，讓我們解決一個簡單但是頻繁的需求：計算月累計、季度累計、和年累計的銷售數字。

這個例子中三個函數是很相似的。因此我只解釋第一個月累計，然後建立下面兩個用複製黏貼的方式。

1 -  在資料檢視中選擇Invoices表，然後點擊建立測量；

2 -  在公式欄用MonthSales替換Measure ；

3 - 輸入這個公式MonthSales = TOTALMTD(SUM(InvoiceLines[SalePrice]),DateDimension[DateKey])。

MonthSales = TOTALMTD(SUM(InvoiceLines[SalePrice]),DateDimension[DateKey])

4 - 斷行符號或者點擊對勾完成公式

現在可以複製這個公式，產生兩個新的公式用來表示季度銷售和年度銷售日期公式如下:

QuarterSales = TOTALQTD(SUM(InvoiceLines[SalePrice]),DateDimension[DateKey])YearSales = TOTALYTD(SUM(InvoiceLines[SalePrice]),DateDimension[DateKey])

三個公式分別使用了Totalmtd()、Totalqtd和Totalytd來彙總時間其他參數是一樣的。

結果如下：

 

正縮減，每個月的銷售數字隨著累加到季度銷售數字中，最後還要加到年度銷售數字中。還要注意整個排序是按照monthfull來排序的，其實是按照MonthNumber 進行排序。

總結

在這個例子中，我使用了Invoices表來測試僅僅因為這裡儲存了很多指標資料，當然你也可以選擇其他表來嘗試這個智能時間的應用。它不僅方便了對於資料的分類和比較，更提供了一種潛在的排序和彙總。

PowerBI 引入時間智慧