以性能為考量的 DAX 進階時間智慧

我們都知道時間智慧的基本功能，通常是根據年份、季度、月份和天數來計算。但有時候，我們需要進行更複雜的時間智慧計算。在編寫這些計算時，我們也要考慮到效能。

介紹

在 Power BI 中有許多 DAX 函數用於時間智慧計算。

最常見的函數有：

你可以在這裡找到完整的時間智慧函數列表：時間智慧 – DAX 指南。這些函數涵蓋了最常見的情況。

然而，有些需求無法輕易用這些函數來滿足。這就是我們要討論的地方。

我想分享一些我在專案中遇到的情況，包括：

• 最近 n 期間的計算及其變體
• 如何處理閏年
• 本週至今的計算
• 計算每週的總和
• 財務週的年初至今（YTD）

我會展示如何使用擴展的日期表來支持這些情況，並提高效率和效能。

大多數時間智慧函數在財務年度與日曆年度不一致的情況下仍然有效。唯一的例外是年初至今（YTD）。

對於這種情況，請查看上面提到的 DATESYTD() 函數。在那裡，你會找到一個可選的參數，可以傳遞財務年度的最後一天。

最後一個案例將涵蓋基於週的計算，而財務年度與日曆年度不一致。

情境

我將使用知名的 ContosoRetailDW 數據模型。

基礎度量是線上銷售總和，代碼如下：

線上銷售總和 = SUMX('線上銷售',

 ( '線上銷售'[單價]

            * '線上銷售'[銷售數量] ) 

                         - '線上銷售'[折扣金額] )

我幾乎會專注於 DAX-Studio，它提供了伺服器計時功能來分析 DAX 代碼的效能。在下面的參考部分，你可以找到一篇關於如何收集和解釋 DAX Studio 中效能數據的文章鏈接。

這是我在示例中使用的基本查詢，以從數據模型中獲取一些數據：

評估 

 CALCULATETABLE( 

 SUMMARIZECOLUMNS('日期'[年份] 

 ,'日期'[月份簡稱] 

,'日期'[週] 

,'日期'[日期] 

,"線上銷售", [線上銷售總和] 

) 

 ,'產品'[產品類別名稱] = "電腦" ,'產品'[產品子類別名稱] = "筆記型電腦" 

,'客戶'[大陸] = "北美" 

 ,'客戶'[國家] = "美國"  ,'客戶'[州/省] = "德克薩斯州" )

在大多數示例中，我會移除一些過濾器，以獲得更完整的數據（針對每一天）。

日期表

我的日期表包含相對較多的附加列。

在下面的參考部分，你可以找到一些由 SQLBI 撰寫的文章，關於建立與週相關的計算，包括創建日期表來支持這些計算。

如我在下面提到的日期表文章中所述，我添加了以下列：

• 索引或偏移列，用於計算從當前日期起的天數、週數、月份、季度、學期和年份。
• 標記列，用於標記當前的天、週、月、季度、學期和年份，根據當前日期。
• 這些列需要每日重新計算，以確保使用正確的日期作為參考日期。
• 每週和每月的開始和結束日期（如有需要可添加更多）。
• 財務年度的開始和結束日期。
• 前一年日期，以包括當前期間的開始和結束日期。這對於週特別有趣，因為每年的週的開始和結束日期並不相同。

如你所見，我將廣泛使用這些列來簡化我的計算。

此外，我們將使用日曆層級來計算不同層級的所需結果。

完整的日曆層級包含：

• 年份
• 學期
• 季度
• 月份
• 天

或者

• 年份
• 週
• 天

如果財務年度與日曆年度不一致，我會用財務年度來建立層級，而不是日曆年度。

然後，我添加了一個單獨的財務月份名稱列和一個財務月份排序列，以確保財務年度的第一個月份顯示在最前面。

好了，讓我們開始第一個案例。

最近 n 期間

這個情境計算過去 n 期間的滾動總和。

例如，對於每一天，我們想要獲得最近 10 天的銷售額：

這是我想到的度量：

