DAX 专题10:时间智能 Time Intelligence-读书笔记(18)

时间智能是DAX语言的一个强大且重要的功能。时间智能 可以在不修改日期筛选器的情况下编写引用不在可视化对象中的时间段公式。我们看下图，矩阵显示的是2018年的销售额。外部筛选器要把年份添加到筛选字段，选择2018年。

如果我们想要2017年的数据跟2018年的数据对比，就要把年份字段放到矩阵列上得到结果。但这样做可能不是最好的方法;如果不重复这个过程就无法在其他矩阵中得到相同的结果。另外，你不能用这种方法计算某一年与前一年销售相比的变化情况。

1、使用时间智能函数 Using Time Intelligence Functions

时间智能函数可以创建与某一时间点相关联的度量值 。例如：有一个"去年销售额"的度量值，就可以展示以某个时间点为参照的去年的销售额，这个时间点可以在可视化对象中选择。相对时间智能函数也非常有用，用它可以显示比较周期，而不必更改矩阵中的日期选择。使用时间智能函数能让一切都变得简单，还可以构建想要的视觉对象效果，否则就不行了。

DAX语言中有不少内置时间智能函数，当然你也可以在需要时书写自己的时间智能函数。时间智能的应用场景太多了，内置时间智能函数 不可能满足每一个需要的场景。所以DAX内置的时间智能函数使用起来还要符合一定的运行环境。

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.)

•要有一个标准的日期表，日期表包含一个连续的日期范围，涵盖你正在分析的数据期间的每一天。

•日期表必须有"每天"级别的粒度。

•每个日期必须在Calendar表中存在且仅存在一次(日期必须为不重复值)。

•日期不能有间隔。(例如，您不能因为事实表中的日期不包括周末而跳过周末日期)

•Calendar表必须从第一个数据点的年初开始，并扩展到最后一个数据点的年底。

•内置的时间智能只适用于标准日历(其中月份是一月，二月等)。

•您可以为不同的财年定制一个标准日历。(例如，您可以将日历的结束日期设置为6月30日或任何其他日期，而不是12月31日。)

如果因为某些原因日期表不符合上述条件，那么就别使用内置时间智能函数了。异或时间智能函数不能正常运行时，就要书写自己的时间智能度量值，通常还是使用CALCULATE和FILTER函数。书写自己的时间智能函数可能比较复杂，但也不必担心。

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.

某些场合你可能要在报表中使用非标准的日期表。标准的日期表在下列情况下不工作。

•当你使用每周或每月的数据构建数据模型时，你将使用每周或每月日期表而不是每天的日期表。

NOTE: 你可以加载每周或每月的数据，同时仍然使用标准日期表——只要满足上一节所说的其他条件，这时内置的时间智能函数也能正常工作。为了让模型正常工作，你的每周或每月数据将使用单个日期来表示时间段(例如，周末日期代表当周、月末日期代表当月)，然后将此日期连接到标准日期表表。但这是一个坑，不是最好的办法，虽然也能运行。我还是建议大家要学习如何正确地书写和使用自定义时间智能度量值，这个本章后面会给大家介绍。

•零售行业中的会计期间经常会使用ISO或445日期表，这个很常见，是因为企业总是需要周一到周日一整周的定期交易周期期。

在445日历中，其中有2个月由4个日历周组成，有1个月由5个日历周组成。这种设置每个月都是从周一开始，在周日结束，同时每个季度也有91天(91 × 4个季度= 364天)，更便于规律地数据周期计算。

•13个4周的周期，而不是日历月

•日期中既有日期又有时间的(例如，每小时日历)。

还有很多种情况我们不能一一都列举出来，对PBI来说内置的时间智能函数也不可能满足所有情形。总而言之，如果模型中有标准的日期表，就可以使用内置的时间智能函数，如果没有，就用CALCULATE() and FILTER()函数编写定制化的时间智能公式。

3、如何关闭自动日期  Here's How: Turning Off Auto Date/Time

