時間智能是DAX語言的一個強大且重要的功能。時間智能 可以在不修改日期篩選器的情況下編寫引用不在可視化對象中的時間段公式。我們看下圖，矩陣顯示的是2018年的銷售額。外部篩選器要把年份添加到篩選字段，選擇2018年。

如果我們想要2017年的數(shù)據(jù)跟2018年的數(shù)據(jù)對比，就要把年份字段放到矩陣列上得到結(jié)果。但這樣做可能不是最好的方法;如果不重復這個過程就無法在其他矩陣中得到相同的結(jié)果。另外，你不能用這種方法計算某一年與前一年銷售相比的變化情況。

1、使用時間智能函數(shù) Using Time Intelligence Functions

時間智能函數(shù)可以創(chuàng)建與某一時間點相關聯(lián)的度量值 。例如：有一個"去年銷售額"的度量值，就可以展示以某個時間點為參照的去年的銷售額，這個時間點可以在可視化對象中選擇。相對時間智能函數(shù)也非常有用，用它可以顯示比較周期，而不必更改矩陣中的日期選擇。使用時間智能函數(shù)能讓一切都變得簡單，還可以構(gòu)建想要的視覺對象效果，否則就不行了。

DAX語言中有不少內(nèi)置時間智能函數(shù)，當然你也可以在需要時書寫自己的時間智能函數(shù)。時間智能的應用場景太多了，內(nèi)置時間智能函數(shù) 不可能滿足每一個需要的場景。所以DAX內(nèi)置的時間智能函數(shù)使用起來還要符合一定的運行環(huán)境。

Here are a few of the rules for using inbuilt time intelligence functions:

? You must have a Calendar table that contains a contiguous range of dates that covers every day in the period you are analysing.

? The Calendar table must be at the day level of granularity.

? Every date must exist once and only once in the Calendar table.

? You can’t skip any dates. (For example, you can’t skip weekend dates just because your data doesn’t include weekends.)

? The Calendar table must start at the beginning of the year of your first data point and extend to the end of the year of your last data point.

? Inbuilt time intelligence works only on a standard calendar (where the months are January, February,?

etc.).

? You can customize a standard calendar for different financial years. (For example, you can set the end date for a calendar to be June 30 or any other date instead of December 31.)

?要有一個標準的日期表，日期表包含一個連續(xù)的日期范圍，涵蓋你正在分析的數(shù)據(jù)期間的每一天。

?日期表必須有"每天"級別的粒度。

?每個日期必須在Calendar表中存在且僅存在一次(日期必須為不重復值)。

?日期不能有間隔。(例如，您不能因為事實表中的日期不包括周末而跳過周末日期)

?Calendar表必須從第一個數(shù)據(jù)點的年初開始，并擴展到最后一個數(shù)據(jù)點的年底。

?內(nèi)置的時間智能只適用于標準日歷(其中月份是一月，二月等)。

?您可以為不同的財年定制一個標準日歷。(例如，您可以將日歷的結(jié)束日期設置為6月30日或任何其他日期，而不是12月31日。)

如果因為某些原因日期表不符合上述條件，那么就別使用內(nèi)置時間智能函數(shù)了。異或時間智能函數(shù)不能正常運行時，就要書寫自己的時間智能度量值，通常還是使用CALCULATE和FILTER函數(shù)。書寫自己的時間智能函數(shù)可能比較復雜，但也不必擔心。

2、非標準日歷 Nonstandard Calendars

In some cases, you may need to use a nonstandard calendar for your reports. A standard calendar would not work in situations like the following:

? With a calendar that uses times as well as dates (e.g., an hourly calendar).

? When you are building a data model using weekly or monthly data, so you use a weekly or monthly calendar instead of a daily calendar .

Note: You could load your weekly or monthly data and still use a daily calendar—and this would work with inbuilt time intelligence as long as all the other criteria were still met. To make it work, your weekly or monthly data would use a single date to represent the time period (e.g., end-of-week date, end-of-month date), and you would then join this date to the standard Calendar table. I consider this to be a hack and not best practice, but it can work. I recommend that you instead learn to do it properly, using custom time intelligence, as described later in this chapter .

? If you use an ISO or 445 calendar for your accounting periods. This is very common in the retail indus-try, where businesses want to have regular trading periods with full weeks from Monday to Sunday. In a 445 calendar, there are 2 months that consist of 4 calendar weeks followed by 1 month with 5 calendar weeks. This helps smooth the months so they all start on a Monday and finish on a Sunday (for example) while also having 91 days in each quarter (91 × 4 quarters = 364 days).

? With 13 4-week periods instead of calendar months.

某些場合你可能要在報表中使用非標準的日期表。標準的日期表在下列情況下不工作。

?當你使用每周或每月的數(shù)據(jù)構(gòu)建數(shù)據(jù)模型時，你將使用每周或每月日期表而不是每天的日期表。

