了解并创建 Excel 中的 Power Pivot 内的日期表

在表中创建日期Excel

可以在数据模型中创建一个Excel，然后将它复制到数据模型中的新表中。 这确实非常简单，它为你提供了极大的灵活性。

在表中创建日期表Excel，从具有连续日期范围的单个列开始。 然后，您可以使用 Excel 公式在 Excel 工作表中创建其他列（如"年、季度、月、会计年度、期间"等），或者，将表复制到数据模型后，可将其创建为计算列。 本文稍后的"将新日期列添加到日期表 "部分中介绍了在 Power Pivot 中创建其他日期列。

向日期表添加新的日期列

包含单个日期列的日期表对于定义日期范围内的所有日期非常重要，该日期列每年每一天都有一行。 此外，还需要在表和日期表事实数据表关系。 但是，按数据透视表或数据透视表中的日期分析时，每天包含一行的单个日期列Power View很有用。 您希望日期表包含有助于聚合一个或多个日期区域或日期组数据的列。 例如，你可能想要按月或季度对销售额求和，或者可以创建一个计算年度增长的度量值。 在每种情况下，日期表都需要年、月或季度列，以便聚合该期间的数据。

如果从关系数据源导入了日期表，则它可能已包含你需要的不同类型的日期列。 在某些情况下，你可能想要修改其中一些列或创建其他日期列。 如果在数据模型中创建自己的日期表并复制到数据模型中Excel尤其如此。 幸运的是，使用 DAX 中的日期和时间函数，在 Power Pivot 中创建新日期列非常简单。

DAX 日期和时间函数

如果您曾经在公式中处理过日期和时间Excel函数，那么您可能熟悉日期和时间函数。 尽管这些函数与函数中的Excel类似，但存在一些重要差异：

• DAX 日期和时间函数使用日期时间数据类型。

• 它们可以将列中的值作为参数。

• 它们可用于返回和/或操作日期值。

在日期表中创建自定义日期列时，通常使用这些函数，因此它们非常重要，必须理解。 我们将使用其中一些函数为 Year、Quarter、FiscalMonth 等创建列。

DAX 包括以下日期和时间函数：

• DATE

• DATEVALUE

• NEXTDAY

• EDATE

• EOMONTH

• HOUR

• MINUTE

• MONTH

• NOW

• SECOND

• TIME

• TIMEVALUE

• TODAY

• WEEKDAY

• WEEKNUM

• YEAR

• YEARFRAC

还可以在公式中使用许多其他 DAX 函数。 例如，此处描述的许多公式使用数学和三角 函数（如MOD 和TRUNC、逻辑函数（如IF）和 文本函数（如FORMAT））。有关其他 DAX 函数 的信息，请参阅本文稍后的其他资源部分。

日历年的公式示例

以下示例介绍用于在名为"日历"的日期表中创建其他列的公式。 名为 Date 的一列已存在，包含从 2010 年 1 月 1 日到 2016 年 12 月 31 日之间的连续日期范围。

月份名称

=FORMAT ([date]，"mmmm")

在此公式中，为了获取月份名称，我们使用 FORMAT 函数将数字值从"日期"列转换为文本。 我们将"日期"列指定为第一个参数，然后指定格式;我们希望月份名称显示所有字符，因此我们使用"mmmm"。 结果如下所示：

如果要返回缩写为三个字母的月份名称，我们将在 format 参数中使用"mmm"。

一周中的某一天

=FORMAT ([date]，"ddd")

在此公式中，我们使用 FORMAT 函数获取日期名称。 由于我们只需要缩写的日名称，因此我们在 format 参数中指定"ddd"。

示例数据透视表

一旦有日期的字段（如"年、季度、月"等），就可以在数据透视表或报告中使用它们。 例如，下图在 VALUES 中显示"销售额"事实数据表"销售额"字段，在"行"中显示"日历"维度表"年"和"季度"。 SalesAmount 针对年份和季度上下文进行聚合。

会计年度的公式示例

假日或特殊日期