線上銷售（最近 10 天） = 

 CALCULATE (

 [線上銷售總和] 

 ,DATESINPERIOD ( 

 '日期'[日期], 

MAX ( '日期'[日期] ), 

-10, 

DAY 

 ) 

 )

當我執行查詢並過濾電腦和北美時，我得到這個結果：

如果我查看伺服器計時，結果還不錯：

如你所見，儲存引擎完成了一半以上的工作，這是一個好兆頭。雖然不是完美，但執行時間少於 100 毫秒，從效能的角度來看，仍然非常好。

這種方法有一個重要的問題：

當計算多個月份的滾動總和時，你必須知道這種方法是以日期為導向的。

這意味著當你查看特定時間時，它會回到給定月份的同一天。例如：

我們查看 2024 年 1 月 12 日，想要計算最近三個月的滾動總和。這個計算的起始日期將是 2023 年 11 月 13 日。

當我們想要獲得整個月份的滾動總和時，該如何處理呢？

在上述情況中，我希望將起始日期設為 2023 年 11 月 1 日。

對於這種情況，我們可以使用月份索引列。

每一列都有一個基於當前日期的唯一索引。

因此，我們可以用它來回溯三個月，獲得整個月份。

這是 DAX 代碼：

線上銷售滾動完整三個月 = 

 VAR CurDate = 

 MAX ( '日期'[日期] ) 

 VAR CurMonthIndex = 

 MAX ( '日期'[月份索引] ) 

 VAR FirstDatePrevMonth = 

 CALCULATE ( 

 MIN ( '日期'[日期] ), 

 REMOVEFILTERS ( '日期' ), 

 '日期'[月份索引] = CurMonthIndex - 2 

 ) 

 RETURN 

 CALCULATE ( 

 [線上銷售總和], 

 DATESBETWEEN ( 

 '日期'[日期], 

FirstDatePrevMonth, 

CurDate 

 ) 

 )

執行仍然很快，但效率較低，因為大多數計算無法由儲存引擎執行：

我嘗試了其他方法（例如，’日期'[月份索引] >= CurMonthIndex – 2 && ‘日期'[月份索引] <= CurMonthIndex），但這些方法的效果不如這種方法。

這是相同邏輯的結果，但針對最近兩個月（為了避免顯示過多行）：

關於閏年

閏年的問題很奇怪，這在計算每一天的前一年時尤其明顯。讓我解釋一下：

當我執行以下查詢以獲取 2020 年和 2021 年 2 月的最後幾天時：

評估 

CALCULATETABLE ( 

 SUMMARIZECOLUMNS ( 

 '日期'[年份], 

 '日期'[月份簡稱], 

 '日期'[月份鍵],

 '日期'[日], 

 "線上銷售", [線上銷售總和], 

 "線上銷售（PY）", [線上銷售（PY）] 

 ), 

 '日期'[年份] IN {2020, 2021}, 

 '日期'[月份] = 2, 

 '日期'[日] IN {27, 28, 29}, 

 '客戶'[大陸] = "北美", 

 '客戶'[國家] = "美國" 

) 

 ORDER BY '日期'[月份鍵], 

 '日期'[日]

我得到以下結果：

如你所見，2020 年 2 月 28 日的結果顯示了兩次，而 2021 年 2 月的線上銷售（PY）缺少一天。

查看月份時，總和是正確的：

問題在於 2021 年沒有 2 月 29 日。因此，當列出每日的銷售額時，無法顯示 2020 年 2 月 29 日的銷售額。

雖然結果是正確的，但當數據導出到 Excel 並進行求和時，日常結果的總和將與整個月份顯示的結果不同。

這可能會削弱用戶對數據可靠性的感知。

我的解決方案是添加一個閏年日期表。這個表是日期表的副本，但沒有日期列。我每年在 2 月 29 日添加一行，即使在非閏年也如此。

然後，我為每個月份和日期添加了一個計算列（月份日）：

月份日 = ('閏年日期'[月份] * 100 ) + '閏年日期'[日]

計算前一年的線上銷售的度量如下：