NOTE: 你可以加載每周或每月的數(shù)據(jù)，同時仍然使用標準日期表——只要滿足上一節(jié)所說的其他條件，這時內(nèi)置的時間智能函數(shù)也能正常工作。為了讓模型正常工作，你的每周或每月數(shù)據(jù)將使用單個日期來表示時間段(例如，周末日期代表當周、月末日期代表當月)，然后將此日期連接到標準日期表表。但這是一個坑，不是最好的辦法，雖然也能運行。我還是建議大家要學習如何正確地書寫和使用自定義時間智能度量值，這個本章后面會給大家介紹。

?零售行業(yè)中的會計期間經(jīng)常會使用ISO或445日期表，這個很常見，是因為企業(yè)總是需要周一到周日一整周的定期交易周期期。

在445日歷中，其中有2個月由4個日歷周組成，有1個月由5個日歷周組成。這種設置每個月都是從周一開始，在周日結(jié)束，同時每個季度也有91天(91 × 4個季度= 364天)，更便于規(guī)律地數(shù)據(jù)周期計算。

?13個4周的周期，而不是日歷月

?日期中既有日期又有時間的(例如，每小時日歷)。

還有很多種情況我們不能一一都列舉出來，對PBI來說內(nèi)置的時間智能函數(shù)也不可能滿足所有情形?？偠灾?，如果模型中有標準的日期表，就可以使用內(nèi)置的時間智能函數(shù)，如果沒有，就用CALCULATE() and FILTER()函數(shù)編寫定制化的時間智能公式。

3、如何關閉自動日期? Here's How: Turning Off Auto Date/Time

Power BI有一個叫做自動日期/時間的功能，可以幫助初學者在報表中使用日、月、季度和年而不加載自己的日歷表。個人不喜歡這個功能，因為它會自動為數(shù)據(jù)中的每個日期列創(chuàng)建一個隱藏的日期表。每個日期表都是獨立的，它們不能協(xié)同工作。此外，盡管這些日期表隱藏在視圖之外，但它們可能不只有一個或兩個，會使你的數(shù)據(jù)模型變得非常大。我們正在學習編寫DAX，那我們還是使用專用日期表完成報告吧，我也建議大家關閉自動日期/時間這個功能。操作起來也不復雜

1) 選擇文件，選項和設置選項。

2) 導航到全局部分并選擇數(shù)據(jù)加載。

3) 取消新文件的自動日期/時間。

4) 如果你的工作簿已經(jīng)開啟了自動日期/時間功能，那么你可以通過選擇當前文件部分并選擇數(shù)據(jù)加載來關閉它。

4、內(nèi)置時間智能 Inbuilt Time Intelligence

1?? 使用連續(xù)日期區(qū)間? ?Using a Contiguous Date Range?

示例數(shù)據(jù)中，Calendar表已經(jīng)包含了Sales表的所有日期，要確定日期表的所有日期包含Sales表的所有日期也很容易的：創(chuàng)建一個新矩陣，將日期表的年份放在行上，將日期表月份名稱放到矩陣的值上，在值字段單擊下拉箭頭并更改設置，讓值顯示為計數(shù)。

矩陣的值顯示的是每年的天數(shù)，除2016年是半年以外，其它年份是全年的日期。這里我們使用了隱式度量值這個功能，雖然之前跟大家說過盡量不用隱式度量值，但用它來快速的核對一下數(shù)據(jù)也是很方便的。用隱式度量值并不是錯誤的方法，只是隱式度量值不能被引用，也不能修改名字和結(jié)果的數(shù)據(jù)類型，用起來不太方便而已。如果是用于核對數(shù)據(jù)，核對完成以后從矩陣中刪除這個值字段即可，也不會帶來什么麻煩。

NOTE:從技術角度來講，日期表應該涵蓋完整的年份數(shù)據(jù)(每年數(shù)據(jù)都是從年度的第一天連續(xù)到當年最后一天)，并且包括模型中的所有日期。但從實際經(jīng)驗來看，我們可以對日期表做點微小的調(diào)整，例如開始于模型中的最早日期，結(jié)束于模型中的最晚日期，盡管這樣做可能會帶來一點點小的風險。如果你能了解并避開存在的風險，那你可以這樣做，否則的話還是使用完整的日期表吧。

2?? 使用去年同期函數(shù)? Using SAMEPERIODLASTYEAR()

語法：SAMEPERIODLASTYEAR(<日期列>)? ? ? ? // 去年同期時間智能函數(shù)

返回：一個表，其中包含指定參數(shù)日期列中的日期在當前上下文中前一年的日期列。

備注：

? dates 參數(shù)可以是以下任一項 ：

對日期/時間列的引用，

返回單列日期/時間值的表表達式，

定義日期/時間值的單列表的布爾表達式。

? 有關對布爾表達式的約束，可參照 CALCULATE參數(shù)中對布爾表達式的要求。

? 返回的日期和等效公式 DATEADD(dates, -1, year) 返回的日期相同

? 在已計算的列或行級安全性 (RLS) 規(guī)則中使用時，不支持在 DirectQuery 模式下使用此函數(shù)

示例：計算上年同期銷售額

先建立一個矩陣，把年份放在行上，把總銷售度量值放在值上

右鍵單擊SALES表，選擇新建度量值，在公式欄輸入