你可能希望包含一个日期列，指示某些日期是假日或其他一些特殊日期。 例如，你可能希望通过将"假日"字段作为切片器或筛选器添加到数据透视表来对"新年"日的销售总额求和。 其他情况下，你可能希望从其他日期列或度量值中排除这些日期。

包括假日或特殊日期非常简单。 可以在包含要Excel的日期的表中创建一个表。 然后，可以复制或使用"添加到数据模型"，以链接表方式将其添加到数据模型。 在大多数情况下，不需要在表和日历表之间创建关系。 引用它的任何公式都可以使用 LOOKUPVALUE 函数返回值。

下面是在数据表中创建的表Excel包括要添加到日期表的假日：

在日期表中，我们将创建一个名为 Holiday 的列，并使用如下所示的公式：

=LOOKUPVALUE (Holidays[Holiday]，Holidays[date]，Calendar[date])

让我们更仔细地看一下此公式。

我们使用 LOOKUPVALUE 函数从 Holidays 表中的 Holiday 列获取值。 第一个参数指定结果值的列。 我们在" 假日" 表中指定 "假日 "列，因为这是我们希望返回的值。

=LOOKUPVALUE (Holidays[Holiday]，Holidays[date]，Calendar[date])

然后，我们指定第二个参数，即包含要搜索的日期的搜索列。 我们在 Holidays 表中 指定"日期 " 列，如下所示：

=LOOKUPVALUE (Holidays[Holiday]，Holidays[date]，Calendar[date])

最后，在"日历"表中指定包含要搜索的日期的"假日"表中的列。 这当然就是" 日历" 表中的" 日期" 列。

=LOOKUPVALUE (Holidays[Holiday]，Holidays[date]，Calendar[date])

"假日"列将返回每一行的假日名称，该行的日期值与"假日"表中的日期匹配。

自定义日历 - 十三个四周时间段

某些组织（如零售或食品服务）通常报告不同的时间段，例如十三个四周时间段。 使用 13 个四周周期的日历，每个周期为 28 天;因此，每个时间段包含四个星期一、四个星期二、四个星期三，等等。 每个时间段包含的天数相同，通常，假日将位于每年同一时间段内。 可以选择在一周的任何一天开始一个时间段。 与日历或会计年度中的日期一样，您可以使用 DAX 创建包含自定义日期的其他列。

在以下示例中，第一个完整的周期从会计年度的第一个星期日开始。 在这种情况下，会计年度从 7 月 1 日开始。

句点

此自定义日历中的时间段包含 28 天，并且始终从星期日开始。 此列将返回第一个会计年度中从第一个星期日开始的期间数。

=INT ( ([Week]+3) /4)

让我们更仔细地看一下此公式。

首先，我们创建一个公式，将 Week 列中的值作为整数返回，如下所示：

=INT ([Week])

然后向该值添加 3，如下所示：

=INT ([Week]+3)

然后将结果除以 4，如下所示：

=INT ( ([Week]+3) /4)

结果如下所示：

期间会计年度

此值返回一个周期的会计年度。

=INT ( ([Period]+12) /13) +2008

让我们更仔细地看一下此公式。

首先，我们创建一个公式，该公式返回 Period 中的值并添加 12：

= ([Period]+12)

我们将结果除以 13，因为会计年度有 13 个 28 天的周期：

= ( ([Period]+12) /13)

我们添加了 2010，因为这是表中的第一年：

= ( ([Period]+12) /13) +2010

最后，我们使用 INT 函数删除结果的任何分数，当除以 13 时返回一个整数，如下所示：

=INT ( ([Period]+12) /13) +2010

结果如下所示：

FiscalYear 中的期间

此值返回期间数字 1 – 13，从每个会计年度的 (日) 整期开始。

=IF (MOD ([Period]，13) ， MOD ([Period]，13) ，13)

此公式稍微复杂一些，因此我们将首先用我们更理解的语言来描述它。 此公式将值从 [Period] 除以 13， (年 1-13) 数字。 如果该数字为 0，则返回 13。

