>Sample chapters>微软Office>Excel

Microsoft PowerPivot for Excel 2010:DAX中的日期计算

在本章中 Microsoft PowerPivot for Excel 2010:为数据赋予意义,了解如何为PowerPivot模型创建日期表,以及如何使用该表支持几种类型的计算:工作日数,随着时间的推移进行汇总和比较,and closing balance over time.

许多数据分析必须处理日期。Microsoft SQL Server PowerPivot for Excel offers a number of functions that simplify many calculations on dates that are typical in a business scenario,but using the right function in the right way requires some explanation.正如你在本章所看到的,日期计算的第一步是创建一个支持大多数需求的单独日期表。

使用日期表

在前几章的一些例子中,we defined calculated columns that extracted parts of the date that we used to group dates,such as the year and the month.This technique might be applied to each table containing a date,但这很快就会变得难以管理。最好为每个日期创建一个包含行的单独表,使用日期作为键将该日期表与包含与日期相关数据的其他表链接。这样,您得到一个模型,其中关于日期的所有属性都包含在一个单独的表中,当您使用数据透视表浏览数据时,很容易访问这些属性。你可以在里面看到图7-1.

图7-1

图7-1使用名为orderdate的日期表浏览数据透视表的顺序数据。

日期表还可用于使用对日期进行操作的特殊DAX函数进行计算。这些功能,of which DATEADD is an example,通常要求给定范围内的所有日期都存在于数据表中,否则,错过一天可能会导致计算错误。You might have no sales for a day (in fact,在非工作日没有销售是很常见的)。因此,单独的日期表允许您进行正确的计算,而不需要修改包含要分析的度量值的原始表。

此技术的唯一副作用是,您需要在PowerPivot中为要在单个表中分析的每个日期属性创建一个日期表,因为PowerPivot中的两个表之间只能有一个关系。

How to Build a Dates Table

要在PowerPivot中创建日期表,you need a data source that contains at least a column with all days included in the period of time you want to analyze.For example,如果销售数据中包含的最小和最大日期是7月3日,2001年和7月27日,2004,分别the range of dates you should consider is between January 1,2001年和12月31日,2004.这样,您在所有年份中都有包含销售数据的所有天数。

In Chapter 3,"DAX简介,"you saw how to create Day,月,以及日历表的年份计算列,该日历表的日期列仅作为现有数据。然而,如果没有外部源为您提供有效的日期表(如SQL Server中的相应表),则we suggest that you create all the calculated columns for a Dates table in Excel.这样,例如,当需要处理更多的日期时,可以更容易地将该表的全部内容复制并粘贴到新的内容中。PowerPivot模型中的订单日期和装运日期。

要创建日期表,你可以先在单元格中键入日期,然后在下面的单元格中键入1/1/2001,你可以在里面看到图7-2.

图7-2

图7-2在Excel中创建日期表。

然后在包含1/1/2001日期的单元格右下角(在中突出显示图7-2),你可以一直拖到2004年12月31日,你可以在里面看到图7 -3.

图7 -3

图7 -3为在Excel中创建日期表选择结束范围。

At this point,你可以释放鼠标。您刚刚创建了一个包含从2001年初到2004年底的所有天数的列表。现在您可以单击日期单元格,click the Format As Table button on the Home tab of the ribbon,and then confirm that your table has headers,你可以在里面看到图7-图4.

图7-图4

图7-图4确认表的范围并确认表具有标题。

图7~5,you can see how to give the Calendar name to the table by using the text box available on the Design tab of the Table Tools contextual tab of the Excel ribbon and how to start adding new columns by right-clicking a cell in the table and selecting the InsertTable Column To The Right item from the Insert context menu.

图7~6,您可以看到如何在新列的空单元格中定义公式来计算年份。在您键入之后=年,you can click the Date column to get the right syntax to read that column,如图所示图7~6.

图7~5

图7~5在日期表中插入新列。