Total Sales LastYear =

? ? CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))

輸入公式時，智能提示顯示：SAMEPERIODLASTYEAR返回的是當前上下文日期(矩陣的日期行字段)前一年的日期，CALCULATE再用這個日期篩選出SALES表，再計算[Total Sales]。

NOTE: 在智能提示中，SAMEPERIODLASTYEAR()將單個Dates參數(shù)作為其唯一參數(shù)輸入，其實DAX所有的內(nèi)置時間智能函數(shù)都需要這個Dates參數(shù)，它總是指向Calendar表中的Date列。

3?? 去年同期"函數(shù)是如何工作的? ?How does SAMEPERIODLASTYEAR() Work?

在第15章中，我們介紹了CALCULATE()可以接受一個表作為高級篩選參數(shù)(CALCULATE的第二參數(shù))，你可以想象成這個新表連接到數(shù)據(jù)模型，然后在CALCULATE()計算之前，CALCULATE()內(nèi)的這個表篩選數(shù)據(jù)模型中的其它表(在本例中是Calendar表和Sales表)。SAMEPERIODLASTYEAR()的工作過程和CALCULATE完全相同，如下所示:

Total Sales LastYear =

? ? CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Calendar'[Date]))

在這個示例中，SAMEPERIODLASTYEAR()返回一個日期表，這個日期表與當前矩陣中的行字段日期相比都是向前提前了一年。將度量值放到矩陣中，可以看到：標綠的年份是2018年，這是矩陣的初始篩選，篩選出日期表中2018年的所有日期；CALCULATE度量值放到矩陣后，SAMEPERIODLASTYEAR('Calendar'[Date]) 函數(shù)將矩陣篩選出的2018年全年日期全部變成提前一年的日期，這個日期表(提前了一年的日期)再篩選銷售表中日期與它相等的行出來，最后度量值用第一參數(shù)計算篩選出表的銷售額，即為2017年的銷售額。標紅的值與標綠的年數(shù)據(jù)就能顯示在一行，（矩陣行字段當前年的數(shù)據(jù)與前一年數(shù)據(jù)就在一行顯示了），更便于做數(shù)據(jù)對比。

為了便于理解，您可以想象由SAMEPERIODLASTYEAR()創(chuàng)建的新表是位于Calendar表之上的臨時表(我們叫它"上年日期"表)，并保持與原始Calendar表的關系，如下所示。(請記住，這是邏輯上的工作方式;實際上你看不到這個表格。)

"上年日期"表傳遞給CALCULATE()，然后CALCULATE()通過模型中的關系將"上年日期"表做為篩選表傳遞給日期表(Calendar表)，然后Calendar表在計算[Total Sales LY]之前篩選Sales表，最后再計算銷售額。

4?? 計算年初至今銷售額? ?Calculating Year-to-Date Sales

實際工作中有個常見的業(yè)務需求，就是計算年初至今(YTD)的數(shù)值。DAX有一個內(nèi)置的計算年初至今函數(shù)YTD。在寫YTD公式之前，應該創(chuàng)建一個新矩陣，如果公式代碼符合需求，矩陣會立即反饋給你一個正確的結(jié)果。

下面我們創(chuàng)建一個2018年的月度為行字段的矩陣，大家要注意，在外部篩選上我們選擇了CalendarYear = 2018。

外部篩選中我們篩選年份=2018年，矩陣里面月份數(shù)據(jù)為2018年每月的銷售額。如果我們不篩選年份=2018，那矩陣中每個月的數(shù)據(jù)就是所有年份的當月銷售額。

要計算累計值，我們可以這樣寫度量值

Total Sales YTD = TOTALYTD([Total Sales], 'Calendar'[Date])

通過做差計算[Total Sales YTD]兩個月的對應值，跟[Total Sales]當前行的值比較，就可以看出累計值是如何顯示的。

年初至今語法:

= TOTALYTD(表達式，CALENDAR[Date], 篩選器,指定年度的結(jié)束日期)

第一參數(shù)：表達式，一般為度量值

第二參數(shù)：日期表的日期列

第三參數(shù)：可選參數(shù)篩選器，可以指定哪一年，或者哪個產(chǎn)品等

第四參數(shù)：可選參數(shù)，指定年度結(jié)束的于哪一天(也就是指定所謂年初至今的年結(jié)束于哪一天)

用法1：只篩選部分連續(xù)月時的所有年份年初至今銷售額

Total Sales YTD = TOTALYTD([TOTAL SALES],'Calendar'[Date])? //所有年份的累計

用法2：固定年份年初至今銷售額--只顯示2018年的數(shù)據(jù)

Determined Year YTD = TOTALYTD([TOTAL SALES],'Calendar'[Date],'Calendar'[CalendarYear]=2018)? ?//指定年份的年初至今累計值

用法3：財年開始日期不等于標準日期開始日期

?? 改變財年結(jié)束日期 Changing Financial Year-Ending Dates

