理解数据表

上一页
下一页

数据分析涉及到随着时间的推移浏览数据并在时间段内进行计算。例如，您可能需要将今年的利润与去年的利润进行比较。同样，您可能需要预测未来几年的增长和利润。为此，您需要对一段时间进行分组和聚合。

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日的所有日期。

  • 如果您需要同时按日历年和财年进行报表，则可以使用一个跨越所需日期范围的日期表。

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

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

日期表如下面的屏幕截图所示。

Data Table

将日期表添加到数据模型

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

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

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

  • 从Microsoft Azure Marketplace导入。

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

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

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

  • 在一列的第一行键入“日期”。

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

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

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

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

包含单列日期的表格已在Excel中准备好。

Single Column of Dates
  • 选择表格。
  • 单击功能区上的“复制”。
  • 单击Power Pivot窗口。
  • 单击功能区上的“粘贴”。
Paste

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

粘贴预览对话框出现，如下面的屏幕截图所示。

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

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

现在，您在数据模型中有一个包含单列连续日期的日期表。列的标题为“日期”，如您在Excel表格中所给定的。

向日期表添加新的日期列

接下来，您可以根据计算需求向日期表添加计算列。

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

  • 日

    =DAY('Date'[Date])

  • 月

    =MONTH('Date'[Date])

  • 年

    =YEAR('Date'[Date])

  • 季度

    =CONCATENATE ("QTR ", INT (('Date'[Month]+2)/3))

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

Resulting Date table

因此，您可以向日期表添加任意数量的计算列。重要的是，日期表必须包含一列跨越执行计算的时间段的连续日期。

为日历年创建日期表

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

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

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

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

  • 将表格命名为“日历”。

  • 添加以下计算列：

    • 日 =DAY('Calendar'[Date])

    • 月 =MONTH('Calendar'[Date])

    • 年 =YEAR('Calendar'[Date])

    • 星期几 =FORMAT('Calendar'[Date],"DDD")

    • 月份名称 =FORMAT('Calendar'[Date],"MMM")

Adding Calculated Columns

向日历表添加节假日

按如下方式向日历表添加节假日：

  • 获取该年份宣布的节假日列表。

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

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

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

  • 将表格命名为“节假日”。

Name Table Holidays

  • 接下来，您可以使用DAX LOOKUPVALUE函数向日历表添加一个计算的节假日列。

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],'Calendar'[Date])

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

Lookupvalue

向财年添加列

财年通常包括从财年结束后的第一个月的第一天到下一个财年结束的日期。例如，如果财年结束日期是3月31日，则财年范围是从4月1日至3月31日。

您可以使用DAX公式将财务时间段包含在日历表中：

  • 为FYE添加一个度量值

    FYE:=3

  • 添加以下计算列：

    • 财年

      =IF('Calendar'[Month]<='Calendar'[FYE],'Calendar'[Year],'Calendar'[Year]+1)

    • 财务月份

      =IF('Calendar'[Month]<='Calendar'[FYE],12-'Calendar'[FYE]+'Calendar'[Month],'Calendar'[Month]-'Calendar'[FYE] )

    • 财务季度

      =INT(('Calendar'[Fiscal Month]+2)/3)

Adding Columns to Fiscal Year

设置日期表属性

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

要设置日期表属性：

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

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

Mark as Date Table
打印页面
上一页
下一页
广告

© . All rights reserved.