Data Modeling With Dax 简明教程

Understanding Data Tables

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

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

您可以从数据源导入“日期”表以及其他数据,也可以自己在数据模型中创建“日期”表。

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

What is a Date Table?

“日期”表是数据模型中的一张表,至少有一列连续日期跨越所需持续时间。它可以有其他表示不同时间段的列。然而,正如 DAX 时间智能函数所需的,连续的日期列是必需的。

例如,

  1. “日期”表可以包含日期、财政月份、财政季度和财政年份等列。

  2. “日期”表可以包含日期、月份、季度和年份等列。

Date Table with Contiguous Dates

假设您需要在公历年范围内进行计算。那么,“日期”表至少必须有一列包含连续日期集,包括该特定公历年中的所有日期。

例如,假设您要浏览的数据的日期从 2014 年 4 月 1 日持续到 2016 年 11 月 30 日。

  1. 如果您必须报告公历年,您需要一张“日期”表,其中包含一列 - “日期”,其按顺序包含从 2014 年 1 月 1 日到 2016 年 12 月 31 日的所有日期。

  2. 如果您必须报告财政年度,并且您的财政年度末是 6 月 30 日,您需要一张“日期”表,其中包含一列 - “日期”,其按顺序包含从 2013 年 7 月 1 日到 2017 年 6 月 30 日的所有日期。

  3. 如果您必须报告公历年和财政年度,那么您可以拥有一张涵盖所需日期范围的“日期”表。

表中必须包含给定期限内每一年所有日期。因此,你会在这段时间内得到连续的日期。

如果你会定期用新数据刷新数据,那么你必须将结束日期延长一年或两年,这样你就不必频繁更新表。

表看起来像下面的屏幕截图。

data table

Adding a Date Table to the Data Model

你可以通过以下方法将表添加到数据模型中——

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

  2. 在 Excel 中创建一个表,然后复制或链接到 Power Pivot 中的一个新表。

  3. 从 Microsoft Azure Marketplace 导入。

Creating a Date Table in Excel and Copying to the Data Model

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

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

  2. - 在列第一行输入日期。

  3. 在同一列第二行中输入要创建的日期范围内的第一日期。

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

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

  1. Click the Date column.

  2. 单击功能区上的插入选项卡。

  3. Click Table.

  4. Verify the table range.

  5. Click OK.

Excel 中准备好了单列日期的表。

single column of dates
  1. Select the table.

  2. 单击功能区上的复制。

  3. 单击 Power Pivot 窗口。

  4. 单击功能区上的粘贴。

paste

这将把剪贴板内容添加到数据模型中的新表。因此,你也可以使用同样的方法在现有数据模型中创建表。

在下面的屏幕截图所示的粘贴预览对话框中显示。

  1. 在表名框中键入日期。

  2. Preview the data.

paste preview
  1. 选中框 - 将第一行用作列标题。

  2. Click OK.

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

现在,你在数据模型中有了一个日期表,其中包含一列连续日期。如你在 Excel 表中给出的那样,该列的标题是日期。

Adding New Date Columns to the Date Table

接下来,你可以根据计算需求向日期表中添加计算列。

例如,你可以按如下方式添加列 - 日、月、年和季度 −

  1. Day =DAY('Date'[Date])

  2. Month =MONTH('Date'[Date])

  3. Year =YEAR('Date'[Date])

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

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

resulting data table

因此,你可以向日期表中添加任意数量的计算列。重要的是,并且需要的是,日期表必须有一列连续日期,跨越你要执行计算的时间范围。

Creating a Date Table for a Calendar Year

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

假设你想为日历年 2017 创建一个日期表。

  1. 创建一个 Excel 表,其中有一列日期,包括从 2017 年 1 月 1 日到 2017 年 12 月 31 日的连续日期。(请参考上一部分了解如何执行此操作。)

  2. 复制 Excel 表并将其粘贴到数据模型中的新表中。(请参考上一部分了解如何执行此操作。)

  3. 将该表的名称命名为日历。

  4. 添加以下计算列 - 日 = DAY('Calendar'[Date]) 月 = MONTH('Calendar'[Date]) 年 = YEAR('Calendar'[Date]) 星期几 = FORMAT('Calendar'[Date],"DDD") 月份名称 = FORMAT('Calendar'[Date],"MMM")

adding calculated columns

Adding Holidays to the Calendar Table

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

  1. 获取该年的申报节假日清单。

  2. 例如,对于美国,你可以从以下链接中获取任何需要的年份的节假日清单 http://www.calendar-365.com/

  3. 将其复制粘贴到 Excel 工作表中。

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

  5. 将该表命名为 Holidays。

name table holidays
  1. 接下来,可以使用 DAX LOOKUPVALUE 函数,将假日计算列添加到日历表中。

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

DAX LOOKUPVALUE 函数在第三个参数(即 Holidays[日期] 中的日历[日期])中搜索第二个参数(即 Holidays[日期]),如果存在匹配项,则返回第一个参数(即 Holidays[假日])。结果将类似于以下屏幕截图中显示的内容。

lookupvalue

Adding Columns to a Fiscal Year

一个财政年度通常包括从财政年度结束后的下一个月 1 日到下一个财政年度结束的日期。例如,如果财政年度结束日期是 3 月 31 日,则该财政年度是从 4 月 1 日到 3 月 31 日。

可以使用 DAX 公式将财政期间纳入日历表 −

  1. 添加 FYE 的措施 FYE:=3

  2. 添加以下计算列 − Fiscal Year =IF('Calendar'[月]⇐ 'Calendar'[FYE],'Calendar'[年],'Calendar'[role="Year"][.年]1) Fiscal Month =IF('Calendar'[月]⇐'Calendar'[FYE],12-'Calendar'[FYE]'Calendar'[月],'Calendar'[月]-'Calendar'[FYE] ) Fiscal Quarter =INT(('Calendar'[财政月]+2)/3)

adding columns to fiscal year

Setting the Date Table Property

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

若要设置日期表属性 −

  1. 在 Power Pivot 窗口中选择日历表。

  2. 在功能区上点击“设计”选项卡。

  3. 在日历组中,单击标记为日期表。

  4. 在下拉列表中,单击标记为日期表。

setting the date table property

标记为日期表对话框显示。在日历表中,选择日期列。这必须是日期数据类型列,且必须具有唯一值。单击确定。

mark as date table