有些業(yè)務場景不是按通常的日期做為年度結(jié)束日期的，這時如果需要正確完成計算，許多內(nèi)置的時間智能函數(shù)功能允許我們指定特定日期為年終日期。YTD系列函數(shù)中第三參數(shù)就是用來指定年終日期的，這個參數(shù)是可選參數(shù)(不寫此參數(shù)時年終日期為當年12月31日):

Total Sales FYTD =?

? ? ? ? ? ? TOTALYTD([Total Sales], 'Calendar'[Date], "30/6")? //年度最后一天修改為6月30號

練習：月初至今和季初至今

Total Sales Month to Date

Total Sales QTD = TOTALQTD([TOTAL SALES],'Calendar'[Date])

Total Sales Quarter to Date

Total Sales MTD = TOTALMTD([TOTAL SALES],'Calendar'[Date])

?? 如何設置不讓日期列默認聚合? Setting Calendar Columns Not to Aggregate

這個對所有表的數(shù)值類型的列都可以進行設置

方法1：單列設置，選中日期表的不想要聚合的列，在列工具菜單->∑摘要,選擇不匯總，這樣只能一次設置一列

方法2：一次設置多列不默認聚合

轉(zhuǎn)到模型視圖，在字段區(qū)選中并展開想要修改的表的列，按住Ctrl再依次選擇需要設置的列名->屬性區(qū)域下拉滾動條，點開"高級"，點擊匯總依據(jù)選擇框的下拉箭頭，選擇“無”選項。

?? 其它時間智能函數(shù) Practicing with Other Time Intelligence Functions

Previous? 上一個系列

語法：PREVIOUSYEAR(<dates>[,<year_end_date>])??

基于當前上下文中的“日期”列中的最后一個日期，返回一個表，該表包含上一年所有日期的列 。

參數(shù) 釋義

date?：包含日期的一個列。

year_end_date （可選）:帶有日期的文本字符串，用于定義年末日期。 默認值為 12 月 31 日。

備注：

? 此函數(shù)基于輸入?yún)?shù)中給定的最新日期返回上一年的所有日期。 例如，如果“日期”參數(shù)中的最新日期指的是 2009 年，則此函數(shù)返回 2008 年的所有日期，直到指定的“year_end_date”為止 。

? dates 參數(shù)可以是以下任一項 ：

對日期/時間列的引用。

返回單列日期/時間值的表表達式。

定義日期/時間值的單列表的布爾表達式。

? 有關對布爾表達式的約束，參照 CALCULATE函數(shù)中的相關描述。

? year_end_date 參數(shù)是日期的字符串文本，采用的區(qū)域設置與創(chuàng)建工作簿的客戶端的區(qū)域設置相同 。 日期的年份部分會被忽略。

? 在已計算的列或行級安全性 (RLS) 規(guī)則中使用時，不支持在 DirectQuery 模式下使用此函數(shù)。

示例代碼：

Previous Year = PREVIOUSYEAR('Calendar'[Date])

Total Sales Previous Year =CALCULATE([TOTAL SALES],PREVIOUSYEAR('Calendar'[Date]))

Previous Momth = PREVIOUSMONTH('Calendar'[Date])

Total Sales Previous Momth =CALCULATE([TOTAL SALES], PREVIOUSMONTH('Calendar'[Date]))

Previous Day = PREVIOUSDAY('Calendar'[Date])

Total Sales Previous Day = CALCULATE([TOTAL SALES],PREVIOUSDAY('Calendar'[Date]))

5?? 使用內(nèi)置的DATEADD函數(shù)? Using the DATEADD() Inbuilt Function

The DAX inbuilt time intelligence function DATEADD() can be used to give exactly the same results as some of the formulas already covered in this chapter . The following example shows how to use DATEADD():

Total Sales Previous Quarter 2 =CALCULATE([Total Sales],?? DATEADD('Calendar'[Date],MAX('Calendar'[Date]),-1,QUARTER)?)

Microsoft developers have really tried hard to provide simple ways of writing DAX, even for those who have minimal knowledge of how the language works. Instead of having to learn about CALCULATE() and DATEADD(), you can leverage the power of DAX time intelligence by using simple functions like the ones at the start of this chapter . But CALCULATE() is lurking under the hood. CALCULATE() and its cousin CALCULATETABLE() are the only DAX functions that can change filtering behaviour. If there are changes to filtering and you can’t see a CALCULATE() in the function, then you are working with a syntax sugar formula. SAMEPERIODLASTYEAR(), PREVIOUSMONTH(), PREVIOUSDAY(), and PREVIOUSQUARTER() are all syntax sugar versions of the DATEADD() function.

我們可以使用DAX內(nèi)置的時間智能函數(shù)DATEADD()把本章中已經(jīng)寫過的一些公式寫出等效公式，下面的例子展示了如何使用DATEADD()

Total Sales Previous Quarter 2 =?CALCULATE([Total Sales], DATEADD('Calendar'[Date],MAX('Calendar'[Date]),-1,QUARTER))