線上銷售（PY 閏年） = 

 VAR ActYear = 

 SELECTEDVALUE ( '閏年日期'[年份] ) 

 VAR ActDays = 

 VALUES ( '閏年日期'[月份日] ) 

 RETURN 

 CALCULATE ( 

 [線上銷售總和], 

 REMOVEFILTERS ( 閏年日期 ), 

 '閏年日期'[年份] = ActYear - 1, 

 ActDays 

 )

如你所見，我獲得了當前年份，並通過使用 VALUES() 函數獲得當前過濾上下文中的所有日期列表。

使用這種方法，我的度量適用於單日、月份、季度和年份。這個度量的結果如下：

如你所見，這個度量非常有效，因為大部分工作由儲存引擎完成：

但老實說，我不喜歡這種方法，儘管它運作得很好。

原因是閏年日期表沒有日期列。因此，它不能用作現有時間智慧函數的日期表。

我們還必須在可視化中使用這個表的日曆列。無法使用普通的日期表。

因此，我們必須重新發明所有時間智慧函數來使用這個表。

我強烈建議僅在必要時使用這種方法。

本週至今和 PY

一些業務領域專注於每週分析。

不幸的是，標準的時間智慧函數不支持每週分析。因此，我們必須自己建立每週度量。

第一個度量是 WTD。

第一種方法如下：

線上銷售 WTD v1 = 

 VAR MaxDate = MAX('日期'[日期]) 

  

 VAR CurWeekday = WEEKDAY(MaxDate, 2) 

  

 RETURN 

 CALCULATE([線上銷售總和] 

 ,DATESBETWEEN('日期'[日期] 

 ,MaxDate - CurWeekDay + 1  ,MaxDate) 

 )

如你所見，我使用 WEEKDAY() 函數來計算週的開始日期。然後，我使用 DATESBETWEEN() 函數來計算 WTD。

當你將這個模式調整到你的情況時，必須確保 WEEKDAY() 中的第二個參數設置為正確的值。請閱讀文檔以了解更多信息。

結果如下：

另一種方法是將每週的第一天存儲在日期表中，並在度量中使用這個信息：

線上銷售 WTD PY v2 = 

 VAR DayOfWeek = MAX('日期'[星期幾]) 

  

 VAR FirstDayOfWeek = MIN('日期'[第一天的週日期PY])   

 RETURN 

 CALCULATE([線上銷售總和] 

 ,DATESBETWEEN('日期'[日期] 

 ,FirstDayOfWeek 

,FirstDayOfWeek + DayOfWeek - 1) 

 )

結果完全相同。

在 DAX Studio 中分析效能時，我看到這兩個度量的表現相當：

我傾向於使用第二個，因為它在與其他度量結合時具有更好的潛力。但最終，這取決於當前情境。

另一個挑戰是計算前一年。

看看不同年份相同週的以下日期：

如你所見，日期是偏移的。由於標準的時間智慧函數是基於日期的偏移，因此它們將無法正常工作。

我嘗試了不同的方法，但最終我在日期表中存儲了前一年相同週的第一天，並像上面第二版本的 WTD 一樣使用它：

線上銷售 WTD PY = 

 VAR DayOfWeek = MAX('日期'[星期幾]) 

  

 VAR FirstDayOfWeek = MIN('日期'[第一天的週日期PY])   

 RETURN 

 CALCULATE([線上銷售總和] 

 ,DATESBETWEEN('日期'[日期]

 ,FirstDayOfWeek 

,FirstDayOfWeek + DayOfWeek - 1) 

 )

這是結果：

由於邏輯與 WTD v2 相同，因此效能也相同。因此，這個度量非常有效。

每週的總和 PY

有時，每週的視圖就足夠了，我們不需要在每日層級計算 WTD。

在當前年度的情況下，我們不需要 WTD 度量。基礎度量按週切片即可涵蓋這一點。結果是正確的。

但對於前一年來說，又是另一回事。

這是我想到的第一個版本：

線上銷售（PY 每週） v1 = 

 VAR ActYear = MAX('日期'[年份]) 

  

 RETURN 

 CALCULATE([線上銷售總和] 

 ,ALLEXCEPT('日期' 

 ,'日期'[週] 

) 

 ,'日期'[年份] = ActYear - 1 

 )

