了解 Excel 中的樞紐分析表,並在其中建立日期表格

若要瀏覽和計算一段時間的資料,Power Pivot 中的日期資料表是不可或缺的關鍵。 本文將帶您全盤了解日期資料表,以及如何在 Power Pivot 中建立日期資料表。 尤其本文將說明:

  • 日期資料表對於根據日期與時間瀏覽和計算資料很重要的原因。

  • 如何使用 Power Pivot 將日期資料表新增到 [資料模型]。

  • 如何在日期資料表中建立 [年]、[月] 和 [期間] 等新日期資料行。

  • 如何建立日期資料表與事實資料表之間的關聯。

  • 如何處理時間。

本文適用於不熟悉 Power Pivot 的使用者。 不過,對已充分了解如何匯入資料、建立關聯和建立計算結果欄和量值的使用者來說也很重要。

本文「不會」說明如何在量值公式中使用 DAX 時間智慧函數。 如需如何使用 DAX 時間智慧函數建立量值的詳細資訊,請參閱 Excel Power Pivot 中的時間智慧 (機器翻譯)。

附註: 在 Power Pivot 中,「量值」這個名稱就等同於「導出欄位」這個名稱。 在本文中,我們一律使用量值這個名稱。 如需詳細資訊,請參閱 Power Pivot 中的量值 (機器翻譯)。

內容

了解日期資料表

將日期資料表新增到 [資料模型]

從關聯式資料庫匯入

在 Excel 中建立日期資料表

如何:在 Excel 中建立日期資料表並複製到 [資料模型] 中

將新日期資料行新增到日期資料表

DAX 日期和時間函數

日曆年度的公式範例

年度

月份

季度

月份名稱

會計年度的公式範例

會計年度

會計月份

會計季度

假日或特殊日期

自訂行事曆 - 十三個四週的期間

期間

期間會計年度

會計年度中的期間

關聯

多個關聯

非作用中的關聯

多個日期資料表

[日期資料表] 屬性

處理時間

讓日期更易於使用

附錄

將文字資料類型日期轉換為日期資料類型

其他資源

了解日期資料表

幾乎所有資料分析都包含瀏覽和比較一段日期與時間的資料。 例如,您可能會想要加總上一個會計季度的銷售額,然後將這些總計與其他季度加以比較,或者您可能會想要計算帳戶的月末期終餘額。 在這些各個案例中,您使用日期做為一種方法,將銷售交易或某個特定時間週期的餘額組成群組或加以彙總。

Power View 報表

按會計季度樞紐分析表顯示的總銷售額

日期資料表可以包含許多不同的日期和時間表示方法。 例如,日期資料表通常會有 [會計年度]、[月份]、[季度] 或 [期間] 等資料行,而當您交叉分析篩選和篩選樞紐分析表或 Power View 報表中的資料時,您可以從 [欄位清單] 選取這些資料行做為欄位。

Power View 欄位清單

Power View 欄位清單

針對要在個別範圍中包含所有日期的 [年份]、[月份] 和 [季度] 等日期資料行,日期資料表「必須」至少要有一個具有一組連續日期的資料行。 也就是說,針對日期資料表中包含的每年的每一天,該資料行都必須有一個資料列。

例如,如果您要瀏覽的資料的日期是從 2010 年 2 月 1 日起到 2012 年 11 月 30 止,而且您是根據日曆年度製作報表,則您會想要日期範圍至少是從 2010 年 1 月 1 日起到 2012 年 12 月 31 日止的日期資料表。 日期資料表中的每一年必須包含每年的每一天。 如果您將定期使用更新的資料來重新整理資料,您可能會想要讓結束日期在一兩年內到期,這樣就不必隨著時間推移更新日期資料表。

具有一組連續日期的日期資料表

具連續日期的日期表格

如果您是根據會計年度製作報表,您可以針對每個會計年度建立具有一組連續日期的日期資料表。 例如,如果會計年度是從 3 月 1 日開始,而您有會計年度 2010 年起到目前日期 (例如會計年度 2013 年) 為止的資料,您可以建立從 3/1/2009 開始的日期資料表,並至少包含每個會計年度的每一天,直到會計年度 2013 年的最後一個日期為止。

如果您將同時根據日曆年度和會計年度製作報表,則不需要建立個別的日期資料表。 單一日期資料表可以包含日曆年度、會計年度的資料行,甚至可以包含十三個四週期間的行事曆。 重要的是,針對所有包含的年份,日期資料表都會包含一組連續的日期。

將日期資料表新增到 [資料模型]

您可透過一些方法將日期資料表新增到 [資料模型]:

  • 從關聯式資料庫或其他資料來源匯入。

  • 在 Excel 中建立日期資料表,然後複製或連結到 Power Pivot 中的新資料表。

  • 從 Microsoft Azure Marketplace 匯入。

現在就讓我們分別深入了解這些方法。

從關聯式資料庫匯入