初學DAX的用戶可能對DATEADD()的語法覺得不好理解，公式中MAX()的使用也難以掌握，以后會給大家解釋。到目前為止，我們在本章中介紹的許多函數(shù)實際上都是DATEADD()的語法糖。微軟開發(fā)人員確實在努力提供編寫DAX的簡單方法，即使那些對DAX的工作原理知之甚少的人也是如此。如果不學CALCULATE()和DATEADD()，我們可以通過本章開頭的簡單函數(shù)利用DAX時間智能的強大功能，但是CALCULATE()其實是始終潛伏在時間智能函數(shù)的底層的，CALCULATE()和它的兄弟calculattable()是唯一可以改變篩選行為的DAX函數(shù)。如果對篩選進行了更改，并且在函數(shù)中看不到CALCULATE()，那么您正在使用的公式寫法叫語法糖公式。SAMEPERIODLASTYEAR()， PREVIOUSMONTH()， PREVIOUSDAY()和PREVIOUSQUARTER()都是DATEADD()函數(shù)的語法糖版本。

5、寫個自己的時間智能函數(shù) Writing Your Own Time Intelligence Functions

As mentioned earlier in this chapter, writing your own time intelligence functions is a bit harder than using the inbuilt functions, particularly when you are new to the concepts. When you use an inbuilt time intelligence function, DAX works out the dates needed to complete the time shift for you. When you write your own time intelligence functions, it is up to you to determine how to select the new set of dates required to complete the time shift. When you get the hang of it, you will find it quite traightforward (and that will be a good sign of how much progress you are making in understanding DAX).There are a couple of strange things in the syntax that you need to get your head around before you can fully understand what you’re doing with custom time intelligence. I explain these things now, and you will be writing your own custom time intelligence functions in no time at all.

These are the two concepts you need to get your head around:

? Concept 1: Thinking “whole of table” when thinking about filter context

? Concept 2: Knowing how to use MIN() and MAX()

The following sections cover these concepts and provide examples that will help you cement what you learn.

正如本章前面提到的，編寫自己的時間智能函數(shù)比使用內(nèi)置函數(shù)要困難一些，特別是當你對某些概念還不太熟悉時。我們使用DAX內(nèi)置的時間智能函數(shù)時，DAX會為我們計算出完成時間轉(zhuǎn)換所需的日期。當編寫自己的時間智能函數(shù)時，我們要自己決定如何選擇完成日期推移所需的新日期集合。一旦你掌握了它的使用竅門，你會發(fā)現(xiàn)它非常簡單(這也是一個標志，表明你在理解DAX方面已經(jīng)取得了很大進步)。在完全理解如何使用自定義時間智能函數(shù)之前，語法中有一些東西需要搞搞清楚。下面兩個理念要刻在腦海里：

Concept 1: Thinking “Whole of Table” When Thinking About Filter Context?

在考慮篩選上下文時考慮“整個表”。?

Concept 2: Knowing how to use MIN() and MAX()

知道如何使用MIN()和MAX()

下面給大家介紹這兩個概念，并提供示例演示。

??理念1 ：在考慮篩選上下文時考慮“整個表”。

這個圖是本章開始時的一個矩陣，篩選區(qū)年份選擇2018，矩陣中紅框為1月數(shù)據(jù)，兩個篩選器結(jié)合起來的篩選條件就是2018年1月，從總體上來看這行數(shù)據(jù)對整個日期表篩選后的日期就是2018年1月的日期，這也是報表頁面的初始篩選，這個篩選對整個模型適用。

我們可以想像經(jīng)過初始篩選的日期表 Month = January 和 Year = 2018的部分只會有31行數(shù)據(jù)，同時日期表的其它列也會也還在，這就是我們對年份和月份篩選后，也同時對表的其它列進行了篩選。

?? 理念2 理解如何使用MIN 和MAX

在編寫自定義的時間智能函數(shù)時，在FILTER函數(shù)中常常會用到MIN和MAX函數(shù)。

NOTE:如果愿意，你也可以使用FIRSTDATE()和LASTDATE()函數(shù)，這兩個函數(shù)本書不做過多介紹，只介紹一下MIN和MAX函數(shù)的用法。

重點：DAX公式中只要對列使用聚合函數(shù)，它總會服從自來可視化對象的初始篩選。我們再看一下剛才的矩陣

紅框部分日期是從1月1號到1月31號，我們寫兩個公式

= MIN('Calendar'[Date])

= MAX('Calendar'[Date])

這兩個公式放在度量值中，也會服從矩陣的當前篩選上下文，想想如果把這兩個公式放到矩陣值上會得到什么？

可以看到，對于矩陣中的每個行字段， MIN('Calendar'[Date]) 都是當前月的第一天，MAX('Calendar'[Date])都是當前月的最后一天，外部篩選年份我們先的是2018年，所以矩陣值的年份也都是2018年。這也證明了度量值中對列的聚合服從可視化對象的初始篩選，可視化對象先篩選，聚合函數(shù)再運算。

所以我們可以把MIN和MAX 想像成從當前篩選上下文中取值的工具，它可以使用在任何可用的表列上，并且取到的值可以使用在DAX公式中。

