建立日期表

  • 9 分鐘

在 Power BI中建立報表期間，常見的業務需求是根據日期和時間計算。 組織希望了解他們的業務在幾個月、幾個季度、一個財政年度等方面的表現如何。 因此，這些面向時間的值的格式正確至關重要。 Power BI 自動偵測日期列和表格；但是，可能會出現您需要採取額外步驟才能取得組織所需格式的日期的情況。

例如，假設您正在為組織中的銷售團隊開發報告。 此資料庫包含銷售、訂單、產品等表。 您注意到，其中許多表格（包括「銷售」和「訂單」）都包含自己的日期列，如 ShipDate 和 OrderDate所示銷售表和訂單表中的 列。 您的任務是開發一個按年份和月份列出的總銷售額和訂單表。 如何建立包含多個表的視覺效果，每個表都引用自己的日期列？

語義模型摘錄的螢幕截圖，其中突出顯示了 Sales.ShipDate 和 Order.OrderDate。

為了解決這個問題，您可以建立一個可供多個表格使用的公共日期表。 以下部分說明如何在 工作 中完成此操作 Power BI。

建立通用日期表

建構通用日期表的方法有：

  • 來源資料

  • DAX

  • Power Query

來源資料

有時，來源資料庫和資料倉儲已經有自己的日期表。 如果設計資料庫的管理員做了周密的工作，這些表格可以用來執行以下任務：

  • 確定公司假期

  • 獨立的日曆和會計年度

  • 確定週末與工作日

來源資料表已經成熟，可以立即使用。 如果您有這樣的表，請將其帶入您的語義模型中，並且不要使用本節中概述的任何其他方法。 我們建議您使用來源日期表，因為它可能與您可能使用的其他工具共用 Power BI。

如果沒有來源資料表，可以使用其他方式建立普通日期表。

DAX

您可以使用資料分析表達式 (DAX) 函數 CALENDARAUTO() 或 CALENDAR() 來建立通用日期表。 CALENDAR() 函數根據在函數中輸入作為參數的開始日期和結束日期傳回連續的日期範圍。 或者，CALENDARAUTO() 函數傳回從語意模型自動決定的連續、完整的日期範圍。 開始日期選擇為語意模型中存在的最早日期，結束日期是語意模型中存在的最晚日期加上已填入會計月份的資料（您可以選擇將其作為參數包含在其中） CALENDARAUTO() 函數。 出於本範例的目的，使用 CALENDAR() 函數是因為您只想查看 2011 年 5 月 31 日（Sales 開始追蹤此資料的第一天）以及接下來 10 年的資料。

在 Power BI Desktop中，選擇 新建表，然後輸入下列 DAX 公式：

Dates  = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))

Power BI中 CALENDAR 公式的螢幕截圖。

現在，您有一列可以使用的日期。 然而，該列稍微稀疏。 您也希望只查看年份、月份、一年中的星期和星期幾的列。 您可以透過選擇功能區上的 新列 並輸入以下 DAX 公式來完成此工作，該公式將從您的日期表中檢索年份。

Year = YEAR(Dates[Date])

使用 DAX 方程式新增列的螢幕截圖。

您可以執行相同的程序來檢索月份號、週號和星期：

MonthNum = MONTH(Dates[Date])

WeekNum = WEEKNUM(Dates[Date])

DayoftheWeek = FORMAT(Dates[Date], "DDDD")

完成後，您的表格將包含下圖所示的列。

DAX 表中最終列的螢幕截圖。

現在您已經使用 DAX 建立了一個通用日期表。 這個過程只​​是將你的新表添加到語義模型中；您仍然需要在日期表與銷售和訂單表之間建立關係，然後將您的表標記為語義模型的正式日期表。 但是，在完成這些任務之前，請確保考慮建立通用日期表的另一種方法：使用 Power Query。

Power Query

您可以使用 M 語言（用於在 Power Query 中建立查詢的開發語言）來定義公共日期表。

在 中選擇 轉換資料 Power BI Desktop，這將引導您前往 Power Query。 在左側 查詢 窗格的空白處，右鍵點選開啟下列下拉選單，在其中選擇 新建查詢 > 空白查詢。

在 Power BI 中建立新查詢的螢幕截圖。

在產生的 新查詢 檢視中，輸入下列 M 公式來建立日曆表：

= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))

使用 M 公式開發日曆表的螢幕截圖。