如果您從資料倉儲或其他類型的關聯式資料庫匯入一些或所有資料,很有可能已經有日期資料表,以及該資料表與您正在匯入的其餘資料之間的關聯。 日期和格式可能會符合事實資料中的日期,而且日期可能是從過去開始,並且一直延續到未來。 您要匯入的日期資料表可能很大,且包含超出您需包含在 [資料模型] 中的日期範圍。 您可以使用 Power Pivot [資料表匯入精靈] 的進階篩選功能,選擇性地只選擇您真正需要的日期和特定資料行。 這可大幅縮減活頁簿的大小並提升效能。

資料表匯入精靈

[表格匯入精靈] 對話方塊

在大多數情況下,您不需要建立任何額外的資料行 (例如 [會計年度]、[週]、[月份名稱] 等),因為它們已經存在於匯入的資料表中。 不過,在某些情況下,在您將日期資料表匯入 [資料模型] 後,您可能需要根據特定的報表需求,建立額外的日期資料行。 幸好,您可以透過 DAX 輕鬆完成這個作業。 稍後您將深入了解如何建立日期資料表欄位。 每個環境的情況都不同。 如果您不確定資料來源是否為有關聯的日期或行事曆資料表,請詢問您的資料庫系統管理員。

在 Excel 中建立日期資料表

您可以在 Excel 中建立日期資料表,然後在 [資料模型] 中將它複製到新資料表。 這是一個相當輕鬆的作業,而且有很大的彈性。

當您在 Excel 中建立日期資料表時,您需要從具有一個連續日期範圍的單一資料行開始。 接著,您可以使用 Excel 公式在 Excel 工作表中建立額外的資料行 (例如 [年份]、[季度]、[月份]、[會計年度]、[期間] 等),或是在將資料表複製到 [資料模型] 後,您也可以將它們建立為計算結果欄。 若要了解如何在 Power Pivot 中建立額外的日期資料行,請參閱本文稍後的<將日期資料行新增到日期資料表>一節。

如何:在 Excel 中建立日期資料表並複製到 [資料模型] 中

  1. 在 Excel 空白工作表中的 A1 儲存格中,輸入資料行標頭名稱以識別日期範圍。 這通常會是 [日期]、[日期時間] 或 [日期索引鍵] 等。

  2. A2 儲存格中,輸入開始日期。 例如,1/1/2010

  3. 按一下填滿控點,然後將它向下拖曳到包含結束日期的列數。 例如,12/31/2016

    Excel 中的日期欄

  4. 選取 [日期] 資料行中的所有資料列 (包括 A1 儲存格中的標頭名稱)。

  5. 在 [樣式] 群組中,按一下 [格式化為表格],然後選取樣式。

  6. 在 [格式化為表格] 對話方塊中,按一下 [確定]。

    Power Pivot 中的日期欄

  7. 複製所有資料列,包括標頭。

  8. 在 Power Pivot 的 [常用] 索引標籤上,按一下 [貼上]。

  9. 在 [貼上預覽] > [資料表名稱] 中,輸入 [日期] 或 [行事曆] 等名稱。 保持選取 [使用第一個資料列做為資料行標頭],然後按一下 [確定]。

    貼上預覽

    Power Pivot 中的新日期資料表 (在此範例中名為 [行事曆]) 看起來像這樣:

    Power Pivot 中的日期表格

    附註: 您也可以使用 [加入至資料模型] 建立連結資料表。 不過,這不必要地增加了活頁簿的大小,因為活頁簿有兩個版本的日期資料表 (一個是 Excel,而一個是 Power Pivot)。

附註: 在 Power Pivot 中,[日期] 名稱是個關鍵字。 如果您將 Power Pivot 中建立的資料表命名為 [日期],則需在引數中參照到該名稱的任何 DAX 公式中,使用單引號括住資料表名稱。 本文的所有這些範例影像和公式皆參考 Power Pivot 中建立的日期資料表 (名為 [行事曆])。

現在 [資料模型] 中已有日期資料表。 您可以使用 DAX 新增 [年份]、[月份] 等日期資料行。

將新日期資料行新增到日期資料表

若要定義某個日期範圍內的所有日期,具有單一日期資料行的日期資料表 (每年的每一天都各有一個資料列) 是很重要的關鍵。 也需要建立事實資料表與日期資料表之間的關聯。 不過,在樞紐分析表或 Power View 報表中依據日期進行分析時,該單一日期資料行 (每年各有一個資料列) 並不實用。 您會希望讓日期資料表包含多個資料行,協助您彙總某個範圍或某個日期群組的資料。 例如,您可能會想要依據月份或季度加總銷售額,或者您可能會建立計算逐年成長率的量值。 在這些各個案例中,日期資料表需要年份、月份或季度資料行,以便彙總該期間的資料。

如果您已從關聯式資料來源匯入日期資料表,該資料表可能已包含您需要的不同類型的日期資料行。 在某些情況下,您可能會想要修改其中的某些資料行或建立額外的日期資料行。 如果您在 Excel 中建立自己的日期資料表並將它複製到 [資料模型] 中,則更是如此。 幸好,透過 DAX 中的日期和時間函數 (英文),您可在 Power Pivot 中輕鬆建立新日期資料行。