Power BI有一个叫做自动日期/时间的功能，可以帮助初学者在报表中使用日、月、季度和年而不加载自己的日历表。个人不喜欢这个功能，因为它会自动为数据中的每个日期列创建一个隐藏的日期表。每个日期表都是独立的，它们不能协同工作。此外，尽管这些日期表隐藏在视图之外，但它们可能不只有一个或两个，会使你的数据模型变得非常大。我们正在学习编写DAX，那我们还是使用专用日期表完成报告吧，我也建议大家关闭自动日期/时间这个功能。操作起来也不复杂

1) 选择文件，选项和设置选项。

2) 导航到全局部分并选择数据加载。

3) 取消新文件的自动日期/时间。

4) 如果你的工作簿已经开启了自动日期/时间功能，那么你可以通过选择当前文件部分并选择数据加载来关闭它。

4、内置时间智能 Inbuilt Time Intelligence

1️⃣ 使用连续日期区间   Using a Contiguous Date Range 

示例数据中，Calendar表已经包含了Sales表的所有日期，要确定日期表的所有日期包含Sales表的所有日期也很容易的：创建一个新矩阵，将日期表的年份放在行上，将日期表月份名称放到矩阵的值上，在值字段单击下拉箭头并更改设置，让值显示为计数。

矩阵的值显示的是每年的天数，除2016年是半年以外，其它年份是全年的日期。这里我们使用了隐式度量值这个功能，虽然之前跟大家说过尽量不用隐式度量值，但用它来快速的核对一下数据也是很方便的。用隐式度量值并不是错误的方法，只是隐式度量值不能被引用，也不能修改名字和结果的数据类型，用起来不太方便而已。如果是用于核对数据，核对完成以后从矩阵中删除这个值字段即可，也不会带来什么麻烦。

NOTE:从技术角度来讲，日期表应该涵盖完整的年份数据(每年数据都是从年度的第一天连续到当年最后一天)，并且包括模型中的所有日期。但从实际经验来看，我们可以对日期表做点微小的调整，例如开始于模型中的最早日期，结束于模型中的最晚日期，尽管这样做可能会带来一点点小的风险。如果你能了解并避开存在的风险，那你可以这样做，否则的话还是使用完整的日期表吧。

2️⃣ 使用去年同期函数  Using SAMEPERIODLASTYEAR()

语法：SAMEPERIODLASTYEAR(<日期列>)        // 去年同期时间智能函数

返回：一个表，其中包含指定参数日期列中的日期在当前上下文中前一年的日期列。

备注：

• dates 参数可以是以下任一项 ：

对日期/时间列的引用，

返回单列日期/时间值的表表达式，

定义日期/时间值的单列表的布尔表达式。

• 有关对布尔表达式的约束，可参照 CALCULATE参数中对布尔表达式的要求。

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

• 在已计算的列或行级安全性 (RLS) 规则中使用时，不支持在 DirectQuery 模式下使用此函数

示例：计算上年同期销售额

先建立一个矩阵，把年份放在行上，把总销售度量值放在值上

右键单击SALES表，选择新建度量值，在公式栏输入

Total Sales LastYear =

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

输入公式时，智能提示显示：SAMEPERIODLASTYEAR返回的是当前上下文日期(矩阵的日期行字段)前一年的日期，CALCULATE再用这个日期筛选出SALES表，再计算[Total Sales]。

NOTE: 在智能提示中，SAMEPERIODLASTYEAR()将单个Dates参数作为其唯一参数输入，其实DAX所有的内置时间智能函数都需要这个Dates参数，它总是指向Calendar表中的Date列。

3️⃣ 去年同期"函数是如何工作的   How does SAMEPERIODLASTYEAR() Work?

在第15章中，我们介绍了CALCULATE()可以接受一个表作为高级筛选参数(CALCULATE的第二参数)，你可以想象成这个新表连接到数据模型，然后在CALCULATE()计算之前，CALCULATE()内的这个表筛选数据模型中的其它表(在本例中是Calendar表和Sales表)。SAMEPERIODLASTYEAR()的工作过程和CALCULATE完全相同，如下所示:

Total Sales LastYear =

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

