理解資料表

上一頁
下一頁

資料分析涉及到隨著時間的推移瀏覽資料並在時間段內進行計算。例如，您可能需要將今年的利潤與去年的利潤進行比較。同樣，您可能需要預測未來幾年的增長和利潤。為此，您需要對一段時間進行分組和聚合。

DAX提供了許多時間智慧函式，可幫助您執行大多數此類計算。但是，這些DAX函式需要一個日期表才能與資料模型中的其他表一起使用。

您可以從資料來源匯入日期表以及其他資料，也可以在資料模型中自己建立日期表。

在本章中，您將瞭解日期表的不同方面。如果您熟悉Power Pivot資料模型中的日期表，則可以跳過本章並繼續後續章節。否則，您可以瞭解Power Pivot資料模型中的日期表。

什麼是日期表？

日期表是資料模型中的一個表，其中至少包含一列在所需持續時間內連續的日期。它可以包含表示不同時間段的其他列。但是，DAX時間智慧函式需要的是連續日期列。

例如，

  • 日期表可以包含日期、財務月份、財務季度和財務年份等列。

  • 日期表可以包含日期、月份、季度和年份等列。

包含連續日期的日期表

假設您需要在日曆年的範圍內進行計算。然後，日期表必須至少包含一列連續的日期集，包括該特定日曆年中的所有日期。

例如，假設您要瀏覽的資料的日期範圍是從2014年4月1日至2016年11月30日。

  • 如果您需要按日曆年進行報表，則需要一個包含“日期”列的日期表，該列按順序包含從2014年1月1日至2016年12月31日的所有日期。

  • 如果您需要按財年進行報表，並且您的財年結束日期是6月30日，則需要一個包含“日期”列的日期表，該列按順序包含從2013年7月1日至2017年6月30日的所有日期。

  • 如果您需要同時按日曆年和財年進行報表，則可以使用一個跨越所需日期範圍的日期表。

您的日期表必須包含給定持續時間內每一年範圍內的所有日期。因此，您將在該時間段內獲得連續的日期。

如果您定期使用新資料重新整理資料，則結束日期將延長一兩年，以便您不必經常更新日期表。

日期表如下面的螢幕截圖所示。

Data Table

將日期表新增到資料模型

您可以透過以下任何一種方式將日期表新增到資料模型中：

  • 從關係資料庫或任何其他資料來源匯入。

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

  • 從Microsoft Azure Marketplace匯入。

在Excel中建立日期表並複製到資料模型

在Excel中建立日期表並複製到資料模型是建立資料模型中日期表最簡單和最靈活的方式。

  • 在Excel中開啟一個新工作表。

  • 在一列的第一行鍵入“日期”。

  • 在同一列的第二行鍵入要建立的日期範圍的第一個日期。

  • 選擇單元格，單擊填充柄並將其向下拖動以建立所需日期範圍內的連續日期列。

例如，鍵入2014/1/1，單擊填充柄並向下拖動以填充直到2016/12/31的連續日期。

  • 單擊“日期”列。
  • 單擊功能區上的“插入”選項卡。
  • 單擊“表格”。
  • 驗證表格範圍。
  • 單擊“確定”。

包含單列日期的表格已在Excel中準備好。

Single Column of Dates
  • 選擇表格。
  • 單擊功能區上的“複製”。
  • 單擊Power Pivot視窗。
  • 單擊功能區上的“貼上”。
Paste

這會將剪貼簿的內容新增到資料模型中的新表中。因此，您也可以使用相同的方法在現有資料模型中建立日期表。

貼上預覽對話框出現，如下面的螢幕截圖所示。

  • 在“表名”框中鍵入“日期”。
  • 預覽資料。
Paste Preview
  • 選中“使用第一行作為列標題”複選框。
  • 單擊“確定”。

這會將剪貼簿的內容複製到資料模型中的新表中。

現在，您在資料模型中有一個包含單列連續日期的日期表。列的標題為“日期”，如您在Excel表格中所給定的。