提示: 如果您未曾使用 DAX,您可以前往 Office.com 並從快速入門:30 分鐘學會 DAX 基本概念 (機器翻譯) 開始學習

DAX 日期和時間函數

如果您曾在 Excel 公式中使用日期和時間函數,您可能相當熟悉日期和時間函數 (英文)。 雖然這些函數與 Excel 中的對應項目類似,仍有一些重要差異:

  • DAX 日期和時間函數使用 [日期時間] 資料類型。

  • 它們可使用資料行中的值做為引數。

  • 它們可用來傳回和/或操縱日期值。

在日期資料表中建立自訂日期資料行時,通常會使用這些函數,因此請務必了解這些函數。 我們將使用許多這類函數建立 [年份]、[季度]、[會計月份] 等等的資料行。

附註: DAX 中的日期和時間函數與時間智慧函數不同。 深入了解 Excel 2013 Power Pivot 中的時間智慧 (機器翻譯)

DAX 包含下列日期和時間函數:

也有許多其他可在公式中使用的 DAX 函數。 例如,這裡說明的許多公式使用數學和三角函數 (英文) (例如 MOD (英文) 和 TRUNC (英文))、邏輯函數 (英文) (例如 IF (英文)),以及文字函數 (英文) (例如 FORMAT (英文))。如需其他 DAX 函數的詳細資訊,請參閱本文稍後的<其他資源>一節。

日曆年度的公式範例

下列範例說明在 [行事曆] 日期資料表中用來建立額外資料行的公式。 一個 [日期] 資料行已經存在,且包含從 1/1/2010 到 12/31/2016 的連續日期範圍。

年份

=YEAR([date])

在此公式中,YEAR (英文) 函數會傳回來自 [日期] 資料行的值的年份。 因為 [日期] 資料行中的值屬於 [日期時間] 資料類型,YEAR 函數知道如何傳回其中的年份。

年份欄

月份

=MONTH([date])

在此公式中,與 YEAR 函數很像的是,只要使用 MONTH (英文) 函數,就能傳回 [日期] 資料行中的月份值。

月份欄

季度

=INT(([Month]+2)/3)

在此公式中,我們使用 INT (英文) 函數傳回整數形式的日期值。 我們為 INT 函數指定的引數是 [月份] 資料行中的值,加上 2,然後除以 3,以取得季度 1 到 4。

季度欄

月份名稱

=FORMAT([date],"mmmm")

在此公式中,為取得月份名稱,我們使用 FORMAT (英文) 函數將 [日期] 資料行中的數值轉換為文字。 我們將 [日期] 資料行指定為第一個引數,然後指定格式;我們想要讓月份名稱顯示所有字元,因此使用「mmmm」。 結果看起來像這樣:

月份名稱欄

若要傳回縮寫為三個字母的月份名稱,則在格式引數中使用「mmm」。

星期幾

=FORMAT([date],"ddd")

在此公式中,我們使用 FORMAT 函數取得星期幾名稱。 由於我們只想要縮寫的星期幾名稱,因此在格式引數中指定「ddd」。

星期幾欄
樞紐分析表範例

有了 [年份]、[季度]、[月份] 等日期的欄位後,您就可以在樞紐分析表或報表中使用這些欄位。 例如,下列影像顯示 [銷售] 事實資料表中的 [銷售額] 欄位 (位於 [值] 中),並顯示 [行事曆] 維度資料表中的 [年份] 和 [季度] (位於 [資料列] 中)。 將會彙總年份和季度內容的 [銷售額]。

樞紐分析表範例

會計年度的公式範例

會計年度

=IF([Month]<= 6,[Year],[Year]+1)

在此範例中,會計年度從 7 月 1 日開始。

沒有任何函數可以從日期值擷取會計年度,因為會計年度與日曆年度的開始日期和結束日期通常不一樣。 為取得會計年度,我們先使用 IF (英文) 函數測試 [月份] 的值是否小於或等於 6。 在第二個引數中,如果 [月份] 的值小於或等於 6,則會傳回 [年份] 資料行中的值。 若非如此,則會傳回 [年份] 中的值並加上 1。

會計年度欄

指定會計年度結束月份值的另一種方法是,建立直接指定月份的量值。 例如,FYE:=6。 接著,您可以在月份數字的位置參照量值名稱。 例如,=IF([Month]<=[FYE],[Year],[Year]+1)。 這在多個不同公式中參照會計年度結束月份時會更有彈性

會計月份

=IF([Month]<= 6, 6+[Month], [Month]- 6)

在此公式中,我們指定 [月份] 的值是否小於或等於 6,然後使用 6 並加上 [月份] 中的值,否則是將 [月份] 中的值減掉 6。

會計月份欄

會計季度

=INT(([FiscalMonth]+2)/3)

我們用於 [會計季度] 的公式與用於日曆年度中的 [季度] 公式大致相同。 唯一的差異是我們指定了 [會計月份],而不是 [月份]。

會計季度欄

假日或特殊日期

