了解数据表

数据分析涉及浏览一段时间内的数据并进行跨时间段的计算。例如，您可能必须将本年度的利润与上一年的利润进行比较。同样，您可能必须预测未来几年的增长和利润。对于这些，您需要在一段时间内使用分组和聚合。

DAX 提供了多种时间智能函数，可帮助您执行大多数此类计算。但是，这些 DAX 函数需要一个日期表才能与数据模型中的其他表一起使用。

您可以从数据源导入日期表和其他数据，也可以在数据模型中自行创建日期表。

在本章中，您将了解日期表的不同方面。如果您熟悉 Power Pivot 数据模型中的日期表，则可以跳过本章并继续后续章节。否则，您可以理解 Power Pivot 数据模型中的日期表。

什么是日期表？

日期表是数据模型中的一个表，在所需的持续时间内至少有一列连续的日期。它可以有代表不同时间段的附加列。但是，必需的是连续日期列，正如 DAX 时间智能函数所要求的那样。

例如，

  • 日期表可以包含日期、会计月份、会计季度和会计年度等列。

  • 日期表可以包含日期、月份、季度和年份等列。

具有连续日期的日期表

假设您需要在一个日历年的范围内进行计算。然后，日期表必须至少有一列包含一组连续的日期，包括该特定日历年中的所有日期。

例如，假设您要浏览的数据的日期为2014年4月1日至2016年11月30日。

  • 如果您必须报告日历年，则需要一个带有“日期”列的日期表，该表按顺序包含从 2014 年 1 月 1日到 2016 年 12 月 31 日的所有日期。

  • 如果您必须报告会计年度，并且您的会计年度结束日期为 6 月 30 日，则需要一个带有“日期”列的日期表，其中包含从 2013 年 7 月 1日到2017 年6 月 30日的所有日期。顺序。

  • 如果您必须报告日历年度和会计年度，那么您可以使用一个涵盖所需日期范围的日期表。

您的日期表必须包含给定持续时间内每年范围内的所有天数。因此，您将获得该时间段内的连续日期。

如果您定期使用新数据刷新数据，则结束日期将延长一两年，这样您就不必经常更新日期表。

日期表类似于以下屏幕截图。

数据表

将日期表添加到数据模型

您可以通过以下任一方式将日期表添加到数据模型 -

  • 从关系数据库或任何其他数据源导入。

  • 在 Excel 中创建日期表，然后复制或链接到 Power Pivot 中的新表。

  • 从 Microsoft Azure 市场导入。

在 Excel 中创建日期表并复制到数据模型

在 Excel 中创建日期表并复制到数据模型是在数据模型中创建数据表的最简单、最灵活的方法。

  • 在 Excel 中打开一个新工作表。

  • 类型 – 列第一行中的日期。

  • 在同一列的第二行中键入要创建的日期范围中的第一个日期。

  • 选择单元格，单击填充柄并将其向下拖动以在所需日期范围内创建一列连续日期。

例如，输入 1/1/2014，单击填充柄并向下拖动以填充截至 31/12/2016 的连续日期。

  • 单击日期列。
  • 单击功能区上的插入选项卡。
  • 单击表。
  • 验证表范围。
  • 单击“确定”。

Excel 中的单列日期表已准备就绪。

单列日期
  • 选择表格。
  • 单击功能区上的复制。
  • 单击“Power Pivot”窗口。
  • 单击功能区上的“粘贴”。
粘贴

这会将剪贴板的内容添加到数据模型中的新表中。因此，您也可以使用相同的方法在现有数据模型中创建日期表。

将出现粘贴预览对话框，如下图所示。

  • 在表名称框中键入日期。
  • 预览数据。
粘贴预览
  • 选中该框 - 使用第一行作为列标题。
  • 单击“确定”。

这会将剪贴板的内容复制到数据模型中的新表中。

现在，数据模型中有一个日期表，其中包含一列连续日期。该列的标题是您在 Excel 表格中给出的日期。