向日期表新增新的日期列

接下來，您可以根據計算需求向日期表新增計算列。

例如，您可以新增“日”、“月”、“年”和“季度”列，如下所示：

  • 日

    =DAY('Date'[Date])

  • 月

    =MONTH('Date'[Date])

  • 年

    =YEAR('Date'[Date])

  • 季度

    =CONCATENATE ("QTR ", INT (('Date'[Month]+2)/3))

資料模型中生成的日期表如下面的螢幕截圖所示。

Resulting Date table

因此，您可以向日期表新增任意數量的計算列。重要的是，日期表必須包含一列跨越執行計算的時間段的連續日期。

為日曆年建立日期表

日曆年通常包括一年中的1月1日至12月31日，還包括該特定年份的節假日。在執行計算時，您可能需要只考慮工作日，不包括週末和節假日。

假設您要為2017日曆年建立日期表。

  • 建立一個包含“日期”列的Excel表格，該列包含從2017年1月1日至2017年12月31日的連續日期。（請參閱上一節瞭解如何執行此操作。）

  • 複製Excel表格並將其貼上到資料模型中的新表中。（請參閱上一節瞭解如何執行此操作。）

  • 將表格命名為“日曆”。

  • 新增以下計算列：

    • 日 =DAY('Calendar'[Date])

    • 月 =MONTH('Calendar'[Date])

    • 年 =YEAR('Calendar'[Date])

    • 星期幾 =FORMAT('Calendar'[Date],"DDD")

    • 月份名稱 =FORMAT('Calendar'[Date],"MMM")

Adding Calculated Columns

向日歷表新增節假日

按如下方式向日歷表新增節假日：

  • 獲取該年份宣佈的節假日列表。

  • 例如，對於美國，您可以從以下連結獲取任何所需年份的節假日列表 http://www.calendar-365.com/。

  • 複製並將其貼上到Excel工作表中。

  • 複製Excel表格並將其貼上到資料模型中的新表中。

  • 將表格命名為“節假日”。

Name Table Holidays

  • 接下來，您可以使用DAX LOOKUPVALUE函式向日歷表新增一個計算的節假日列。

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],'Calendar'[Date])

DAX LOOKUPVALUE函式在第二個引數Holidays[Date]中搜索第三個引數Calendar[Date]，如果匹配則返回第一個引數Holidays[Holiday]。結果將如下面的螢幕截圖所示。

Lookupvalue

向財年新增列

財年通常包括從財年結束後的第一個月的第一天到下一個財年結束的日期。例如，如果財年結束日期是3月31日，則財年範圍是從4月1日至3月31日。

您可以使用DAX公式將財務時間段包含在日曆表中：

  • 為FYE新增一個度量值

    FYE:=3

  • 新增以下計算列：

    • 財年

      =IF('Calendar'[Month]<='Calendar'[FYE],'Calendar'[Year],'Calendar'[Year]+1)

    • 財務月份

      =IF('Calendar'[Month]<='Calendar'[FYE],12-'Calendar'[FYE]+'Calendar'[Month],'Calendar'[Month]-'Calendar'[FYE] )

    • 財務季度

      =INT(('Calendar'[Fiscal Month]+2)/3)

Adding Columns to Fiscal Year

設定日期表屬性

當您使用DAX時間智慧函式（如TOTALYTD、PREVIOUSMONTH和DATESBETWEEN）時，它們需要元資料才能正常工作。日期表屬性設定此類元資料。

要設定日期表屬性：

  • 在Power Pivot視窗中選擇“日曆”表。
  • 單擊功能區上的“設計”選項卡。
  • 單擊“日曆”組中的“標記為日期表”。
  • 單擊下拉列表中的“標記為日期表”。
Setting the Date Table Property

出現“標記為日期表”對話方塊。在“日曆”表中選擇“日期”列。這必須是日期資料型別的列，並且必須具有唯一值。單擊“確定”。

Mark as Date Table
列印頁面
上一頁
下一頁
廣告