您可能會想要包含將某些日期顯示為假日或其他特殊日期的日期資料行。 例如,您可能會想要以交叉分析篩選或篩選的形式,將 [假日] 欄位新增到樞紐分析表,以加總新年的銷售總計。 而在某些情況下,您可能會想要在日期資料行或量值中排除這些日期。

包含假日或特殊日期非常簡單。 您可以在 Excel 中建立有您要包含的日期的資料表。 接著,您可以複製或使用 [加入至資料模型],將它新增到 [資料模型],以做為連結資料表。 在大多數情況下,您不需要建立資料表與 [行事曆] 資料表之間的關聯。 任何參照資料表的公式都可以使用 LOOKUPVALUE (英文) 函數來傳回值。

以下是在 Excel 中建立的資料表範例,其中包含要新增到日期資料表的假日:

日期

假日

1/1/2010

新年

11/25/2010

感恩節

12/25/2010

聖誕節

1/1/2011

新年

11/24/2011

感恩節

12/25/2011

聖誕節

1/1/2012

新年

11/22/2012

感恩節

12/25/2012

聖誕節

1/1/2013

新年

11/28/2013

感恩節

12/25/2013

聖誕節

11/27/2014

感恩節

12/25/2014

聖誕節

1/1/2014

新年

11/27/2014

感恩節

12/25/2014

聖誕節

1/1/2015

新年

11/26/2014

感恩節

12/25/2015

聖誕節

1/1/2016

新年

11/24/2016

感恩節

12/25/2016

聖誕節

在日期資料表中,我們建立 [假日] 資料行,並使用像這樣的公式:

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

讓我們更仔細看看這個公式。

我們使用 LOOKUPVALUE 函數取得 [假日] 資料表的 [假日] 資料行中的值。 在第一個引數中,我們指定結果值將位於哪個資料行。 我們指定 [假日] 資料表中的 [假日] 資料行,因為這是我們要傳回的值。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

接著,我們指定第二個引數,也就是有我們要搜尋的日期的搜尋資料行。 我們指定 [假日] 資料表中的 [日期] 資料行,就像這樣:

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

最後,我們指定 [行事曆] 資料表中的資料行,該資料行具有我們要在 [假日] 資料表中搜尋的日期。 這當然就是 [行事曆] 資料表中的 [日期] 資料行。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

[假日] 資料行將傳回具有符合 [假日] 資料表中日期之日期值的每一列的假日名稱。

假日表格

自訂行事曆 - 十三個四週的期間

某些組織 (例如零售業或食品服務業) 通常會根據不同的期間製作報表,例如十三個四週的期間。 在十三個四週期間的行事曆中,每個期間為 28 天,因此每個期間都包含四個星期一、四個星期二、四個星期三,依此類推。 每個期間都包含相同的天數,而且假日通常會落在每年的同一期間內。 您可任意選擇從星期幾開始某個期間。 就像日曆年度或會計年度中的日期一樣,您可以使用 DAX 建立具有自訂日期的額外資料行。

在下列範例中,第一個完整期間從會計年度的第一個星期天開始。 在此情況下,會計年度從 7/1 開始。

此值提供從會計年度中第一個完整週開始的週數。 在此範例中,第一個完整週從星期天開始,因此,[行事曆] 資料表中第一個會計年度的第一個完整週其實是從 7/4/2010 開始,然後持續到 [行事曆] 資料表中的最後一個完整週。 雖然此值本身在分析中並不是那麼實用,但需要用它進行計算,以用於其他 28 天期間的公式。

=INT([date]-40356)/7)

讓我們更仔細看看這個公式。

首先,我們建立以整數形式傳回 [日期] 資料行中的值的公式,就像這樣:

=INT([date])

接著,我們要尋找第一個會計年度中的第一個星期天。 發現的結果是 7/4/2010。

週欄

現在,將該值減掉 40356 (這是 6/27/2010 的整數,也就是上一個會計年度的最後一個星期天),以取得自從 [行事曆] 資料表中天數開始的天數,就像這樣:

=INT([date]-40356)

接著,將結果除以 7 (一週中的天數),就像這樣:

=INT(([date]-40356)/7)

結果看起來像這樣:

週欄

期間

此自訂行事曆中的期間包含 28 天,且一律會從星期天開始。 此資料行會傳回從第一個會計年度的第一個星期天開始的期間數。

=INT(([Week]+3)/4)

讓我們更仔細看看這個公式。

首先,我們建立以整數形式傳回 [週] 資料行中的值的公式,就像這樣:

=INT([Week])

接著,將該值加上 3,就像這樣:

=INT([Week]+3)

接著,將結果除以 4,就像這樣:

=INT(([Week]+3)/4)

結果看起來像這樣:

期間欄

期間會計年度

此值會傳回某個期間的會計年度。

=INT(([Period]+12)/13)+2008

讓我們更仔細看看這個公式。

首先,我們建立一個傳回 [期間] 中的值並加上 12 的公式:

= ([Period]+12)

將結果除以 13,因為會計年度中有十三個 28 天期間:

=(([Period]+12)/13)