在这个示例中，SAMEPERIODLASTYEAR()返回一个日期表，这个日期表与当前矩阵中的行字段日期相比都是向前提前了一年。将度量值放到矩阵中，可以看到：标绿的年份是2018年，这是矩阵的初始筛选，筛选出日期表中2018年的所有日期；CALCULATE度量值放到矩阵后，SAMEPERIODLASTYEAR('Calendar'[Date]) 函数将矩阵筛选出的2018年全年日期全部变成提前一年的日期，这个日期表(提前了一年的日期)再筛选销售表中日期与它相等的行出来，最后度量值用第一参数计算筛选出表的销售额，即为2017年的销售额。标红的值与标绿的年数据就能显示在一行，（矩阵行字段当前年的数据与前一年数据就在一行显示了），更便于做数据对比。

为了便于理解，您可以想象由SAMEPERIODLASTYEAR()创建的新表是位于Calendar表之上的临时表(我们叫它"上年日期"表)，并保持与原始Calendar表的关系，如下所示。(请记住，这是逻辑上的工作方式;实际上你看不到这个表格。)

"上年日期"表传递给CALCULATE()，然后CALCULATE()通过模型中的关系将"上年日期"表做为筛选表传递给日期表(Calendar表)，然后Calendar表在计算[Total Sales LY]之前筛选Sales表，最后再计算销售额。

4️⃣ 计算年初至今销售额   Calculating Year-to-Date Sales

实际工作中有个常见的业务需求，就是计算年初至今(YTD)的数值。DAX有一个内置的计算年初至今函数YTD。在写YTD公式之前，应该创建一个新矩阵，如果公式代码符合需求，矩阵会立即反馈给你一个正确的结果。

下面我们创建一个2018年的月度为行字段的矩阵，大家要注意，在外部筛选上我们选择了CalendarYear = 2018。

外部筛选中我们筛选年份=2018年，矩阵里面月份数据为2018年每月的销售额。如果我们不筛选年份=2018，那矩阵中每个月的数据就是所有年份的当月销售额。

要计算累计值，我们可以这样写度量值

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

通过做差计算[Total Sales YTD]两个月的对应值，跟[Total Sales]当前行的值比较，就可以看出累计值是如何显示的。

年初至今语法:

= TOTALYTD(表达式，CALENDAR[Date], 筛选器,指定年度的结束日期)

第一参数：表达式，一般为度量值

第二参数：日期表的日期列

第三参数：可选参数筛选器，可以指定哪一年，或者哪个产品等

第四参数：可选参数，指定年度结束的于哪一天(也就是指定所谓年初至今的年结束于哪一天)

用法1：只筛选部分连续月时的所有年份年初至今销售额

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

用法2：固定年份年初至今销售额--只显示2018年的数据

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

用法3：财年开始日期不等于标准日期开始日期

❇️ 改变财年结束日期 Changing Financial Year-Ending Dates