NOTE: DAX 和EXCEL中的MIN和MAX還是有區(qū)別的，DAX允許這兩個函數(shù)使用在非數(shù)值列上，如果我們寫MIN('Calendar'[DayName])，它返回的是Friday，它們是按文本的首字母先后順序來判斷大小的，在前者判斷為較小的。

NOTE: Whenever you use an aggregation function around a column in a DAX formula, it will always respect the initial filter context coming from the visual.So you can think of MIN() and MAX() as tools that can “harvest” the value from the current filter context, in any available column across the whole table (and any other table for that matter), and you can use the harvested values in your DAX formulas. Remember this fact about MIN() and MAX() when you get into the examples below.Whenever you use an aggregation function around a column in a DAX formula, it will always respect the initial filter context coming from the visual.

6、編寫自定義時間智能函數(shù) Writing Custom Time Intelligence Functions

自定義每年年初至今寫法：(對于一年或多年數(shù)據(jù))

Total Sales YTD Manual =?CALCULATE([Total Sales],FILTER(ALL('Calendar'),'Calendar'[CalendarYear] = MAX('Calendar'[CalendarYear])

&& 'Calendar'[Date] <= MAX('Calendar'[Date]))? ? )? //結(jié)果為每年的年初至今的累計

公式中FILTER函數(shù)返回一個表給函數(shù)CALCULATE。然后，CALCULATE對這個日期表使用一個篩選器，并在計算[Total Sales]之前將這個篩選傳遞到Sales表。讓我們看看FILTER()函數(shù)中的第5行和第6行代碼，第5行: 'Calendar'[CalendarYear] = MAX('Calendar'[CalendarYear])，“日歷年怎么能等于日歷年的MAX值 ?”實際情況是，等號的左邊是一個列名，右邊是一個MAX函數(shù)值。剛才我們講過，MIN()或MAX()公式始終服從當前篩選上下文。因此這個公式第5行的運行過程如下:“向表中添加一個篩選器，使列'Calendar'[CalendarYear]等于來自矩陣的當前篩選上下文中的最大值?！崩缭谙旅娴木仃囍校怀鲲@示的行的最大日期是2018年3月31日，因此MAX('Calendar'[CalendarYear]) = 2018。

現(xiàn)在看看如何使用“整張表”的理念。初始過濾器上下文是2018年3月，但MAX公式在CalendarYear列上工作。我們想象這個篩選器上下文作用于整個數(shù)據(jù)模型中的表，Calendar表中還有31行，對于每一行，'Calendar'[CalendarYear]中的值都是2018。因此對'Calendar'[CalendarYear]使用MIN也將返回2018 。換言之，使用SUM和AVERAGE也將返回2018年。第5行實際上是在說，篩選這個表，其中'Calendar'[CalendarYear]等于當前篩選上下文的年，這個例子里是2018。如果就此停止，公式將返回當前年度(2018年)的總銷售額，但這不是我們所需要。對于矩陣中突出顯示的行，我們只想要截止到3月底的銷售額。

? ? ?我們繼續(xù)往下看。第6行代碼以雙&操作符開始(DAX中是并且的意思，表示第5行和第6行結(jié)果要同時滿足)，然后:'Calendar'[Date] <= MAX('Calendar'Date])這里與第5行相同。MAX('Calendar'[Date])從矩陣中讀取初始篩選上下文，公式的這一部分添加了一個AND條件，以便對底層表進行篩選，以確定'Calendar'[CalendarYear] = 2018，以及'Calendar'[Date]是否在2018年3月31日或之前。

? ? ?我們繼續(xù)往下看。當轉(zhuǎn)到矩陣可視化中的下一行時，日歷年保持不變，但月底日期移動到了下個月的月底。因此，當你在矩陣中按行往下移動時，日期表所包含的天數(shù)就是矩陣當前行單位的月的天數(shù)。

? ? ?我們來看看第4行代碼 FILTER中 的ALL('Calendar')參數(shù)，前面我們提到過: 公式中對列使用聚合函數(shù)時，公式也服從可視化對象的初始篩選，第5和6行代碼也要被矩陣行(月份)篩選，這樣的話就不好了，日期表不是連續(xù)延長了，每月又變成了一段。在第14章我們講過ALL函數(shù)，它可以清除外部篩選，我們把它放在FILTER的第一參數(shù)。我們希望將1月和2月的銷售額包含在3月實際銷售額里，就必須首先刪除由矩陣創(chuàng)建的篩選器。這就是ALL()在第4行中作用在Calendar表時所做的 : 它清除了應用于Calendar表的矩陣篩選器上下文，先刪除篩選器(使用ALL())，然后重新應用第5行和第6行中使用的篩選器，最終便得到所有日期的YTD。

拓展：看看下面這樣寫會得到什么結(jié)果？

Total Sales YTD Manual2 =?CALCULATE([Total Sales],FILTER(ALL('Calendar'),? ? ? ? 'Calendar'[Date] <= MAX('Calendar'[Date])))? ? ?//結(jié)果變成了從事實表第一天開始至今的累計