加上 2010,因為這是資料表中的第一個年份:

=(([Period]+12)/13)+2010

最後,我們使用 INT 函數移除結果的任何分數,然後在除以 13 時傳回整數,就像這樣:

=INT(([Period]+12)/13)+2010

結果看起來像這樣:

期間會計年度欄

會計年度中的期間

此值會傳回期間數 (1-13),從每個會計年度中的第一個完整期間 (從星期天開始) 開始。

=IF(MOD([Period],13), MOD([Period],13),13)

此公式稍微比較複雜,因此我們會採用比較容易理解的方式說明。 此公式指定將 [期間] 中的值除以 13,以取得年份中的期間數 (1-13)。 如果該數字為 0,則會傳回 13。

首先,我們建立一個公式,傳回根據 13 的 [期間] 中的值的餘數。 我們可以使用 MOD (英文) (數學和三角函數),就像這樣:

=MOD([Period],13)

這通常可以提供我們想要的結果,除了 [期間] 的值為 0 以外,因為這些日期不會落在第一個會計年度內,就像 [行事曆] 日期資料表範例的前五天。 我們可以使用 IF 函數處理這一點。 萬一結果為 0,則傳回 13,就像這樣:

=IF(MOD([Period],13),MOD([Period],13),13)

結果看起來像這樣:

會計年度欄中的期間

樞紐分析表範例

下列影像顯示樞紐分析表,具有 [銷售] 事實資料表中的 [銷售額] 欄位 (位於 [值] 中),以及 [行事曆] 日期維度資料表中的 [期間會計年度] 和 [期間會計年度] 欄位 (以 [資料列] 表示)。 將會根據會計年度和會計年度中的 28 天期間彙總內容的 [銷售額]。

會計年度的樞紐分析表範例

關聯

在 [資料模型] 中建立日期資料表後,如果想要開始在樞紐分析表和報表中瀏覽資料,以及根據日期維度資料表中的資料行彙總資料,您必須在具有交易資料的事實資料表與日期資料表之間建立關聯。

因為您需要根據日期建立關聯,您會想要確定在值屬於 [日期時間] ([日期]) 資料類型的資料行之間建立該關聯。

針對事實資料表中的每個日期值,日期資料表中的相關查詢資料行必須包含相符的值。 例如,[日期索引鍵] 資料行中值為 8/15/2012 12:00 AM 的 [銷售] 事實資料表中的資料列 (交易記錄),在日期 (名為 [行事曆]) 資料表的相關 [日期] 資料行中必須有對應的值。 這是您會想要讓日期資料表中的日期資料行包含連續日期範圍的最重要原因之一,該範圍會包含事實資料表中的任何可能日期。

[圖表檢視] 中的關聯性

附註: 雖然每個資料表中的日期資料行都必須屬於相同的資料類型 ([日期]),每個資料行的格式則無所謂。

附註: 如果 Power Pivot 不允許您建立兩個資料表之間的關聯,日期欄位可能不會將日期和時間儲存為相同的精確度。 根據資料行格式設定,值看起來可能相同,但儲存的方式不同。 深入閱讀處理時間

附註: 避免在關聯中使用整數替代索引鍵。 當您從關聯式資料來源匯入資料時,日期和時間資料行通常會以替代索引鍵表示,這是用來表示唯一日期的整數資料行。 在 Power Pivot 中,您應避免使用整數日期/時間索引鍵建立關聯,並改用包含唯一值且具有日期資料類型的資料行。 雖然傳統資料倉儲將使用替代索引鍵視為最佳做法,但 Power Pivot 中並不需要整數索引鍵,而且會很難根據不同的日期期間將樞紐分析表中的值組成群組。

如果您在嘗試建立關聯時出現 [類型] 不符錯誤,很可能是因為事實資料表中的資料行不屬於 [日期] 資料類型。 當 Power Pivot 無法將非日期 (通常是文字資料類型) 自動轉換為日期資料類型時,可能就會發生這種情形。 您仍可在事實資料表中使用該資料行,但您需在新計算結果欄中使用 DAX 公式轉換資料。 請參閱稍後附錄中的將文字資料類型的日期轉換為日期資料類型

多個關聯

在某些情況下,您可能需要建立多個關聯或建立多個日期資料表。 例如,如果 [銷售] 事實資料表中有多個日期欄位 (例如 [日期索引鍵]、[出貨日期] 和 [退貨日期]),這些欄位全部都能與 [行事曆] 日期資料表中的 [日期] 欄位建立關聯,但只有其中一個可以是作用中的關聯。 在此情況下,由於 [日期索引鍵] 代表交易日期 (因此是最重要的日期),這最適合當做「作用中」的關聯。 其他則是非作用中的關聯。

下列樞紐分析表會根據 [會計年度] 和 [會計季度] 計算總銷售量。 使用 Total Sales:=SUM([SalesAmount]) 公式且名為 [總銷售額] 的量值會放置在 [值] 中,而 [行事曆] 日期資料表中的 [會計年度] 和 [會計季度] 欄位則會放置在 [資料列] 中。