有些业务场景不是按通常的日期做为年度结束日期的，这时如果需要正确完成计算，许多内置的时间智能函数功能允许我们指定特定日期为年终日期。YTD系列函数中第三参数就是用来指定年终日期的，这个参数是可选参数(不写此参数时年终日期为当年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

这个对所有表的数值类型的列都可以进行设置

方法1：单列设置，选中日期表的不想要聚合的列，在列工具菜单->∑摘要,选择不汇总，这样只能一次设置一列

方法2：一次设置多列不默认聚合

转到模型视图，在字段区选中并展开想要修改的表的列，按住Ctrl再依次选择需要设置的列名->属性区域下拉滚动条，点开"高级"，点击汇总依据选择框的下拉箭头，选择“无”选项。

❇️ 其它时间智能函数 Practicing with Other Time Intelligence Functions

Previous  上一个系列

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

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

参数 释义

date ：包含日期的一个列。

year_end_date （可选）:带有日期的文本字符串，用于定义年末日期。 默认值为 12 月 31 日。

备注：

• 此函数基于输入参数中给定的最新日期返回上一年的所有日期。 例如，如果“日期”参数中的最新日期指的是 2009 年，则此函数返回 2008 年的所有日期，直到指定的“year_end_date”为止 。

• dates 参数可以是以下任一项 ：

对日期/时间列的引用。

返回单列日期/时间值的表表达式。

定义日期/时间值的单列表的布尔表达式。

• 有关对布尔表达式的约束，参照 CALCULATE函数中的相关描述。

• year_end_date 参数是日期的字符串文本，采用的区域设置与创建工作簿的客户端的区域设置相同 。 日期的年份部分会被忽略。

• 在已计算的列或行级安全性 (RLS) 规则中使用时，不支持在 DirectQuery 模式下使用此函数。

示例代码：

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️⃣ 使用内置的DATEADD函数  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内置的时间智能函数DATEADD()把本章中已经写过的一些公式写出等效公式，下面的例子展示了如何使用DATEADD()

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

初学DAX的用户可能对DATEADD()的语法觉得不好理解，公式中MAX()的使用也难以掌握，以后会给大家解释。到目前为止，我们在本章中介绍的许多函数实际上都是DATEADD()的语法糖。微软开发人员确实在努力提供编写DAX的简单方法，即使那些对DAX的工作原理知之甚少的人也是如此。如果不学CALCULATE()和DATEADD()，我们可以通过本章开头的简单函数利用DAX时间智能的强大功能，但是CALCULATE()其实是始终潜伏在时间智能函数的底层的，CALCULATE()和它的兄弟calculattable()是唯一可以改变筛选行为的DAX函数。如果对筛选进行了更改，并且在函数中看不到CALCULATE()，那么您正在使用的公式写法叫语法糖公式。SAMEPERIODLASTYEAR()， PREVIOUSMONTH()， PREVIOUSDAY()和PREVIOUSQUARTER()都是DATEADD()函数的语法糖版本。

5、写个自己的时间智能函数 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.

正如本章前面提到的，编写自己的时间智能函数比使用内置函数要困难一些，特别是当你对某些概念还不太熟悉时。我们使用DAX内置的时间智能函数时，DAX会为我们计算出完成时间转换所需的日期。当编写自己的时间智能函数时，我们要自己决定如何选择完成日期推移所需的新日期集合。一旦你掌握了它的使用窍门，你会发现它非常简单(这也是一个标志，表明你在理解DAX方面已经取得了很大进步)。在完全理解如何使用自定义时间智能函数之前，语法中有一些东西需要搞搞清楚。下面两个理念要刻在脑海里：

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

在考虑筛选上下文时考虑“整个表”。 

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

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

下面给大家介绍这两个概念，并提供示例演示。

🔶理念1 ：在考虑筛选上下文时考虑“整个表”。

这个图是本章开始时的一个矩阵，筛选区年份选择2018，矩阵中红框为1月数据，两个筛选器结合起来的筛选条件就是2018年1月，从总体上来看这行数据对整个日期表筛选后的日期就是2018年1月的日期，这也是报表页面的初始筛选，这个筛选对整个模型适用。

我们可以想像经过初始筛选的日期表 Month = January 和 Year = 2018的部分只会有31行数据，同时日期表的其它列也会也还在，这就是我们对年份和月份筛选后，也同时对表的其它列进行了筛选。

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

在编写自定义的时间智能函数时，在FILTER函数中常常会用到MIN和MAX函数。

NOTE:如果愿意，你也可以使用FIRSTDATE()和LASTDATE()函数，这两个函数本书不做过多介绍，只介绍一下MIN和MAX函数的用法。

重点：DAX公式中只要对列使用聚合函数，它总会服从自来可视化对象的初始筛选。我们再看一下刚才的矩阵

红框部分日期是从1月1号到1月31号，我们写两个公式

= MIN('Calendar'[Date])

= MAX('Calendar'[Date])

这两个公式放在度量值中，也会服从矩阵的当前筛选上下文，想想如果把这两个公式放到矩阵值上会得到什么？

可以看到，对于矩阵中的每个行字段， MIN('Calendar'[Date]) 都是当前月的第一天，MAX('Calendar'[Date])都是当前月的最后一天，外部筛选年份我们先的是2018年，所以矩阵值的年份也都是2018年。这也证明了度量值中对列的聚合服从可视化对象的初始筛选，可视化对象先筛选，聚合函数再运算。

所以我们可以把MIN和MAX 想像成从当前筛选上下文中取值的工具，它可以使用在任何可用的表列上，并且取到的值可以使用在DAX公式中。

NOTE: DAX 和EXCEL中的MIN和MAX还是有区别的，DAX允许这两个函数使用在非数值列上，如果我们写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、编写自定义时间智能函数 Writing Custom Time Intelligence Functions

自定义每年年初至今写法：(对于一年或多年数据)

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

&& 'Calendar'[Date] <= MAX('Calendar'[Date]))    )  //结果为每年的年初至今的累计