You can see in this matrix that this formula is giving the sales for the current month rather than YTD in each row of the matrix. The reason it doesn’t work is related to the initial filter context discussed earlier . For the March 2018 row, the initial filter context applied a filter so that only the 31 days of March 2018 were “visible” in the Calendar table (behind the scenes). So how can the formula possibly return sales for all days “year to date,” including the sales from January and February, if only March remains due to the existing filters? The dates in January and February were already filtered out by the initial filter context coming from the matrix, so you can’t get the sales for these months to somehow reappear for the new formula if you write the formula this way.

If you want to include sales from January and February in the row next to the actual sales for March, you must first remove the filter created by the matrix. This is what ALL() does when it is wrapped around the Calendar table in line 5: It removes the filter context that comes from the matrix that is applied to the Calendar table. You first remove the filters (using ALL()) and then reapply the filters you want to use in lines 6 and 7 so that you end up with all the dates YTD.

在這個矩陣中可以看到，這個公式給出的是當月的銷售額，而不是矩陣中每一行的YTD。函數(shù)不起作用的原因與前面討論的初始篩選器上下文有關。對于2018年3月行，初始篩選器上下文應用了一個篩選器，以便在Calendar表中只有2018年3月的31天是像是“可見的”(其實是要想象出來這個么可見的日期)。那么，如果現(xiàn)有的篩選只保留了3月，那么該公式怎么也不可能返回“年初至今”所有日期的銷售了，也不會包括1月和2月的銷售，因為1月和2月的日期已經(jīng)被來自矩陣的初始篩選上下文給扔掉了，所以如果按照這種方法寫公式，就無法讓這幾個月的銷售額重新出現(xiàn)在新公式中。

如果希望將1月和2月的銷售額包含在前3月實際銷售額旁邊的一行中，則必須首先刪除由矩陣創(chuàng)建的篩選，這就是ALL()在第5行代碼中對Calendar表所做的:它刪除了來自于Calendar表的矩陣的篩選。首先刪除矩陣的行篩選(使用ALL)，然后重新應用您想在第6行和第7行中使用的篩選，以便最終得到所有日期YTD。

?? 使用ID列編寫時間智能函數(shù) Using ID Columns for Time Intelligence

When I talk about ID columns, I am talking about columns for unique IDs. The benefit of such an ID column is that it gives you a nice clean numeric column to move back and forward through time inside your formulas using DAX.

一個好的日期表一般會包含一個ID列，以唯一地標識希望在報表中使用的時間段，特別是在編寫自定義時間智能函數(shù)時。一個很常用的例子是月份的ID列。我們說的月份的ID列不是月份的數(shù)字像1月2月…，而是從日期表的第一個月的1開始的序號一直延續(xù)的ID號，在日期表中每增加一個都增加1，每個ID都與日期表的一個月份一一對應。在編寫自定義時間智能函數(shù)時，這樣的ID列非常有用，特別是使用非標準日歷(如ISO日歷或445日歷)時。這種ID列的好處是：它能為我們提供一個整潔的數(shù)字列，以便在使用DAX公式中隨著時間前后移動。為了說明這一點，在本節(jié)中，我將展示如何編寫一個計算滾動6個月銷售額的公式。像之前一樣，我們先建立一個矩陣來幫助說明。

在下圖中，可以看到矩陣在行上有CalendarYear和MonthName，在值上有[Total Sales]度量值和Max of Month ID隱式度量值(見下面#1)。為了創(chuàng)建月ID的隱式度量Max，我簡單地將月ID列拖到Values部分，并將聚合更改為Maximum(參見#2)。下圖顯示了矩陣中每個月的月份ID是如何遞增的——當然，不包括年份的總行數(shù)(#3)。

從上圖可知，2017年12月的月份ID是18，要寫出一個顯示2017年12月之前6個月銷售額的公式，我們需要從2017年7月到2017年12月的所有數(shù)據(jù)。換一種說法就是，需要從ID月13到ID月18的所有數(shù)據(jù)。看到了嗎?當你有個一個好的視覺對象幫助時，頭腦更容易理解要解決的問題。

還記得在DAX中使用MAX()函數(shù)從表中“獲取”值的技巧嗎?上圖使用了一個隱式度量，相當于在DAX中寫入MAX([Month ID])，并返回每個月的月份ID。

請記住:

?MAX([Month ID])得到總數(shù)所需的最后一個月ID(在本例中是2017年12月，或ID月18)

?MAX([Month ID]) - 5得到總數(shù)所需的開始月份ID(在本例中是2017年7月，或ID月13)

有了上一節(jié)的經(jīng)驗，我們寫個度量值

Rolling Sales 6Months by MID = CALCULATE([TOTAL SALES],?FILTER(ALL('Calendar'),

? ? 'Calendar'[Month ID]<MAX('Calendar'[Month ID])?? && 'Calendar'[Month ID]>'Calendar'[Month ID]-5))

TOTAL SALES = SUM('Sales'[ExtendedAmount])??

??使用日期平移函數(shù) Using DATESINPERIOD

語法：

DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)

包含單列日期值的表，可以從某一日期按指定的時間間隔往前推/推遲 多少天/月/季/年，