按會計季度樞紐分析表顯示的總銷售額 樞紐分析表欄位清單

這個簡單易懂的樞紐分析表會正確運作,因為我們想要根據 [日期索引鍵] 中的「交易日期」加總總銷售額。 我們的 [總銷售額] 量值使用 [日期索引鍵] 中的日期,並根據會計年度和會計季度加總,因為 [銷售] 資料表的 [日期索引鍵] 和 [行事曆] 資料表的 [日期] 資料行之間有關聯。

非作用中的關聯

但如果我們並不是要根據交易日期加總總銷售額,而是要根據「出貨日期」呢? 我們需要在 [銷售] 資料表的 [出貨日期] 資料行與 [行事曆] 資料表的 [日期] 資料行之間建立關聯。 如果不建立該關聯,彙總一律會根據交易日期。 不過,我們可以有多個關聯 (即使只有一個可以是作用中的關聯),而由於交易日期是最重要的,因此會取得與 [行事曆] 資料表之間的作用中關聯。

在此情況下,[出貨日期] 有非作用中的關聯,因此建立來根據出貨日期彙總資料的任何量值公式都必須使用 USERELATIONSHIP (英文) 函數指定非作用中的關聯。

例如,因為 [銷售] 資料表的 [出貨日期] 資料行與 [行事曆] 資料表的 [日期] 資料行之間有非作用中的關聯,我們可以建立根據出貨日期加總總銷售額的量值。 我們使用像這樣的公式指定要使用的關聯:

Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

此公式只指定:計算 [總銷售額] 的加總,但使用 [銷售] 資料表的 [出貨日期] 資料行與 [行事曆] 資料表的 [日期] 資料行之間的關聯進行篩選。

現在,如果我們建立樞紐分析表,並根據 [出貨日期] 量值將 [總銷售額] 放在 [值] 中,且將 [會計年度] 和 [會計季度] 放在 [資料列] 上,則會看到相同的 [總計],但會計年度和會計季度的所有其他加總金額都是不同的,因為它們是根據出貨日期,而不是根據交易日期。

按運送日期樞紐分析表顯示的總銷售額 樞紐分析表欄位清單

使用非作用中的關聯可讓您只使用一個日期資料表,但它確實需要任何量值 (例如根據 [出貨日期] 的 [總銷售額]) 在其公式中參照非作用中的關聯。 還有另一個替代方法就是使用多個日期資料表。

多個日期資料表

在事實資料表中處理多個日期資料行的另一種方法就是建立多個日期資料表,然後在這些資料表之間建立個別的作用中關聯。 讓我們再次看看 [銷售] 資料表範例。 我們可能想要根據三個資料行的日期彙總資料:

  • 日期索引鍵:每個交易的銷售日期。

  • 出貨日期:將售出項目出貨給客戶的日期和時間。

  • 退貨日期:收到一或多個退回項目的日期和時間。

請記住,具有交易日期的 [日期索引鍵] 欄位是最重要的。 我們會根據這些日期執行大多數彙總,因此我們必定會想要在它和 [行事曆] 資料表的 [日期] 資料行之間建立關聯。 如果我們不想在 [出貨日期] 和 [退貨日期] 與 [行事曆] 資料表的 [日期] 欄位之間建立非作用中關聯 (因而需要特殊量值公式),我們可為出貨日期和退貨日期建立額外的日期資料表。 接著,我們可以在它們之間建立作用中的關聯。

[圖表檢視] 中多個日期表格的關聯性

在此範例中,我們已建立名為 [出貨行事曆] 的另一個日期資料表。 當然,這也表示建立了額外的日期資料行,而由於這些日期資料行位於另一個日期資料表中,因此在命名時,我們想要採用能讓它們與 [行事曆] 資料表的相同資料行有所區隔的方式。 例如,我們已建立 [出貨年份]、[出貨月份]、[出貨季度] 等資料行。

如果我們建立樞紐分析表,並將 [總銷售額] 量值放在 [值] 中,且將 [出貨會計年度] 和 [出貨會計季度] 放在 [資料列] 上,看到的結果則會與建立非作用中關聯和根據 [出貨日期] 導出欄位建立特殊 [總銷售額] 時所看到的結果相同。

按運送日期樞紐分析表與運送行事曆顯示的總銷售額 樞紐分析表欄位清單

這些方法都需要謹慎考量。 搭配單一日期資料表使用多個關聯時,您可能需要使用 USERELATIONSHIP 函數建立轉換非作用中關聯的特殊量值。 另一方面,在欄位清單中建立多個日期資料表可能會造成混淆,而由於您在 [資料模型] 中有更多資料表,因此更需要費力記住它們。 請進行試驗找出最適合您的方式。

[日期資料表] 屬性

[日期資料表] 屬性會設定時間智慧函數需要的中繼資料,例如 TOTALYTD、PREVIOUSMONTH 和 DATESBETWEEN,以便正確運作。 使用這些函數的其中之一執行計算時,Power Pivot 的公式引擎會知道前往何處取得所需的日期。

