使用DAX教程进行数据建模

了解数据表

← 上一节 下一节 →

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

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

您可以导入Date表以及数据源中的其他数据，也可以创建数据模型中的自己的日期表.

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

什么是日期表？

日期表是一个表格数据模型，在所需的持续时间内至少有一列连续的日期.它可以包含代表不同时间段的其他列.但是，必要的是DAX时间智能函数所需的连续日期列.

例如，

  • 日期表可以包含日期，会计月，财政季度和会计年度等列.

  • A日期表可以包含日期，月份，季度和年份等列.

具有连续日期的日期表

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

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

  • 如果必须报告日历年，您需要一个带有列的日期表 - 日期，其中包含2014年1月1日 st ，2014年12月31日 st ，2016年的所有日期序列.

  • 如果您必须报告一个会计年度，并且您的会计年度结束时间为30 th 6月，则需要带有列的日期表 - 日期，其中包含2013年7月1日 st ，2013年6月30日 th ，2017年的所有日期.

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

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

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

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

数据表

将日期表添加到数据模型

您可以将Date表添加到数据模型中以下任何一种方式 :

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

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

  • 从Microsoft Azure Marketplace导入.

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

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

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

  • 类型 - 列第一行中的日期.

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

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

例如，键入1/1/2014，单击填充手柄并向下拖动到填写截至2016年12月31日的连续日期.

  • 点击日期栏.

  • 单击功能区上的"插入"选项卡.

  • 单击"表格".

  • 验证表格范围.

  • 单击确定.

单个日期列的表已在Excel中准备好.

Dat的单列es

  • 选择表.

  • 单击功能区上的"复制".

  • 单击Power Pivot窗口.

  • 单击功能区上的粘贴.

粘贴

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

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

  • 在"表名称"框中键入日期.

  • 预览数据.

粘贴预览

  • 检查方框 - 将第一行用作列标题.

  • 单击"确定".

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

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

将新日期列添加到日期表

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

例如，您可以添加列 - 日，月，年和季，如下 :

  • Day

    =DAY('Date'[Date])

  • Month

    =MONTH('Date'[Date])

  • Year

    =YEAR('Date'[Date])

  • Quarter

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

数据模型中生成的Date表如下图所示.

Resulting Date table

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

创建日历年的日期表

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

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

  • 创建一个Excel表格，其中包含日期，包括从2017年1月1日起的连续日期到31 st 2017年12月.(请参阅上一节了解如何执行此操作.)

  • 复制Excel表并粘贴它进入数据模型中的新表. (请参阅上一节以了解如何执行此操作.)

  • 将表命名为Calendar.

  • 添加以下计算列 :

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

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

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

    • Day of Week =FORMAT('Calendar'[Date],"DDD")

    • Month Name =FORMAT('Calendar'[Date],"MMM")

添加计算列

将假期添加到日历表

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

  • 获取年度申报假期列表.

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

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

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

  • 将表命名为Holidays.

名表假期

  • 接下来，您可以添加使用DAX LOOKUPVALUE函数计算的Calendar表假期列.

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

DAX LOOKUPVALUE函数搜索第三个参数，即第二个参数中的Calendar [Date]，即Holidays [Date]并返回第一个参数，即假日[假日]，如果有匹配.结果将如下面的屏幕截图所示.

Lookupvalue

将列添加到会计年度

会计年度通常包括从会计年度结束到下一会计年度结束的月份的1 st 的日期.例如，如果会计年度结束时为31 st 3月，那么会计年度范围从1月1日 st 到3月31日 st .

您可以使用DAX公式 : 去日历表中的财政时间段;

  • 为FYE添加度量

    FYE:= 3

  • 添加以下计算列 :

    • Fiscal Year

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

    • Fiscal Month

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

    • Fiscal Quarter

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

Adding Columns to Fiscal Year

设置日期表属性

使用DAX时间智能功能时，如TOTALYTD，PREVIOUSMONTH和DATESBETWEEN，他们需要元数据才能正常工作.日期表属性设置此类元数据.

设置日期表属性 :

  • 选择Power Pivot窗口中的日历表.

  • 单击功能区上的"设计"选项卡.

  • 在"日历"组中单击"标记为日期表".

  • 在下拉列表中单击标记为日期表.

设置日期表属性

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

标记为日期表

← 上一节 下一节 →

相关菜鸟教程