在這裡，我從當前年份減去一，同時保留當前週的過濾器。這是結果：

效能良好，但我可以做得更好。

如果我能在日期列中存儲唯一的週識別碼呢？

例如，當前週是 2025 年的第 9 週。

識別碼將是 202509。

當我減去 100 時，我得到 202409，這是前一年同一週的識別碼。在日期表中添加這一列後，我可以將度量更改為：

度量 '所有度量'[線上銷售（PY 每週） v2] = 

VAR WeeksPY = VALUES('日期'[週鍵PY]) 

RETURN 

CALCULATE([線上銷售總和]

,REMOVEFILTERS('日期') 

,'日期'[週鍵] IN WeeksPY 

)

這個版本比之前簡單得多，結果仍然相同。

當我們比較這兩個版本的執行統計時，我們看到：

如你所見，第二個版本，使用日期表中的預計算列，效率稍高。我只有四個 SE 查詢，這是提高效率的好兆頭。

財務週 YTD

最後一個案例是棘手的。

需求是用戶希望看到從財務年度第一週的第一天開始的 YTD。

例如，財務年度從 7 月 1 日開始。

在 2022 年，包含 7 月 1 日的那一週從 6 月 27 日的星期一開始。

這意味著 YTD 計算必須從這個日期開始。

對於前一年的 YTD 計算也是如此，從 2021 年 6 月 28 日的星期一開始。

這種方法在可視化數據時會有一些後果。

再次強調，知道結果是否必須在日或週層級顯示是至關重要的。當在日層級顯示數據時，當選擇財務年度時，結果可能會令人困惑：

如你所見，星期五是財務年度的第一天。YTD 結果並不是從 7 月 1 日開始，而是從那一週的星期一開始。

這樣會導致 YTD 看起來沒有正確開始。用戶必須知道他們在查看什麼。

對於前一年的 YTD 結果也是如此。

為了方便計算，我在日期表中添加了更多列：

• 財務年度週年份——此字段包含財務年度的數字表示（例如 23/24，我得到 2324），從財務年度的第一週開始。
• 財務年度週年份PY——與之前相同，但針對前一年（財務年度週年份 – 101）。
• 財務週排序——此排序列使週從財務年度的第一天開始。使用此列的更複雜方法可以遵循 ISO-週的定義，但我沒有這樣做，以保持簡單。
• 財務年度週排序——與之前相同，但在前面加上財務年度週年份（例如 232402）。
• 第一天的週日期——當前日期所在週的星期一的日期。

以下是每日 YTD 的度量：

線上銷售（財務週 YTD） =

VAR 財務年度週年份 = MAX('日期'[財務年度週年份])

VAR 開始財務年度 = CALCULATE(MIN('日期'[日期])

,REMOVEFILTERS('日期')

,'日期'[財務年度週排序] =



財務年度週年份 * 100 + 1



)



VAR 財務年度開始週日期 = CALCULATE(MIN('日期'[第一天的週日期])

,ALLEXCEPT('日期'

,'日期'[財務年度週年份]

)

,'日期'[日期] = 開始財務年度



)

VAR MaxDate = MAX('日期'[日期])

RETURN

CALCULATE([線上銷售總和]

,REMOVEFILTERS('日期')



,DATESBETWEEN('日期'[日期]

,財務年度開始週日期



,MaxDate

)

以下是每日 YTD PY 的 DAX 代碼：

線上銷售（財務週 YTD） (PY)] =

VAR 財務年度週年份 = MAX('日期'[財務年度週年份])

-- 獲取當前財務年度開始的週/星期幾

VAR 財務年度開始 = CALCULATE(MIN('日期'[日期])

,REMOVEFILTERS('日期')

,'日期'[財務年度週排序] =



財務年度週年份 * 100 + 1

)

VAR MaxDate = MAX('日期'[日期])

-- 獲取自財務年度開始以來的天數

VAR DaysFromFiscalYearStart =

DATEDIFF( 財務年度開始, MaxDate, DAY )

-- 獲取財務年度週開始日期的 PY 日期