公式中FILTER函数返回一个表给函数CALCULATE。然后，CALCULATE对这个日期表使用一个筛选器，并在计算[Total Sales]之前将这个筛选传递到Sales表。让我们看看FILTER()函数中的第5行和第6行代码，第5行: 'Calendar'[CalendarYear] = MAX('Calendar'[CalendarYear])，“日历年怎么能等于日历年的MAX值 ?”实际情况是，等号的左边是一个列名，右边是一个MAX函数值。刚才我们讲过，MIN()或MAX()公式始终服从当前筛选上下文。因此这个公式第5行的运行过程如下:“向表中添加一个筛选器，使列'Calendar'[CalendarYear]等于来自矩阵的当前筛选上下文中的最大值。”例如在下面的矩阵中，突出显示的行的最大日期是2018年3月31日，因此MAX('Calendar'[CalendarYear]) = 2018。

现在看看如何使用“整张表”的理念。初始过滤器上下文是2018年3月，但MAX公式在CalendarYear列上工作。我们想象这个筛选器上下文作用于整个数据模型中的表，Calendar表中还有31行，对于每一行，'Calendar'[CalendarYear]中的值都是2018。因此对'Calendar'[CalendarYear]使用MIN也将返回2018 。换言之，使用SUM和AVERAGE也将返回2018年。第5行实际上是在说，筛选这个表，其中'Calendar'[CalendarYear]等于当前筛选上下文的年，这个例子里是2018。如果就此停止，公式将返回当前年度(2018年)的总销售额，但这不是我们所需要。对于矩阵中突出显示的行，我们只想要截止到3月底的销售额。

     我们继续往下看。第6行代码以双&操作符开始(DAX中是并且的意思，表示第5行和第6行结果要同时满足)，然后:'Calendar'[Date] <= MAX('Calendar'Date])这里与第5行相同。MAX('Calendar'[Date])从矩阵中读取初始筛选上下文，公式的这一部分添加了一个AND条件，以便对底层表进行筛选，以确定'Calendar'[CalendarYear] = 2018，以及'Calendar'[Date]是否在2018年3月31日或之前。

     我们继续往下看。当转到矩阵可视化中的下一行时，日历年保持不变，但月底日期移动到了下个月的月底。因此，当你在矩阵中按行往下移动时，日期表所包含的天数就是矩阵当前行单位的月的天数。

     我们来看看第4行代码 FILTER中 的ALL('Calendar')参数，前面我们提到过: 公式中对列使用聚合函数时，公式也服从可视化对象的初始筛选，第5和6行代码也要被矩阵行(月份)筛选，这样的话就不好了，日期表不是连续延长了，每月又变成了一段。在第14章我们讲过ALL函数，它可以清除外部筛选，我们把它放在FILTER的第一参数。我们希望将1月和2月的销售额包含在3月实际销售额里，就必须首先删除由矩阵创建的筛选器。这就是ALL()在第4行中作用在Calendar表时所做的 : 它清除了应用于Calendar表的矩阵筛选器上下文，先删除筛选器(使用ALL())，然后重新应用第5行和第6行中使用的筛选器，最终便得到所有日期的YTD。

拓展：看看下面这样写会得到什么结果？