图7~6

图7~6在Excel中定义年份列的公式。

At this point,您可以键入右括号,然后按Enter键。将自动为同一列中的表的所有行复制公式,结果你可以看到Figure 7-7(在您将“年”列的格式调整为“常规”之后,以防从“日期”列复制的格式不同)。

Figure 7-7

Figure 7-7为所有行计算的年份列。

用这种方法,you can define all the columns that are useful for navigating the data that aggregate date in several ways.

Figure 7-8显示从7月1日开始的会计年度的完整日期表的最终结果。您可以在随附DVD中的ch07-01-calendar.xlsx工作簿中找到此表。表7-1包含所有列的公式定义。

Figure 7-8

Figure 7-8完整的日期表,财政年度从7月1日开始。

表7-1 Excel中日期表的公式定义。

Column

公式

=YEAR([@Date])

MonthNumber

=MONTH([@Date])

月份

=TEXT([@Date],"毫米-毫米)

=天([日期])

WeekDay

=TEXT([@Date],"dddd")

Quarter

="Q& ROUNDUP(MONTH([@Date]) /3,0)

花萼

="FY-"&[@year]+如果([@month]<7,0,1)

财政季度

="FQ"&mod(天花板(22+[@月]-6-1,3)/3,4)+1

现在,您可以在PowerPivot中将此表作为链接表导入。结果显示在Figure 7-9.

Figure 7-9

Figure 7-9在PowerPivot中作为链接表导入的日期表。

You can see that the month name contains the month number in front of it,所以12月被描述为12-December.自动排序月份名称很有用。然而,if you want to sort month names but also want to avoid the initial number,please take a look at the section"数据透视表中的自定义排序“在第8章中,"掌握数据透视表,"在这里,我们描述如何在数据透视表中对日期表的列进行排序。

您可能需要更改日期表中某些列的数据类型。每当将Excel表导入PowerPivot时,像Year这样的专栏,MonthNumber,and Day are usually defined as Whole Number data types.因此,when you select one of these columns in the PivotTable,默认情况下,所选属性放置在数据透视表的“值”区域中,并在使用sum函数时进行聚合。您可能更喜欢将这些列的数据类型更改为文本,以便在默认情况下将数据分组到行中。

如果要测试新的日历表,you should now import the SalesOrderHeader,SalesOrderDetail,Customer,以及从AdventureWorks数据库到同一PowerPivot模型的产品表。在导入期间自动检测这些表之间的关系。At this point,您需要在SalesOrderHeader表的orderDate字段和刚导入的日历表的日期字段之间创建一个关系。Before starting,在PowerPivot中,必须将日历表重命名为orderDate,以便它表示它所代表的日期。Then you click the Create Relationship button on the Design tab of the ribbon and fill in the dialog box,如图所示图7-10.

图7-10

图7-10Create a relationship between the SalesOrderHeader and OrderDate tables.

At this point,模型已准备好浏览数据,正如你在本章开头所看到的,在里面图7-1.

Working with Multiple Dates Tables

在上一节中看到的模型中,每个订单都有几个日期。如果您不仅要分析订单日期,还要分析发货日期,you need to define a second table in PowerPivot because the same table (that is,日期表)不能与给定表(SalesOrderHeader)有多个关系。

At this point,you have two options.您可以从以前使用的同一个表开始创建新的链接表(如Figure 7-8) or copy that table into Excel and create the linked table starting from this copy.第一个选项不是最好的选项,因为在PowerPivot中,对于给定的Excel表,只能有一个链接表。If you try to create a linked table starting from the same Calendar table you defined before,the warning message shown in图7-11appears.

图7-11

图7-11A warning against trying to create a linked table for an Excel table already used as a linked table.

If you continue creating a linked table this way,不能再更新orderdate表。如果创建的模型必须随着时间的推移而刷新,并且其生命周期可能比当前年份长,你最好换个方法,允许将来更新。

第二个选项要求您在Excel中复制和粘贴现有的日历表。Before you do that,you should rename the Calendar table in Excel,使用与PowerPivot中相应链接表相同的名称,这是订单日期。To do that,可以在Excel功能区的“表格工具”上下文选项卡的“设计”选项卡上的“表格名称”文本框中键入订单日期名称。你可以在里面看到Figure 7-12.

Figure 7-12

Figure 7-12重命名表orderdate,在Excel中。

At this point,如果尝试更新PowerPivot中的orderDate链接表,您将收到错误消息,显示在图7至13.

图7至13

图7至13The error message you get when you try to update OrderDate after changing the name of the underlying Excel table.

When you click the Options button,您可以选择“更改Excel表名”选项。Then you choose the OrderDate table in the combo box that shows the available tables in Excel,你可以在里面看到图7至14.

图7至14

图7至14通过选择正确的基础Excel表来修复LinkedTable中的错误。

Now you can copy the OrderDate table in Excel into a new one that we call ShipDate.你可以选择整张桌子,复制它,然后将其粘贴到Excel工作簿的空白空间中。然而,另一种选择是为类似的每个表使用一个专用的Excel工作表,这样您就可以简单地将工作表复制到一个新的工作表中。这样,每当需要向表中添加列或行时,您不必移动其他现有表。此外,在Excel中单击相应的工作表名称时,可以轻松访问表。

要创建包含orderdate表的工作表副本,您必须右键单击“订单日期”标签,然后从上下文菜单中选择“移动”或“复制”,您可以在图7~15.

图7~15

图7~15从上下文菜单中选择“移动”或“复制”。

所选内容显示对话框,如Figure 7-16,其中,您必须选中“创建副本”复选框并选择新工作表的位置。

Figure 7-16

Figure 7-16选择选项以创建要放置在列表末尾的工作表副本。

At this point,重命名这两个表(使用您在中看到的相同过程Figure 7-12)以及工作表(右击orderdate(2)标签,然后从上下文菜单中选择RenameFigure 7-17);you use the new ShipDate name.

Figure 7-17

Figure 7-17上下文菜单中的重命名选项。

最后,通过单击PowerPivot功能区上的“创建链接表”按钮,可以为ShipDate表创建链接表。再一次,您必须在PowerPivot中在SalesOrderHeader和ShipDate表之间创建关系,通过这次使用SalesOrderHeader表的ShipDate列,你可以在里面看到图7~18.

图7~18

图7~18Creating a relationship between the SalesOrderHeader and OrderDate tables.

You can find the resulting model in the CH07-02-OrderAndShippingDate.xlsx workbook included on the companion DVD.然而,正如您在下一节中看到的,duplicating tables might not be enough.因为Excel在用于切片器和筛选器时不显示列所属的表名,您可能需要向列添加前缀。It is better to do that directly in the source Excel table rather than renaming the columns in PowerPivot only so that the overall model is simpler to understand.

区分多个日期表中的列

复制同一张桌子,such as a Dates table,在PowerPivot模型中多次使用不同表中的相同属性都会使生成的数据透视表难以读取。For example,在里面图7~19,您可以看到一个数据透视表,其中orderdate的年份已放入行和第一个切片器中,从发货日期算起的年份已经被列进第二个切片机。问题是,无论何时将列移入切片器,都没有证据表明该列所属的表,过滤器,排,or columns of the PivotTable.本节示例的最终型号可在随附DVD附带的ch07-03-prefixedDataColumns.xlsx工作簿中找到。

图7~19

图7~19不同表中同名的列在数据透视表中不可识别。

因此,如果您创建了一个具有相同表的多个副本的模型,您应该区分列的名称,以便在报告中立即识别它们。You can edit the table names in Excel by adding a prefix to each column.在图7- 20,您可以看到orderdate表的标题,wherein each column has been prefixed with the word秩序.You can do the same for the ShipDate column by using theShip前缀。

图7- 20

图7- 20orderdate列的前缀为秩序.

重命名列后,您可以创建一个类似于您在中看到的报表图7~19,but this time,对以前不明确的列使用更有意义的名称。您可以在中看到这样一个过程的结果图7-23.

图7-23

图7-23列前缀在切片器和PowerPivot字段列表中更容易识别。

我们建议每次在不同的表中使用相同的列名时都使用列前缀,而不仅仅是日期表。

Calculating Working Days

既然您已经学习了如何创建日历表,值得指出的是,一些列在数据分析中非常有用,并且可以方便地存储在日历表中。For example,您可能有兴趣定义一个计算给定期间内每个工作日平均销售额的度量。(You can find the complete example in the CH07-04-WorkingDays.xlsx workbook included on the companion DVD.) To do that,you have to calculate the number of working days,这就需要知道一天是否是工作日。The simpler way to do this is to add a WorkingDays column to the Excel OrderDate table.该列的工作日值应为1,节假日为0,weekends,以及其他非工作日。不是手工编译这个专栏,you might define it by using the following Excel formula that assigns 1 to all week days between Monday and Friday,leaving 0 to Saturday and Sunday:

=if(工作日([@日期],2)>5,0,1)

This formula is automatically copied into all the rows of the OrderDate table,你可以在里面看到Figure 7-24.

Figure 7-24

Figure 7-24The WorkingDays column added to the OrderDate table in Excel.

您可以修改其他非工作日的单个值,比如公共假日,仅在这些天内用强制固定值(通常为0)覆盖公式。For example,在里面Figure 7-25,您可以看到1月1日的值,2001被0值覆盖,然而,以下日期仍由我们之前定义的公式计算。

Figure 7-25

Figure 7-25The value 0 overriding the formula for January 1,2001.

您可以更新PowerPivot中的orderDate链接表,“工作日”列也显示在数据透视表中。At this point,you can define a measure belonging to the SalesOrderDetail table named DailySales,将LineTotal的总和除以工作日的总和,你可以在里面看到Figure 7-26.

Figure 7-26

Figure 7-26The definition of DailySales measure.

The final result is shown in图7-27,where both WorkingDays and DailySales measures are exposed in the PivotTable.然而,在真实的报告中,你通常不显示工作日数,只显示平均值,such as Daily Sales.

图7-27

图7-27PivotTable showing results for WorkingDays and DailySales.

到目前为止,我们的技术非常容易出错,因为我们直接在一个单元格中写入一个0值来表示假日,without any further explanation.如果我们出错,很难辨认;furthermore,我们不区分周末(自动计算)和假期。更好的解决方案是定义一个单独的假日表,which is easier to check and to maintain because it moves into a single calculated column the logic to merge weekend evaluation and holiday definition using a single formula.在图7—28,you can see such a Holidays table,defined in Excel.

图7—28

图7—28Excel中的假日表。

You can import this Holidays table as a linked table in PowerPivot and define a relationship between the OrderDate and Holidays tables,如图所示图7-29.

图7-29

图7-29orderDate和holidays表之间的关系。

您可以删除WorkingDays列,因为您正在将整个逻辑移动到PowerPivot中。因为您需要使用relate函数来获取PowerPivot中的假日信息,您应该将所有业务逻辑移动到一个简单的地方:避免在Excel和PowerPivot之间将其拆分一半。After you update the OrderDate table in PowerPivot by removing the WorkingDays data column,可以使用在中可以看到的DAX公式定义新的工作日计算列。Figure 7-30.

Figure 7-30

Figure 7-30OrderDate表中的WorkingDays计算列。

Let us examine the DAX formula inFigure 7-30.First of all,可以看到由以下公式定义的新假日计算列:

Holiday =IF( RELATED(HolidaysTable[Date]) = BLANK(),错误的,真的)

“假日”列中的每一天都有一个与“假日”表中的假日相对应的真值。Using this information,we extend the previously defined Excel formula that considers whether a nonworking day is a Saturday,a Sunday,或使用以下DAX公式进行假日:

WorkingDays=if(工作日(订单日期[日期],2)>5订购日期[节假日],0,1)

最后,您可以通过正确计算工作日来浏览数据,根据假日表,我们包括在模型中。在Figure 7-31,you can see the resulting PivotTable,您可以在随附DVD附带的ch07-05-workingdays-holidaystable.xlsx工作簿中找到完整的模型。

Figure 7-31

Figure 7-31使用假日表支持显示最终结果的数据透视表。

另一个涉及工作日的常见计算是两个日期之间的增量。For example,in the SalesOrderHeader table of the model used in this chapter,有三次约会,你也可以在里面看到图7-32

  • 订单日期:订单日期

  • DueDate: When the customer expects the order to be delivered

  • 发货日期:订单发货日期

图7-32

图7-32日期SalesOrderHeader表中的列。

计算订单是否按时发货似乎很容易:您应该比较DueDate和ShipDate列。然而,如果您认为标准交货时间为4个工作日,您应该计算在Duedate之后已发货的订单数减去四个工作日。This calculation requires the support of the Dates table.以下示例的完整模型可在随附DVD中的ch07-06-deliverydays.xlsx工作簿中找到。

为了进行计算,we need to add a calculated column in the SalesOrderHeader table that calculates for each order the difference (in working dates) between the two dates.可以在具有以下公式的日期表中创建WorkingDayNumber计算列:

WorkingDayNumber=sumx(筛选(orderDate,orderdate[日期]<=更早(orderdate[日期]),订单日期[工作日])

This number calculates for each day the number of working days elapsed since the first date in the Dates table.在Figure 7-33,您可以看到如何为几行计算这个数字。

Figure 7-33

Figure 7-33WorkingDayNumber计算。

At this point,您可以使用对应日期的WorkingDayNumber差异定义两个日期之间的工作日数。因为您可能不想向PowerPivot模型添加太多表,you may reuse the same OrderDate table already imported into the model to get the WorkingDayNumber for both DueDate and ShipDate dates of an order.For example,ShipDate的这个数字可以通过使用以下DAX表达式获得:

计算(值(orderdate[workingdaynumber]),筛选(订单日期,orderdate[日期]=salesorderheader[发货日期])

筛选器调用仅筛选OrderDate表中的ShipDate行。使用这个过滤器,the CALCULATE function returns the value of WorkingDayNumber for that row.如果过滤器返回多行(在这种情况下,the filter condition contains an error).

所以对ShipDate和DueDate使用这个DAX表达式,我们可以使用以下公式在SalesOrderHeader中定义DuedletDays计算列:

duedletdays=计算(值(orderdate[workingdaynumber]),筛选(订单日期,orderDate[日期]=销售订单标题[ShipDate]))+ 4-计算(值(orderdate[workingdaynumber]),筛选(订单日期,orderDate[日期]=销售订单标题[DueDate]))

Duedeltadays列显示延迟情况下的正数,表示延迟天数。负数表示提前交货(通常以天为单位)。在图7-34,您可以看到此列和另一个名为deliveryLaydays的计算列的值,它只显示延迟订单的值。

图7-34

图7-34SalesOrderHeader中的DuedletDays和DeliveryPlayDays计算列。

有了这些信息,you can calculate some measures in the PivotTable,such as the ratio of delayed deliveries:

deliveryLayration=count(salesorderheader[deliveryLaydays])/countrows(salesorderheader)

You can also calculate the average delay (in days) for delayed orders,只需在DeliveryLaydays列中选择Summary by Average项。在图7 35,您可以看到一个数据透视表显示这两个度量值。

图7 35

图7 35数据透视表中deliveryPlayRatio和deliveryPlayDays列的平均值。