VAR DateWeekStartPY = CALCULATE(MIN('日期'[日期])

,REMOVEFILTERS('日期')

,'日期'[財務年度週排序] =



(財務年度週年份 - 101) * 100 + 1

)

RETURN

CALCULATE(

[線上銷售總和],

DATESBETWEEN(

'日期'[日期],

DateWeekStartPY,

DateWeekStartPY + DaysFromFiscalYearStart



)

)

如你所見，這兩個度量遵循相同的模式：

• 獲取當前財務年度。
• 獲取當前財務年度的開始日期。
• 獲取財務年度第一週的開始日期。
• 根據這兩個日期之間的差異計算結果。

對於 PY 度量，還需要一個額外的步驟：

• 計算開始和當前日期之間的天數，以計算正確的 YTD。這是必要的，因為年份之間的日期偏移。

以下是每週基於 YTD 的 DAX 代碼：

線上銷售（財務週 YTD） =

VAR 財務週排序 = MAX( '日期'[財務週排序] )

-- 獲取當前財務年度開始的週/星期幾

VAR 財務年度編號 = MAX( '日期'[財務年度週年份] )



RETURN

CALCULATE(

[線上銷售總和],

REMOVEFILTERS('日期'),

'日期'[財務年度週排序] >= (財務年度編號 * 100 ) + 1

&& '日期'[財務年度週排序] <= (財務年度編號 * 100 ) +

財務週排序

)

對於每週的 YTD PY，DAX 代碼如下：

線上銷售（財務週 YTD） (PY) =

VAR 財務週排序 = MAX( '日期'[財務週排序] )

-- 獲取當前財務年度開始的週/星期幾

VAR 財務年度編號PY = MAX( '日期'[財務年度週年份PY] )

RETURN

CALCULATE(

[線上銷售總和],

REMOVEFILTERS('日期'),

'日期'[財務年度週排序] >= (財務年度編號PY * 100) + 1

&& '日期'[財務年度週排序] <= (財務年度編號PY * 100) +

財務週排序

)

再次強調，這兩個度量遵循相同的模式：

• 獲取財務年度中的當前（排序）週數。
• 獲取財務年度第一週的開始日期。
• 根據這些值計算結果。

每週基於度量的結果如下（在每週層級，因為同一週的每一天的值相同）：

當比較這兩種方法時，每週計算的度量比每日計算的度量更有效：

如你所見，每週結果的度量更快，儲存引擎（SE）執行的部分更多，且 SE 查詢更少。

因此，詢問用戶他們是否需要每日層級的 WTD 結果，或者是否只需查看每週層級的結果，可能是個好主意。

結論

當你開始編寫時間智慧表達式時，考慮在你的日期表中是否可以添加更多計算列。

精心設計和擴展的日期表對於兩個原因是有幫助的：

• 使度量更容易編寫
• 提高度量的效能

它們將更容易編寫，因為我不需要進行計算以獲得中間結果來計算所需的結果。

較短和簡單的度量的結果是更好的效率和效能。

隨著我遇到更多情況，我將在我的日期表模板中添加更多列，以便它們能夠提供幫助。

還有一個問題：如何構建它？

在我的案例中，我使用 Azure SQL 數據庫創建了用於示例的表。

但也可以創建 DAX 表，或者在 Fabric 中使用 Python 或 JavaScript，或使用你所用的任何數據平台。

另一個選擇是使用 SQLBI 的 Bravo 工具，它允許你創建包含附加列的 DAX 表，以支持複雜的時間智慧情境。

參考文獻

你可以在這裡找到有關我的日期表的更多信息。

閱讀這篇文章以了解如何在 DAX-Studio 中提取效能數據以及如何解釋它。

SQLBI 文章關於建立日期表以支持每週計算：在 Power BI 中使用每週日曆 – SQLBI

SQLBI 模式以進行進一步的每週計算：

與週相關的計算 – DAX 模式

如同我之前的文章，我使用 Contoso 樣本數據集。你可以從 Microsoft 這裡免費下載 ContosoRetailDW 數據集。

Contoso 數據可以根據這裡的 MIT 許可證自由使用。

我更改了數據集以將數據移至當代日期。