Total Sales YTD Manual2 = CALCULATE([Total Sales],FILTER(ALL('Calendar'),        'Calendar'[Date] <= MAX('Calendar'[Date])))     //结果变成了从事实表第一天开始至今的累计

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。函数不起作用的原因与前面讨论的初始筛选器上下文有关。对于2018年3月行，初始筛选器上下文应用了一个筛选器，以便在Calendar表中只有2018年3月的31天是像是“可见的”(其实是要想象出来这个么可见的日期)。那么，如果现有的筛选只保留了3月，那么该公式怎么也不可能返回“年初至今”所有日期的销售了，也不会包括1月和2月的销售，因为1月和2月的日期已经被来自矩阵的初始筛选上下文给扔掉了，所以如果按照这种方法写公式，就无法让这几个月的销售额重新出现在新公式中。

如果希望将1月和2月的销售额包含在前3月实际销售额旁边的一行中，则必须首先删除由矩阵创建的筛选，这就是ALL()在第5行代码中对Calendar表所做的:它删除了来自于Calendar表的矩阵的筛选。首先删除矩阵的行筛选(使用ALL)，然后重新应用您想在第6行和第7行中使用的筛选，以便最终得到所有日期YTD。

❇️ 使用ID列编写时间智能函数 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列，以唯一地标识希望在报表中使用的时间段，特别是在编写自定义时间智能函数时。一个很常用的例子是月份的ID列。我们说的月份的ID列不是月份的数字像1月2月…，而是从日期表的第一个月的1开始的序号一直延续的ID号，在日期表中每增加一个都增加1，每个ID都与日期表的一个月份一一对应。在编写自定义时间智能函数时，这样的ID列非常有用，特别是使用非标准日历(如ISO日历或445日历)时。这种ID列的好处是：它能为我们提供一个整洁的数字列，以便在使用DAX公式中随着时间前后移动。为了说明这一点，在本节中，我将展示如何编写一个计算滚动6个月销售额的公式。像之前一样，我们先建立一个矩阵来帮助说明。

在下图中，可以看到矩阵在行上有CalendarYear和MonthName，在值上有[Total Sales]度量值和Max of Month ID隐式度量值(见下面#1)。为了创建月ID的隐式度量Max，我简单地将月ID列拖到Values部分，并将聚合更改为Maximum(参见#2)。下图显示了矩阵中每个月的月份ID是如何递增的——当然，不包括年份的总行数(#3)。

从上图可知，2017年12月的月份ID是18，要写出一个显示2017年12月之前6个月销售额的公式，我们需要从2017年7月到2017年12月的所有数据。换一种说法就是，需要从ID月13到ID月18的所有数据。看到了吗?当你有个一个好的视觉对象帮助时，头脑更容易理解要解决的问题。

还记得在DAX中使用MAX()函数从表中“获取”值的技巧吗?上图使用了一个隐式度量，相当于在DAX中写入MAX([Month ID])，并返回每个月的月份ID。

请记住:

•MAX([Month ID])得到总数所需的最后一个月ID(在本例中是2017年12月，或ID月18)

•MAX([Month ID]) - 5得到总数所需的开始月份ID(在本例中是2017年7月，或ID月13)

有了上一节的经验，我们写个度量值

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])  

❇️使用日期平移函数 Using DATESINPERIOD

语法：

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

包含单列日期值的表，可以从某一日期按指定的时间间隔往前推/推迟 多少天/月/季/年，

四个参数均为必选参数，第二参数为一个日期表达式，一般会使用MAX(calendar[Date])或Min(calendar[Date])做为日期推迟或推后的起点。

参数 定义

<dates> ：日期列。

<start_date> ：日期表达式。

<number_of_intervals> ：一个整数，指定要添加到 dates 或从 dates 中减去的时间间隔数。为正值或负值时根据第二参数聚合函数各类不所不同

<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])

                )

            )           //不用时间智能函数写法

 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), //用年做间隔单位函数能自动判断平年闰年的

    CALCULATE (

        [Total Sales],

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

    )

)

7、 DAX函数查询  Researching DAX Functions  

有许多时间智能函数可以用来编写基于时间的DAX公式，学习如何使用全部的时间智能函数(以及所有其他DAX函数)时，建议大家在线搜索并阅读相关文档的信息。我们可以在网上搜索函数名，后面跟上个DAX单词。例如，我们在网页搜索“DATEADD DAX”会返回搜索结果。

或者我们也可以在得微软的官方网站查询各函数的说明文档。

8、其它时间智能函数 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