首先，我们创建一个公式，用于返回 Period by 13 中值的剩余部分。 可以使用 MOD 函数 (数学 和三角函数) 如下所示：

=MOD ([Period]，13)

在大多数情况下，这为我们提供了我们需要的结果，除非 Period 的值为 0，因为这些日期不属于第一个会计年度，如示例日历日期表的前五天。 可以使用 IF 函数来处理此问题。 如果结果为 0，则返回 13，如下所示：

=IF (MOD ([Period]，13) ，MOD ([Period]，13) ，13)

结果如下所示：

多个关系

在某些情况下，可能需要创建多个关系或创建多个日期表。 例如，如果 Sales 事实数据表 中有多个日期字段，例如 DateKey、ShipDate 和 ReturnDate，则它们都可以与"日历"日期表中的"日期"字段具有关系，但只有一个关系可以是活动关系。 在这种情况下，由于 DateKey 表示事务日期，因此也是最重要的日期，因此这最好充当 活动 关系。 其他具有非活动关系。

以下数据透视表按会计年度和会计季度计算总销售额。 名为"总销售额"的度量值，公式为"总销售额 ：=SUM ([SalesAmount]) "， 位于 VALUES 中，"日历日期"表中的 FiscalYear 和 FiscalQuarter 字段位于 ROWS 中。

此直接数据透视表正常工作，因为我们想要在 DateKey 中按交易日期对总销售额求和。 "总销售额"度量值使用 DateKey 中的日期，并按会计年度和会计季度进行求和，因为"销售"表中的 DateKey 与"日历日期"表中的"日期"列存在关系。

非活动关系

但是，如果我们想要对总销售额进行求和，而不是按交易日期，而是按 发货日期进行求和呢？ 我们需要"销售"表中的 ShipDate 列与"日历"表中的"日期"列之间的关系。 如果不创建该关系，聚合始终基于事务日期。 但是，我们可以有多个关系，即使只有一个关系处于活动状态，并且由于事务日期是最重要的，因此它获取与日历表的活动关系。

在这种情况下，ShipDate 具有非活动关系，因此为基于发货日期聚合数据而创建的任何度量公式都必须使用 USERELATIONSHIP 函数指定非活动关系。

例如，由于"销售"表中的"发货日期"列与"日历"表中的"日期"列之间存在非活动关系，因此，我们可以创建一个度量值，用于按发货日期对总销售额进行总和。 我们使用如下所示的公式来指定要使用的关系：

按发货日期：=CALCULATE (SUM (Sales[SalesAmount]) ，USERELATIONSHIP (Sales[ShipDate]，Calendar[Date]) )

此公式仅表示：计算 SalesAmount 的总和，但使用"销售"表中的 ShipDate 列与"日历"表中的"日期"列之间的关系进行筛选。

现在，如果我们创建数据透视表，将"按发货日期总销售额"度量值放在"值"中，将"会计年度"和"会计季度"放在行上，则可以看到相同的"总计"，但会计年度和会计季度的所有其他总和金额都不同，因为它们基于发货日期而不是交易日期。

使用非活动关系仅允许您使用一个日期表，但它确实要求任何度量值 (如按发货日期计算的总销售额) ，在其公式中引用非活动关系。 还有另一种替代方法，即使用多个日期表。

多个日期表

使用数据表中的多个日期列的另一事实数据表是创建多个日期表，并创建它们之间的独立活动关系。 让我们再次查看 Sales 表示例。 我们有三个列，其日期可能想要聚合数据：

• 一个 DateKey，其中每个交易都有销售日期。

• ShipDate – 包含所售商品发货给客户的日期和时间。

• ReturnDate – 具有收到一个或多个返回项的日期和时间。

请记住，具有事务日期的 DateKey 字段最重要。 我们将基于这些日期执行大多数聚合，因此，我们最肯定希望它与"日历"表中的"日期"列之间的关系。 如果不想在 ShipDate 和 ReturnDate 与"日历"表中的"日期"字段之间创建非活动关系，因此需要特殊度量公式，我们可以为发货日期和退货日期创建其他日期表。 然后，我们可以在它们之间创建活动关系。

