Data Modeling With Dax 简明教程

Understanding Data Tables

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

Data Analysis involves browsing data over time and making calculations across time periods. For example, you might have to compare the current year’s profits with the previous year’s profits. Similarly, you might have to forecast the growth and profits in the coming years. For these, you need to use grouping and aggregations over a period of time.

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

DAX provides several Time Intelligence functions that help you perform most of such calculations. However, these DAX functions require a Date table for usage with the other tables in the data model.

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

You can either import a Date table along with other data from a data source or you can create a Date table by yourself in the data model.

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

In this chapter, you will understand different aspects of Date tables. If you are conversant with Date tables in the Power Pivot data model, you can skip this chapter and proceed with the subsequent chapters. Otherwise, you can understand the Date tables in the Power Pivot data model.

What is a Date Table?

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

A Date Table is a table in a data model, with at least one column of contiguous dates across a required duration. It can have additional columns representing different time periods. However, what is necessary is the column of contiguous dates, as required by the DAX Time Intelligence functions.

例如，

For example,

  1. A Date table can have columns such as Date, Fiscal Month, Fiscal Quarter, and Fiscal Year.

  2. A Date table can have columns such as Date, Month, Quarter, and Year.

Date Table with Contiguous Dates

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

Suppose you are required to make calculations in the range of a calendar year. Then, the Date table must have at least one column with a contiguous set of dates, including all the dates in that specific calendar year.

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

For example, suppose the data you want to browse has dates from April 1st, 2014 through November 30th, 2016.

  1. If you have to report on a calendar year, you need a Date table with a column – Date, which contains all the dates from January 1st, 2014 to December 31st, 2016 in a sequence.

  2. If you have to report on a fiscal year, and your fiscal year end is 30th June, you need a Date table with a column – Date, which contains all the dates from July 1st, 2013 to June 30th, 2017 in a sequence.

  3. If you have to report on both calendar and fiscal years, then you can have a single Date table spanning the required range of dates.

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

Your Date table must contain all of the days for the range of every year in the given duration. Thus, you will get contiguous dates within that period of time.

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

If you regularly refresh your data with new data, you will have the end date extended by a year or two, so that you do not have to update your Date table often.

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

A Date table looks like the following screenshot.

data table

Adding a Date Table to the Data Model

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

You can add a Date table to the data model in any of the following ways −

  1. Importing from a relational database, or any other data source.

  2. Creating a Date table in Excel and then copying or linking to a new table in Power Pivot.

  3. Importing from Microsoft Azure Marketplace.

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

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

Creating a Date table in Excel and copying to the data model is the easiest and most flexible way of creating a Data table in the data model.

  1. Open a new worksheet in Excel.

  2. Type – Date in the first row of a column.

  3. Type the first date in the date range that you want to create in the second row in the same column.

  4. Select the cell, click the fill handle and drag it down to create a column of contiguous dates in the required date range.

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

For example, type 1/1/2014, click the fill handle and drag down to fill the contiguous dates up to 31/12/2016.

  1. Click the Date column.

  2. Click the INSERT tab on the Ribbon.

  3. Click Table.

  4. Verify the table range.

  5. Click OK.

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

The table of a single column of dates is ready in Excel.

single column of dates

  1. Select the table.

  2. Click Copy on the Ribbon.

  3. Click the Power Pivot window.

  4. Click Paste on the Ribbon.

paste

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

This will add the contents of the clipboard to a new table in the data model. Hence, you can use the same method to create a Date table in an existing data model also.

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

Paste preview dialog box appears as shown in the following screenshot.

  1. Type Date in the Table Name box.

  2. Preview the data.

paste preview

  1. Check the box – Use first row as column headers.

  2. Click OK.

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

This copies the contents of the clipboard to a new table in the data model.

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

Now, you have a Date table in the data model with a single column of contiguous dates. The header of the column is Date as you had given in the Excel table.

Adding New Date Columns to the Date Table

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

Next, you can add calculated columns to the Date table as per the requirement for your calculations.

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

For example, you can add columns – Day, Month, Year, and Quarter as follows −

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

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

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

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

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

The resulting Date table in the data model looks like the following screenshot.

resulting data table

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

Thus, you can add any number of calculated columns to the Date table. What is important and is required is that the Date table must have a column of contiguous dates that spans the duration of time over which you perform calculations.

Creating a Date Table for a Calendar Year

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

A calendar year typically includes the dates from 1st January to 31st December of a year and also includes the holidays marked for that particular year. When you perform calculations, you might have to take into account only the working days, excluding weekends and holidays.

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

Suppose, you want to create a Date table for the calendar year 2017.

  1. Create an Excel table with a column Date, consisting of contiguous dates from 1st January 2017 to 31st December 2017. (Refer to the previous section to know how to do this.)

  2. Copy the Excel table and paste it into a new table in the data model. (Refer to the previous section to know how to do this.)

  3. Name the table as Calendar.

  4. Add the following calculated columns − 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")

adding calculated columns

Adding Holidays to the Calendar Table

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

Add holidays to the Calendar Table as follows −

  1. Get the list of declared holidays for the year.

  2. For example, for the US, you can get the list of holidays for any required year from the following link http://www.calendar-365.com/.

  3. Copy and paste them into an Excel worksheet.

  4. Copy the Excel table and paste it into a new table in the data model.

  5. Name the table as Holidays.

name table holidays

  1. Next, you can add a calculated column of holidays to the Calendar table using DAX LOOKUPVALUE function.

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

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

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

DAX LOOKUPVALUE function searches the third parameter, i.e. Calendar[Date] in the second parameter, i.e. Holidays[Date] and returns the first parameter, i.e. Holidays[Holiday] if there is a match. The result will look like what is shown in the following screenshot.

lookupvalue

Adding Columns to a Fiscal Year

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

A fiscal year typically includes the dates from 1st of the month after the fiscal year end to the next fiscal year end. For example, if the fiscal year end is 31st March, then the fiscal year ranges from 1st April to 31st March.

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

You can include the fiscal time periods in the calendar table using the DAX formulas −

  1. Add a measure for FYE FYE:=3

  2. Add the following calculated columns − Fiscal Year =IF('Calendar'[Month]⇐'Calendar'[FYE],'Calendar'[Year],'Calendar'[role="Year"][.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

Setting the Date Table Property

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

When you use DAX Time Intelligence functions such as TOTALYTD, PREVIOUSMONTH, and DATESBETWEEN, they require metadata to work correctly. Date Table Property sets such metadata.

若要设置日期表属性 −

To set the Date Table property −

  1. Select Calendar table in the Power Pivot window.

  2. Click the Design tab on the Ribbon.

  3. Click Mark as Date Table in the Calendars group.

  4. Click Mark as Date Table in the dropdown list.

setting the date table property

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

Mark as Date Table dialog box appears. Select the Date column in the Calendar table. This has to be the column of Date data type and has to have unique values. Click OK.

mark as date table