警告: 如果未設定此屬性,使用 DAX 時間智慧函數的量值可能無法傳回正確的結果。

當您設定 [日期資料表] 屬性時,您需要指定 [日期] ([日期時間]) 資料類型的日期資料表和日期資料行。

[標示為日期表格] 對話方塊

如何:設定 [日期資料表] 屬性

  1. 在 PowerPivot 視窗中,選取 [行事曆] 資料表。

  2. 在 [設計] 索引標籤上,按一下 [標記為日期資料表]。

  3. 在 [標記為日期資料表] 對話方塊中,選取具有唯一值和 [日期] 資料類型的資料行。

處理時間

在 Excel 或 SQL Server 中,所有具有 [日期] 資料類型的日期值其實都是數字。 該數字中包含的是參照時間的位數。 在許多情況下,每一個資料列的時間都是半夜。 例如,如果 [銷售] 事實資料表中的 [日期時間索引鍵] 欄位具有像 10/19/2010 12:00:00 AM 這樣的值,這就表示值是採用日的精確度。 如果 [日期時間索引鍵] 欄位值包含了時間 (例如 10/19/2010 8:44:00 AM),這就表示值是採用 [分] 精確度。 值也能採用 [小時] 精確度,或甚至採用 [秒] 精確度。 時間值中的精確度會大幅影響您建立日期資料表的方式,以及它與事實資料表之間的關聯。

您需要判斷是否會將資料彙總為日精確度或時間精確度。 換句話說,您可能會想要在日期資料表中使用 [早上]、[中午] 或 [小時] 等資料行,做為樞紐分析表的 [資料列]、[資料行] 或 [篩選] 區域中的時間日期欄位。

附註: [日] 是 DAX 時間智慧函數能夠處理的最小時間單位。 如果您不需要使用時間值,您應減少資料的精確度,使用 [日] 做為最小單位。

如果您預計要將資料彙總到 [時間] 精確度,日期資料表則需要包含時間的日期資料行。 事實上,在日期範圍每年的每一天中,它需要每小時一列的日期資料行,或者也許甚至是每分鐘一列。 這是因為如果您想要在事實資料表的 [日期時間索引鍵] 資料行與日期資料表的日期資料行之間建立關聯,您必須有相符的值。 想像一下,如果您包含了許多年份,日期資料表可能會變得很大。

雖然在大多數情況下,您只是想要將資料彙總到 [日] 精確度。 換句話說,您將使用 [年份]、[月份]、[週] 或 [星期幾] 等資料行做為樞紐分析表的 [資料列]、[資料行] 或 [篩選] 區域中的欄位。 在此情況下,如上所述,日期資料表中的日期資料行在一年中只需要包含每天一列。

如果日期資料行包含 [時間] 精確度,但您只要彙總到 [日] 精確度,為了建立事實資料表與日期資料表之間的關聯,您可能必須建立將日期資料行中的值截斷為日值的新資料行,藉此修改事實資料表。 換句話說,將像 10/19/2010 8:44:00AM 這樣的值轉換為 10/19/2010 12:00:00 AM。 接著,您可以在此新資料行與日期資料表的日期資料行之間建立關聯,因為值是相符的。

讓我們看看這個範例。 此影像顯示 [銷售] 事實資料表中的 [日期時間索引鍵] 資料行。 此資料表中的所有資料彙總只需要採用 [日] 精確度,做法是使用 [行事曆] 日期資料表中的資料行,例如 [年份]、[月份]、[季度] 等。值中包含的時間並不相關,只有實際日期才有關聯。

DateTimeKey 欄

由於我們不需要將此資料分析到 [時間] 精確度,因此不需要讓 [行事曆] 日期資料表的 [日期] 資料行在一年的每一天中包含每小時一列和每分鐘一列。 因此,日期資料表中的 [日期] 資料行看起來像這樣:

Power Pivot 中的日期欄

若要在 [銷售] 資料表的 [日期時間索引鍵] 資料行與 [行事曆] 資料表的 [日期] 資料行之間建立關聯,我們可以在 [銷售] 事實資料表中建立新計算結果欄,然後使用 TRUNC (英文) 函數截斷 [日期時間索引鍵] 資料行中的日期和時間值,使日期值符合 [行事曆] 資料表的 [日期] 資料行中的值。 公式看起來像這樣:

=TRUNC([DateTimeKey],0)

這提供了一個新資料行 (名為 [日期索引鍵]),日期是來自 [日期時間索引鍵] 資料行,而每一列的時間為 12:00:00 AM:

DateKey 欄

現在,我們可以在 [行事曆] 資料表中的這個新 ([日期索引鍵]) 資料行與 [日期] 資料行之間建立關聯。

同樣地,我們可以在 [銷售] 資料表中建立計算結果欄,將 [日期時間索引鍵] 資料行的時間精確度減少到 [小時] 精確度。 在此情況下,我們將無法使用 TRUNC 函數,但仍可使用其他 DAX 日期和時間函數,擷取新的值並重新串連到 [小時] 精確度。 我們可以使用像這樣的公式:

= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)

新資料行看起來像這樣:

DateTimeKey 欄

假設日期資料表中的 [日期] 資料行具有 [小時] 精確度的值,就能在它們之間建立關聯。

讓日期更易於使用

您在日期資料表中建立的很多日期資料行對其他欄位來說是必要欄位,但在分析中並不太實用。 例如,我們已參照並在本文示範的 [銷售] 資料表中的 [日期索引鍵] 欄位非常重要,因為針對每個交易,該交易會記錄為在特定的日期和時間發生。 但從分析和報表的觀點來看,並不是那麼實用,因為我們無法在樞紐分析表或報表中將它用來當做資料列、資料行或篩選欄位。

同樣地,事實上在我們的範例中,[行事曆] 資料表中的 [日期] 資料行非常實用且重要,但您無法將它用來當做樞紐分析表中的維度。

為了盡可能保持其中的資料表和資料行的實用性,以及讓樞紐分析表或 Power View 報表欄位清單更易於瀏覽,請務必隱藏用戶端工具中不必要的資料行。 您可能也會想要隱藏某些資料表。 前面顯示的 [假日] 資料表包含假日日期,這對 [行事曆] 資料表中的某些資料行很重要,但您無法將 [假日] 資料表中的 [日期] 和 [假日] 資料行本身用來當做樞紐分析表中的欄位。 同樣地,為了讓 [欄位清單] 更易於瀏覽,您可以隱藏整個 [假日] 資料表。

命名慣例是處理日期的另一個重要層面。 您可以視需要隨時命名 Power Pivot 中的資料表和資料行。 但請記住,尤其是在要與其他使用者共用活頁簿的情況下,良好的命名慣例能讓人更容易找出資料表和日期,此原則不只適用於欄位清單,也適用於 Power Pivot 和 DAX 公式。

[資料模型] 中有了日期資料表後,您可以開始建立量值,協助您充分利用資料。 有些可能像加總目前年份的銷售總額一樣簡單,有些可能比較複雜,需要根據特定的唯一日期範圍進行篩選。 若要深入了解,請參閱 Power Pivot 中的量值 (機器翻譯) 和時間智慧函數 (機器翻譯)。

附錄

將文字資料類型日期轉換為日期資料類型

在某些情況下,含有交易資料的事實資料表包含文字資料類型的日期。 也就是說,顯示為 2012-12-04T11:47:09 的日期其實根本就不是日期,或至少不是 Power Pivot 能夠理解的日期類型。 它只是看起來像是日期的文字。 若要在事實資料表的日期資料行與日期資料表的日期資料行之間建立關聯,兩個資料行都必須是 [日期] 資料類型。

通常當您嘗試將屬於文字資料類型的日期資料行的資料類型變更為日期資料類型時,Power Pivot 可解讀日期並自動將它轉換為真正的日期資料類型。 如果 Power Pivot 無法執行資料類型轉換,則會出現類型不符錯誤。

不過,您仍可將日期轉換為真正的日期資料類型。 您可以建立新計算結果欄,並使用 DAX 公式剖析文字字串中的年份、月份、日、時間等項目,然後將它重新串連為 Power Pivot 能夠讀取為真正的日期的方式。

在此範例中,我們已將名為 [銷售] 的事實資料表匯入 Power Pivot。 它包含名為 [日期時間] 的資料行。 值看起來像這樣:

在事實表格中的 DateTime 欄。

如果我們在 Power Pivot 的 [常用] 索引標籤中查看 [格式設定] 群組的 [資料類型],我們會看到它是 [文字] 資料類型。

功能區中的資料類型

我們無法在日期資料表的 [日期時間] 資料行與 [日期] 資料行之間建立關聯,因為資料類型並不相符。 如果我們嘗試將資料類型變更為 [日期],則會出現類型不符錯誤:

不相符的錯誤

在此情況下,Power Pivot 無法將資料類型從文字轉換為日期。 我們仍可使用此資料行,但如果要讓它變成真正的日期資料類型,我們需要建立新資料行,剖析文字並重新建立為 Power Pivot 能夠視為 [日期] 資料類型的值。

請記住,從本文稍早的<處理時間>一節中,除非您的分析不需要採用一日中的時間的精確度,否則您應將事實資料表中的日期轉換為 [日] 精確度。 秉持這個原則,我們希望新資料行中的值採用 [日] 精確度 (排除時間)。 我們可以將 [日期時間] 資料行的值轉換為日期資料類型,並使用下列公式移除 [時間] 精確度:

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

此提供了新的資料行 (在此情況下,名為 [日期])。 Power Pivot 甚至會偵測到日期的值,並自動將資料類型設為 [日期]。

事實表格中的日期欄

若要保留時間精確度,只需將公式延伸為包含小時、分和秒就可以了。

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

現在,我們有屬於 [日期] 資料類型的 [日期] 資料行,我們可以在日期中建立它與日期資料行之間的關聯。

其他資源

Power Pivot 中的日期

Power Pivot 中的計算

快速入門:30 分鐘學會 DAX 基本概念

資料分析運算式參考資料

DAX 資源中心

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×