本示例创建了另一个名为 ShipCalendar 的日期表。 当然，这也意味着创建其他日期列，由于这些日期列在不同的日期表中，因此我们希望将它们与"日历"表中的相同列进行区分。 例如，我们已创建名为 ShipYear、ShipMonth、ShipQuarter 等的列。

如果我们创建数据透视表，将总销售额度量值放在 VALUES 中，将 ShipFiscalYear 和 ShipFiscalQuarter 放在 ROWS 上，则会看到创建非活动关系和特殊的"按发货日期总销售额"计算字段时看到的结果。

每种方法都需要仔细考虑。 对单个日期表使用多个关系时，可能需要使用 USERELATIONSHIP 函数创建特殊度量值来传输非活动关系。 另一方面，在字段列表中创建多个日期表可能令人困惑，并且由于数据模型中有多个表，因此需要更多的内存。 尝试最适合你的方法。

如何：设置日期表属性

1. 在"PowerPivot"窗口中，选择"日历"表。

2. 在"设计"选项卡上，单击"标记为日期表"。

3. 在"标记为日期表"对话框中，选择具有唯一值的列和"日期"数据类型。

将文本数据类型日期转换为日期数据类型

在某些情况下，包含事实数据表数据的数据可能包含文本数据类型。 也就是说，显示为 2012-12-04T11：47：09 的日期实际上不是日期，或者至少不是 Power Pivot 可以理解的日期类型。 它实际上只是像日期一样朗读的文本。 若要在数据表中的日期列与日期表中的日期事实数据表之间创建关系，这两列都必须是"日期"数据类型。

通常，当您尝试将文本 数据类型 的日期列的 数据类型 更改为日期 数据类型 时，Power Pivot 可以自动解释日期并将其转换为真正的日期数据类型值。 如果 Power Pivot 无法执行数据类型转换，则会出现类型不匹配错误。

但是，仍然可以将日期转换为真正的日期数据类型。 你可以创建新的计算列并使用 DAX 公式分析文本字符串中的年、月、日、时间等，然后以 Power Pivot 可以读取为真实日期的方式重新连接它。

本示例将名为 Sales 的事实数据表导入 Power Pivot。 它包含名为 DateTime 的列。 值如下所示：

如果查看 Power Pivot 的"主页"选项卡上的"格式"组中"数据类型"，可以看到它是"文本数据类型"。

由于数据类型不匹配，因此无法在我们的日期表中在 DateTime 列和日期列之间创建关系。 如果尝试将"数据类型 日期"，则会出现类型不匹配错误：

在这种情况下，Power Pivot 无法将文本数据类型文本转换为日期。 我们仍可以使用此列，但要使其进入真正的日期 数据类型，我们需要创建一个新列来分析文本，并重新创建它作为 Power Pivot 可以生成 Date 数据类型 的值。

请记住，从本文前面"使用时间"部分;除非分析必须达到一天中的精度级别，否则应该将日期转换为事实数据表精度级别。 因此，我们希望新列中的值在每天的精度级别， (时间) 。 我们可以使用以下公式将 DateTime 列中的值转换为日期数据类型并删除精度的时间级别：

=DATE (LEFT ([DateTime]，4) ， MID ([DateTime]，6，2) ， MID ([DateTime]，9，2) )

在这种情况下，这会提供名为 Date (的新列) 。 Power Pivot 甚至会检测为日期的值，数据类型日期。

如果要保留精度的时间级别，只需扩展公式以包括小时、分钟和秒。

=DATE (LEFT ([DateTime]，4) ， MID ([DateTime]，6，2) ， MID ([DateTime]，9，2) ) +

TIME (MID ([DateTime]，12，2) ， MID ([DateTime]，15，2) ， MID ([DateTime]，18，2) )

现在，我们有一个"日期"列数据类型，我们可以在日期列和日期列之间创建关系。

其他资源

PowerPivot 中的日期

Power Pivot 中的计算

快速入门：在 30 分钟内了解 DAX 基础知识

数据分析表达式参考

DAX 资源汇