對於您的銷售數據，您希望開始日期能反映數據中的最早日期：2011 年 5 月 31 日。 此外，您還希望查看未來 10 年的日期，包括未來的日期。 此方法可確保當新的銷售資料流入時，您無需重新建立此表。 您也可以變更持續時間。 在本例中，您需要每天的資料指向，但您也可以按小時、分鐘和秒遞增。 下圖顯示了結果。

銷售日曆清單的螢幕截圖。

當您在過程中取得成功後，您會注意到您有一個日期清單而不是日期表。 若要更正此錯誤，請轉至 轉換 功能區上的選項卡並選擇 轉變 > 到餐桌。 顧名思義，此功能會將您的清單轉換為表格。 您也可以將該列重命名為 日期列。

將清單轉換為表格的螢幕截圖 Power Query 編輯。

接下來，您想要為新表格新增列以查看年、月、週和日的日期，以便您可以在視覺效果中建立層次結構。 您的第一個 工作 是透過選擇列名稱旁的圖示並在出現的下拉式選單中選擇 日期 類型來變更欄類型。

迄今為止更改類型的螢幕截圖。

選擇 日期 類型後，您可以新增年、月、週和日的列。 前往 新增列，選擇 日期下的下拉式選單，然後選擇 年份，如下圖所示。

透過 Power Query新增列的螢幕截圖。

請注意， Power BI 新增了從 DateCol中提取的所有年份的列。

在表格上新增帶有 Power Query 的列的螢幕截圖。

數月、數週、數天完成相同的過程。 完成此程序後，該表將包含下圖所示的列。

DateCol、Year、Month、Yeek of Year 和 Day Name 欄位的螢幕截圖。

您現在已成功使用 Power Query 建立了通用日期表。

前面的步驟展示如何將表格放入語意模型中。 現在，您需要將表格標記為官方日期表格，以便 Power BI 可以識別所有未來值並確保格式正確。

標記為官方日期表

將表格標記為官方日期表的第一個 工作 是在 欄位 窗格中尋找新表格。 右鍵點選表格名稱，然後選擇 標記為日期表，如下圖所示。

“標記為​​日期表”選項的螢幕截圖。

透過將表標記為日期表， Power BI 執行驗證以確保資料包含零個空值、唯一且包含一段時間內的連續日期值。 您也可以選擇表格中的特定欄位來標記為日期，這在表中有許多欄位時非常有用。 右鍵點選表格，選擇 標記為日期表格，然後選擇 日期表格設定。出現，您可以在其中選擇應將哪一列標記為 日期 。

“標記為​​日期表”對話框的螢幕截圖。

選擇 標記為日期表 將從您標記為日期的表中的 日期 欄位中刪除自動產生的層次結構桌子。 對於其他日期字段，自動層次結構仍將存在，直到您在該字段與日期表之間建立關係或關閉 自動日期/時間 功能。 您可以手動為常用日期表新增層次結構，方法是右鍵點選 欄位 窗格中的年、月、週或日列，然後選擇 新層次結構。

建立你的視覺效果

要在「銷售」和「訂單」表之間建立視覺化，您需要在這個新的公共日期表與「銷售」和「訂單」表之間建立關係。 因此，您將能夠使用新的日期表來建立視覺效果。 要完成此工作，請前往 模型 選項卡 >管理關係，您可以在其中建立公共日期之間的關係使用 OrderDate 列來建立表格以及Orders 和Sales 表。 以下螢幕截圖顯示了一種此類關係的範例。

「建立關係」對話方塊的螢幕截圖。

建立關係後，您可以使用使用 DAX 或 開發的通用日期表來建立 按時間劃分的總銷售額和訂單數量 Power Query 視覺物件。方法。

要確定總銷售額，您需要新增所有銷售額，因為「銷售額」表格中的 金額 列僅查看每次銷售的收入，而不是總銷售收入。 您可以使用以下度量計算來完成此工作，這將在後面的討論中進行解釋。 建構此度量時將使用的計算如下：

#Total Sales = SUM(Sales[‘Amount’])

完成後，您可以透過返回 視覺化 標籤並選擇 表格 視覺物件來建立表格。 您希望按年和月查看總訂單和銷售額，因此您只需包含日期表中的「年」和「月」列、 OrderQty 列和 #TotalSales 衡量。 當您了解層次結構時，您還可以建立一個層次結構，讓您 向下切入 從幾年到幾個月。 對於本範例，您可以並排查看它們。 現在您已成功建立具有公共日期表的視覺物件。

使用 DAX 的通用日期列的螢幕截圖。