将新日期列添加到日期表

接下来，您可以根据计算要求将计算列添加到日期表中。

例如，您可以添加列 - 日、月、年和季度，如下所示 -

  • 天

    =DAY('日期'[日期])

  • 月

    =月份('日期'[日期])

  • 年

    =年份('日期'[日期])

  • 四分之一

    =CONCATENATE ("QTR ", INT (('日期'[月份]+2)/3))

数据模型中生成的日期表如下图所示。

结果日期表

因此，您可以将任意数量的计算列添加到日期表中。重要且必需的是，日期表必须有一列连续的日期，该日期跨越您执行计算的时间段。

创建日历年的日期表

日历年通常包括一年中的 1 月 1 日至 12 月 31 日，还包括该特定年份标记的假期。执行计算时，您可能只需考虑工作日，不包括周末和节假日。

假设您要为 2017 年创建一个日期表。

  • 创建一个包含“日期”列的 Excel 表，其中包含从 2017 年 1 月 1 日到 2017 年 12 月 31 日的连续日期。（请参阅上一节了解如何执行此操作。）

  • 复制 Excel 表并将其粘贴到数据模型中的新表中。（请参阅上一节了解如何执行此操作。）

  • 将表命名为“日历”。

  • 添加以下计算列 -

    • 日 =DAY('日历'[日期])

    • 月 =MONTH('日历'[日期])

    • 年 =YEAR('日历'[日期])

    • 星期几 =FORMAT('日历'[日期],"DDD")

    • 月份名称 =FORMAT('日历'[日期],"MMM")

添加计算列

将假期添加到日历表

将假期添加到日历表中，如下所示 -

  • 获取当年宣布的假期列表。

  • 例如，对于美国，您可以从以下链接获取任何所需年份的假期列表http://www.calendar-365.com/。

  • 将它们复制并粘贴到 Excel 工作表中。

  • 复制 Excel 表并将其粘贴到数据模型中的新表中。

  • 将表命名为 Holidays。

姓名表假期

  • 接下来，您可以使用 DAX LOOKUPVALUE 函数将计算的假期列添加到日历表中。

=LOOKUPVALUE(节假日[节假日],节假日[日期],'日历'[日期])

DAX LOOKUPVALUE 函数在第二个参数（即Holidays[Date]）中搜索第三个参数（即Calendar[Date]），如果匹配则返回第一个参数（即Holidays[Holiday]）。结果将如以下屏幕截图所示。

查找值

向会计年度添加列

会计年度通常包括从会计年度结束后的下个月1日到下一个会计年度结束的日期。例如，如果会计年度结束日期为 3 月 31 日，则会计年度范围为 4 月 1日至 3 月 31日。

您可以使用 DAX 公式将会计时间段包含在日历表中 -

  • 添加 FYE 度量

    财年:=3

  • 添加以下计算列 -

    • 财政年度

      =IF('日历'[月]<='日历'[财年],'日历'[年],'日历'[年]+1)

    • 财政月

      =IF('日历'[月]<='日历'[财年],12-'日历'[财年]+'日历'[月],'日历'[月]-'日历'[财年] )

    • 财政季度

      =INT(('日历'[财政月份]+2)/3)

向会计年度添加列

设置日期表属性

当您使用 TOTALYTD、PREVIOUSMONTH 和 DATESBETWEEN 等 DAX 时间智能函数时，它们需要元数据才能正常工作。日期表属性设置此类元数据。

设置日期表属性 -

  • 在 Power Pivot 窗口中选择日历表。
  • 单击功能区上的“设计”选项卡。
  • 单击“日历”组中的“标记为日期表”。
  • 单击下拉列表中的“标记为日期表”。
设置日期表属性

将出现“标记为日期表”对话框。选择日历表中的日期列。这必须是日期数据类型的列并且必须具有唯一值。单击“确定”。

标记为日期表