四個參數(shù)均為必選參數(shù)，第二參數(shù)為一個日期表達式，一般會使用MAX(calendar[Date])或Min(calendar[Date])做為日期推遲或推后的起點。

參數(shù) 定義

<dates>?：日期列。

<start_date>?：日期表達式。

<number_of_intervals>?：一個整數(shù)，指定要添加到 dates 或從 dates 中減去的時間間隔數(shù)。為正值或負值時根據(jù)第二參數(shù)聚合函數(shù)各類不所不同

<interval>?：日期偏移的間隔。 interval 的值可以是以下值之一：DAY、MONTH、QUARTER 和 YEAR

用DATESINPERIOD推遲或提前一段日期的5種寫法

Rolling 6 Months Sales 2 =? ? CALCULATE (?[Total Sales],

?DATESINPERIOD ('Calendar'[Date],?

MAX ('Calendar'[Date]),?

-6, MONTH

)

?)

?Total Sales Moving Annual Total =CALCULATE([Total Sales],?

? ? ? ? ? ? ? ? FILTER(ALL('Calendar'),

? ? ? ? ? ? ? ? ? ? 'Calendar'[Date] > MAX('Calendar'[Date]) - 365

? ? ? ? ? ? ? ? ? ? && 'Calendar'[Date] <= MAX('Calendar'[Date])

? ? ? ? ? ? ? ? )

? ? ? ? ? ? )???? ? ??? //不用時間智能函數(shù)寫法

?Total Sales Rolling 90 Days =? ?IF(MAX('Calendar'[ID])>=90,

? ? ? ? ? ? ? ? CALCULATE([Total Sales],

? ? ? ? ? ? ? ? ? ? FILTER(ALL('Calendar'),

? ? ? ? ? ? ? ? ? ? ? ? 'Calendar'[Date] > MAX('Calendar'[Date]) - 90

? ? ? ? ? ? ? ? ? ? ? ? && 'Calendar'[Date] <= MAX('Calendar'[Date])

? ? ? ? ? ? ? ? ? ? )

? ? ? ? ? ? ? ? )

? ? ? ? ? ? )

Total Sales MAT Improved =?

? ? ? ? ? ? IF(MAX('Calendar'[Date])>=DATE(2017,7,1),? ? ? ? ?//判斷第一年是否是滿一年

? ? ? ? ? ? ? ? CALCULATE([Total Sales],

? ? ? ? ? ? ? ? ? ? FILTER(ALL('Calendar'),

? ? ? ? ? ? ? ? ? ? ? ? 'Calendar'[Date] > MAX('Calendar'[Date]) - 365

? ? ? ? ? ? ? ? ? ? && 'Calendar'[Date] <= MAX('Calendar'[Date])

? ? ? ? ? ? ? ? ? ? )

? ? ? ? ? ? ? ? )

? ? ? ? ? ? )

Total Sales MAT Improved 2 =IF (

? ? MAX ('Calendar'[Date]) >= DATE (2017, 7, 1),?//用年做間隔單位函數(shù)能自動判斷平年閏年的

? ? CALCULATE (

? ? ? ? [Total Sales],

? ? ? ? DATESINPERIOD ('Calendar'[Date], MAX ('Calendar'[Date]), -1, YEAR)

? ? )

)

7、 DAX函數(shù)查詢? Researching DAX Functions??

有許多時間智能函數(shù)可以用來編寫基于時間的DAX公式，學習如何使用全部的時間智能函數(shù)(以及所有其他DAX函數(shù))時，建議大家在線搜索并閱讀相關文檔的信息。我們可以在網(wǎng)上搜索函數(shù)名，后面跟上個DAX單詞。例如，我們在網(wǎng)頁搜索“DATEADD DAX”會返回搜索結(jié)果。

或者我們也可以在得微軟的官方網(wǎng)站查詢各函數(shù)的說明文檔。

8、其它時間智能函數(shù) Other Time Intelligence Functions

DATESINPERIOD(date_column, start_date, number_of_intervals, intervals)

DATESBETWEEN(column, start_date, end_date)

DATEADD(date_column, number_of_intervals, interval)

FIRSTDATE(date_column)

LASTDATE(date_column)

LASTNONBLANKDATE(date_column, [expression])

STARTOFMONTH(date_column)

STARTOFQUARTER(date_column)

STARTOFYEAR(date_column [,YE_date])

ENDOFMONTH(date_column)

ENDOFQUARTER(date_column)

ENDOFYEAR(date_column)

PARALLELPERIOD(date_column)

PREVIOUSDAY(date_column)

PREVIOUSMONTH(date_column)

PREVIOUSQUARTER(date_column)

PREVIOUSYEAR(date_column)

NEXTDAY(date_column)

NEXTMONTH(date_column)

NEXTQUARTER(date_column)

NEXTYEAR(date_column [,YE_date])

DATESMTD(date_column)

DATESQTD(date_column)

DATESYTD(date_column [,YE_date])

TOTALMTD(expression, dates, filter)

TOTALQTD(expression, dates, filter

9、A Free Quick Reference Guide

http://xbi.com.au/on-line-shop?