Data Modeling With Dax 简明教程

Data Modeling with DAX - Quick Guide

Data Modeling with DAX - Overview

所有组织的决策者都已明确指出，需要分析组织的历史数据，特别是整个行业的历史数据。在当今竞争激烈的世界中，为了应对不断变化的业务挑战，这一点正变得越来越重要。

The decision makers in all the organizations have identified the need for analyzing the historical data of their organization in specific, and of the industry in general. This is becoming crucial day-by-day in the present competitive world, to meet the ever-changing business challenges.

大数据和商业智能已成为商业领域的热门词汇。数据源变得巨大，数据格式也变得多样化。当务之急是拥有易于使用的工具，以便在更短的时间内处理不断流动的庞大数据，从而深入了解并在适当的时间做出相关决策。

Big Data and Business Intelligence have become the buzzwords in the business world. Data sources have become huge and data formats have become variant. The need of the hour is to have simple-to-use tools to handle the ever-flowing vast data in less time to gain insight and make relevant decisions at the appropriate time.

数据分析师不再需要等待 IT 部门处理所需数据。他们需要一个方便的工具，使他们能够快速理解所需数据，并将其提供以一种有助于决策者在正确时间采取必要行动的格式。

Data analysts can no longer wait for the required data to be processed by the IT department. They require a handy tool that enables them to quickly comprehend the required data and make it available in a format that helps the decision makers take required action at the right time.

Microsoft Excel 拥有一个功能强大的工具，称为 Power Pivot，在 Excel 的早期版本中可用作加载项，并且在 Excel 2016 中已成为内置功能。Power Pivot 数据库（称为数据模型）以及作用于数据模型的公式语言（称为 DAX，即 *D*ata *A*nalysis *E*xpressions，数据分析表达式）使 Excel 用户能够在短时间内执行诸如数据建模和分析等任务。

Microsoft Excel has a powerful tool called as Power Pivot that was available as an add-in in the prior versions of Excel and is built-in feature in Excel 2016. The database of Power Pivot, called the data model and the formula language that works on the data model, called DAX (*D*ata *A*nalysis *E*xpressions) enables an Excel user to perform tasks such as data modeling and analysis in no time.

在本教程中，您将基于 Power Pivot 数据模型来学习使用 DAX 进行数据建模和分析。本教程的说明中使用了一个利润和分析数据库样本。

In this tutorial, you will learn data modeling and analysis using DAX, based on the Power Pivot data model. A sample Profit and Analysis database is used for the illustrations throughout this tutorial.

Data Modeling and Analysis Concepts

需要从各种来源获取的数据称为原始数据，在应用于分析目的之前需要进行处理。您将在本章节中了解这些内容——数据建模和分析概念。

The data that you obtain from different variety of sources, termed as raw data, needs processing before you can utilize it for analysis purposes. You will learn about these in the chapter − Data Modeling and Analysis Concepts.

Data Modeling and Analysis with Excel Power Pivot

由于您将在本教程中掌握的工具是 Excel Power Pivot，因此您需要了解如何在 Power Pivot 中执行数据建模和分析步骤。您将在更广泛的层面学习这些内容——使用 Excel Power Pivot 进行数据建模和分析。

Since the tool that you will be mastering in this tutorial is Excel Power Pivot, you need to know how the data modeling and analysis steps are carried out in Power Pivot. You will learn these at a broader level in the chapter - Data Modeling and Analysis with Excel Power Pivot.

在学习后续章节时，您将了解 Power Pivot、DAX 和 DAX 函数在数据建模和分析中的不同方面。

As you proceed with the subsequent chapters, you will learn about the different facets of Power Pivot, DAX and DAX functions in data modeling and analysis.

到教程结束时，您将能够针对任何上下文执行使用 DAX 进行数据建模和分析。

By the end of the tutorial, you will be able to perform data modeling and analysis with DAX for any context at hand.

Data Modeling with DAX - Concepts

商业智能 (BI) 在多个领域和组织中日益重要。在日益激烈的竞争环境中，基于历史数据的决策和预测已变得至关重要。对于任何类型的数据分析而言，都有大量数据可从内部和外部的不同来源获得。

Business Intelligence (BI) is gaining importance in several fields and organizations. Decision making and forecasting based on historical data have become crucial in the evergrowing competitive world. There is huge amount of data available both internally and externally from diversified sources for any type of data analysis.

然而，挑战在于根据当前需求从可用大数据中提取相关数据，并以一种便于从数据中预测不同见解的方式存储数据。因此，使用关键业务术语获得的数据模型是一种有价值的沟通工具。该数据模型还需要提供按需生成报告的快速方法。

However, the challenge is to extract the relevant data from the available big data as per the current requirements, and to store it in a way that is amicable for projecting different insights from the data. A data model thus obtained with the usage of key business terms is a valuable communication tool. The data model also needs to provide a quick way of generating reports on an as needed basis.

面向 BI 系统的数据建模使您能够应对许多数据挑战。

Data modeling for BI systems enables you to meet many of the data challenges.

Prerequisites for a Data Model for BI

BI 数据模型应满足正在对其进行数据分析的业务需求。以下是对任何数据模型提出的基本要求：

A data model for BI should meet the requirements of the business for which data analysis is being done. Following are the minimum basics that any data model has to meet −

The data model needs to be Business Specific

适合一条业务线的模型可能不适合另一条业务线。因此，必须根据特定业务、使用的业务术语、数据类型及其关系来开发数据模型。它应基于组织的目标和决策类型。

A data model that is suitable for one line of business might not be suitable for a different line of business. Hence, the data model must be developed based on the specific business, the business terms used, the data types, and their relationships. It should be based on the objectives and the type of decisions made in the organization.

The data model needs to have built-in Intelligence

数据模型应通过元数据、层次结构和继承关系包含内置智能，从而促进高效有效的商业智能流程。通过这一点，您将能够为不同用户提供一个公共平台，从而消除重复的流程。

The data model should include built-in intelligence through metadata, hierarchies, and inheritances that facilitate efficient and effective Business Intelligence process. With this, you will be able to provide a common platform for different users, eliminating repetition of the process.

The data model needs to be Robust

数据模型应准确地展示特定于业务的数据。它应启用有效的磁盘和内存存储，以方便快速处理和报告。

The data model should precisely present the data specific to the business. It should enable effective disk and memory storage so as to facilitate quick processing and reporting.

The data model needs to be Scalable

数据模型应该能够以快速、有效的方式适应不断变化的业务场景。可能需要包括新的数据或数据类型。可能需要有效处理数据刷新。

The data model should be able to accommodate the changing business scenarios in a quick and efficient way. New data or new data types might have to be included. Data refreshes might have to be handled effectively.

Data Modeling for BI

BI 的数据建模包含以下步骤 -

Data modeling for BI consists of the following steps −

Shaping the data
Loading the data
Defining the relationships between the tables
Defining data types
Creating new data insights

Shaping the Data

构建数据模型所需的数据可以来自多种来源，并且可以采用不同的格式。您需要确定来自每个数据源的数据的哪一部分对于特定的数据分析是必需的。这被称为整形数据。

The data required to build a data model can be from various sources and can be in different formats. You need to determine which portion of the data from each of these data sources is required for specific data analysis. This is called Shaping the Data.

例如，如果您正在检索组织中所有员工的数据，则需要决定每个员工的哪些详细信息与当前上下文相关。换句话说，您需要确定需要导入员工表中的哪些列。这是因为，数据模型中表中的列数越少，对该表的计算速度就越快。

For example, if you are retrieving the data of all the employees in an organization, you need to decide what details of each employee are relevant to the current context. In other words, you need to determine which columns of the employee table are required to be imported. This is because, the lesser the number of columns in a table in the data model, the faster will be the calculations on the table.

Loading the Data

您需要加载已标识的数据 - 表格中带有每个表中所选列的数据。

You need to load the identified data – the data tables with the chosen columns in each of the tables.

Defining the Relationships Between Tables

接下来，您需要定义不同表之间的逻辑关系，以便于组合来自这些表的数据，即如果您有一个表 - 产品 - 包含有关产品的数据和一个表 - 销售 - 其中包含产品的各种销售交易，通过定义两个表之间的关系，您可以按产品总结销售额。

Next, you need to define the logical relationships between the various tables that facilitate combining data from those tables, i.e. if you have a table – Products - containing data about the products and a table - Sales - with the various sales transactions of the products, by defining a relationship between the two tables, you can summarize the sales, product wise.

Defining Data Types

识别数据模型中数据合适的类型对于计算的准确性至关重要。对于您导入的每个表中的每一列，您需要定义数据类型。例如，文本文档类型、实数数据类型、整型数据类型等。

Identifying the appropriate data types for the data in the data model is crucial for the accuracy of calculations. For each column in each table that you have imported, you need to define the data type. For example, text data type, real number data type, integer data type, etc.

Creating New Data Insights

这是 BI 中数据建模的关键步骤。所构建的数据模型可能必须与需要理解数据趋势并在很短的时间内制定所需决策的几个人共享。因此，从源数据创建新的数据见解将有效，避免对分析进行返工。

This is a crucial step in date modeling for BI. The data model that is built might have to be shared with several people who need to understand data trends and make the required decisions in a very short time. Hence, creating new data insights from the source data will be effective, avoiding rework on the analysis.

新的数据见解可以是特定业务人员可以轻松理解并使用的元数据形式。

The new data insights can be in the form of metadata that can be easily understood and used by specific business people.

Data Analysis

一旦数据模型准备就绪，即可根据需求分析数据。呈现分析结果也是一个重要的步骤，因为决策将基于报告做出。

Once the data model is ready, the data can be analyzed as per the requirement. Presenting the analysis results is also an important step because the decisions will be made based on the reports.

Data Modeling Using Excel Power Pivot

Microsoft Excel Power Pivot 是一个用于数据建模和分析的出色工具。

Microsoft Excel Power Pivot is an excellent tool for data modeling and analysis.

Data model is the Power Pivot database.
DAX is the formula language that can be used to create metadata with the data in the data model by means of DAX formulas.
Power PivotTables in Excel created with the data and metadata in the data model enables you to analyze the data and present the results.

在本教程中，您将学习使用 Power Pivot 数据模型和 DAX 进行数据建模以及使用 Power Pivot 进行数据分析。如果您不熟悉 Power Pivot，请参阅 Excel Power Pivot 教程。

In this tutorial, you will learn data modeling with Power Pivot data model and DAX and data analysis with Power Pivot. If you are new to Power Pivot, please refer to the Excel Power Pivot tutorial.

您已在上一章 - 数据建模和分析概念 - 中学习数据建模过程步骤。在本章中，您将学习如何使用 Power Pivot 数据模型和 DAX 执行每个这些步骤。

You have learnt the data modeling process steps in the previous chapter - Data Modeling and Analysis Concepts. In this chapter, you will learn how to execute each of those steps with Power Pivot data model and DAX.

在以下各节中，您将学习应用于 Power Pivot 数据模型的每个过程步骤，以及如何使用 DAX。

In the following sections, you will learn each of these process steps as applied to Power Pivot data model and how DAX is used.

Shaping the Data

在 Excel Power Pivot 中，您可以从各种类型的数据源导入数据，并在导入时，您可以查看并选择要导入的表和列。

In Excel Power Pivot, you can import data from various types of data sources and while importing, you can view and choose the tables and columns that you want to import.

Identify the data sources.
Find the data source types. For example, database or data service or any other data source.
Decide on what data is relevant in the current context.
Decide on the appropriate data types for the data. In Power Pivot data model, you can have only one data type for the entire column in a table.
Identify which of the tables are the fact tables and which are the dimensional tables.
Decide on the relevant logical relationships between the tables.

Loading Data into the Data Model

您可以通过功能区“Power Pivot”窗口中提供的多个选项将数据加载到数据模型中。你可以在该组中找到这些选项，“获取外部数据”。

You can load data into the data model with several options provided in the Power Pivot window on the Ribbon. You can find these options in the group, Get External Data.

在章 – 将数据加载到数据模型中，您将学习如何从 Access 数据库加载数据到数据模型。

You will learn how to load data from an Access database into the data model in the chapter – Loading Data into the Data Model.

出于说明目的，已使用一个包含损益数据的 Access 数据库。

For illustration purposes, an Access database with Profit and Loss data is used.

Defining Data Types in the Data Model

Power Pivot 中数据建模过程中的下一步是定义加载到数据模型中的表的列的数据类型。

The next step in the data modeling process in Power Pivot is defining data types of the columns in the tables that are loaded into the data model.

在章 – 在数据模型中定义数据模型中，您将学习如何定义表中的列的数据类型。

You will learn how to define data types of the columns in the tables in the chapter – Defining Data Types in the Data Model.

Creating Relationships Between the Tables

Power Pivot 中数据建模过程中的下一步是创建数据模型中的表之间的关系。

The next step in the data modeling process in Power Pivot is creating relationships between the tables in the data model.

在章 – 扩展数据模型中，您将学习如何创建表之间的关系。

You will learn how to create relationships between the tables in the chapter – Extending the Data Model.

Creating New Data Insights

在数据模型中，您可以通过下列方法创建生成新的数据见解的元数据 −

In the data model, you can create metadata necessary for creating new data insights by −

Creating Calculated Columns
Creating Date Table
Creating Measures

然后可以通过创建基于表中的列和作为数据透视表字段列表中字段的衡量指标，来分析数据。

You can then analyze the data by creating dynamic Power PivotTables that are based on the columns in the tables and measures that appear as fields in the PivotTable Fields list.

Adding Calculated Columns

表中的计算列是通过使用 DAX 公式添加到表中的列。

Calculated columns in a table are the columns that you add to a table by using DAX formulas.

在章 – 扩展数据模型中，您将学习如何向表中的数据模型添加计算列。

You will learn how to add calculated columns in a table in the data model in the chapter - Extending the Data Model.

Creating Date Table

为了使用 DAX 公式中的时间智能函数来创建元数据，您需要一个日期表。如果您不熟悉日期表，请参阅章– 理解日期表。

To use Time Intelligence Functions in DAX formulas to create metadata, you require a Date table. If you are new to Date tables, please refer to the chapter – Understanding Date Tables.

在章 – 扩展数据模型中，您将学习如何在数据模型中创建日期表。

You will learn how to create a Date table in the data model in the chapter – Extending the Data Model.

Creating Measures

您可以通过使用 DAX 函数和 DAX 公式在数据表中创建各种衡量指标，以根据当前上下文中数据分析的不同需要进行计算。

You can create various measures in the Data table by using the DAX functions and DAX formulas for different calculations as required for data analysis in the current context.

这是使用DAX数据建模的关键步骤。

This is the crucial step of data modeling with DAX.

在后续章节中，你将学习如何针对利润和损益分析的不同目的创建度量。

You will learn how to create the measures for various purposes of profit and loss analysis in the subsequent chapters.

Analyzing Data with Power PivotTables

你可以针对利润和损益分析的每一个方面创建Power PivotTable（透视表）。当你学习如何在后续章节中使用DAX创建度量时，你还将学习如何使用Power PivotTable（透视表）分析数据。

You can create Power PivotTables for each of the facets of profit and loss analysis. As you learn how to create measures using DAX in the subsequent chapters, you will also learn how to analyze data with these measures using Power PivotTables.

Loading Data into the Data Model

你可以从不同类型的数据源将数据加载到数据模型中。为此，你可以在Power Pivot窗口的Ribbon（功能区）中的获取外部数据组中找到各种选项。

You can load data from different types of data sources into the data model. For this, you can find various options in the Get External Data group on the Ribbon in the Power Pivot window.

正如你所观察到的那样，你可以从数据库中或从数据服务或其他几种类型的数据源中加载数据。

As you can observe, you can load data from databases, or from data services or several other types of data sources.

当你从数据源中将数据加载到数据模型中时，将会与数据源建立连接。这使得当源数据更改时能够刷新数据。

When you load data from a data source into the data model, a connection will be established with the data source. This enables data refresh when the source data changes.

Initiating with a New Data Model

在本节中，你将学习如何为利润和损益分析建模数据。分析数据在一个Microsoft Access数据库中。

In this section, you will learn how to model the data for profit and loss analysis. The data for analysis is in a Microsoft Access database.

你可以如下启动一个新的数据模型−

You can initiate a new data model as follows −

Open a new Excel workbook
Click the PowerPivot tab on the Ribbon
Click Manage in the Data Model group

此时将出现Power Pivot窗口。由于你尚未加载任何数据，所以该窗口将为空。

The Power Pivot window appears. The window will be blank as you have not yet loaded any data.

Loading Data from Access Database into the Data Model

要从Access数据库加载数据，请执行以下步骤−

To load the data from the Access database, carry out the following steps −

Click From Database in the Get External Data group on the Ribbon.
Click From Access in the dropdown list.

表导入向导对话框出现。

Table Import Wizard dialog box appears.

Browse to the Access file.
Give a friendly name for the connection.

Click the Next button. The next part of the Table Import Wizard appears.
In the Table Import Wizard, select the option – Select from a list of tables and views to choose the data to import.

Click the Next button. The next part of the Table Import Wizard appears as shown in the following screenshot.
Select all the tables.
Give friendly names to the tables. This is necessary because these names appear in the Power PivotTables and hence should be understood by everyone.

Choosing the Columns in the Tables

对于当前分析，你可能不需要选定表中的所有列。因此，你需要仅选择在构建数据时选择的那些列。

You might not require all the columns in the selected tables for the current analysis. Hence, you need to select only those columns that you selected while shaping the data.

Click the Preview & Filter button. The next part of the Table Import Wizard -Preview of the selected table - appears.

As seen in the above screenshot, the column headers have check boxes. Select the columns you want to import in the selected table.
Click OK. Repeat the same for the other tables.

Importing Data into the Data Model

你现在正处于将数据加载到数据模型的最后阶段。在表导入向导中单击“完成”按钮，表导入向导的下一部分显示。

You are at the last stage of loading data into the data model. Click the Finish button in the Table Import Wizard. The next part of the Table Import Wizard appears.

导入状态将显示。当数据加载完成时，状态最终会显示为“成功”。

The importing status will be displayed. The status finally displays Success when data loading is complete.

Viewing the Data in the Data Model

导入的表显示在 PowerPivots 窗口中。这是数据模型的视图。

The imported tables appear in the Power Pivot window. This is the view of the data model

您可以观察到以下内容 −

You can observe the following −

Each of the tables appear in a separate tab.
The tab names are the respective table names.
The area below the data is for the calculations.

Viewing the Connection Name

在“获取外部数据”组中单击“现有连接”。现有连接对话框会按以下屏幕截图所示的方式显示。

Click the Existing Connections in the Get External Data group. Existing Connections dialog box appears as shown in the following screenshot.

正如在上一个屏幕截图中所示，提供的连接名称显示在 PowerPivot 数据连接中。

As seen in the above screenshot, the connection name given appears under PowerPivot Data Connections.

Defining Data Types in the Data Model

在 PowerPivot 数据模型中，一列中的所有数据必须具有相同数据类型。为了执行准确计算，你需要确保数据模型中每张表中的每列的数据类型符合需求。

In the Power Pivot data model, the entire data in a column must be of the same data type. To accomplish accurate calculations, you need to ensure that the data type of each column in each table in the data model is as per requirement.

Tables in the Data Model

在上一个章节中创建的数据模型中有 3 张表 -

In the data model created in the previous chapter, there are 3 tables −

Accounts
Geography Locn
Finance Data

Ensuring Appropriate Data Types

为了确保表中列符合需求，你需要在 PowerPivot 窗口中检查其数据类型。

To ensure that the columns in the tables are as required, you need to check their data types in the Power Pivot window.

Click a column in a table.
Note the data type of the column as displayed on the Ribbon in the Formatting group.

如果所选列的数据类型不合适，请如下更改数据类型。

If the data type of the selected column is not appropriate, change the data type as follows.

Click the down arrow next to the data type in the Formatting group.
Click the appropriate data type in the dropdown list.
Repeat for every column in all the tables in the data model.

Columns in the Accounts Table

在 Accounts 表中，您有以下的列 −

In the Accounts table, you have the following columns −

Sr.No

Column & Description

*Account*Contains one account number for each row. The column has unique values and is used in defining the relationship with the Finance Data table.

*Class*The class associated with each account. Example - Expenses, Net Revenue, etc.

*Sub Class*Describes the type of expense or revenue. Example – People.

Accounts 表中的所有列都具有描述的性质，因此是 Text 数据类型。

All the columns in the Accounts table are of descriptive in nature and hence are of Text data type.

Columns in the Geography Locn Table

Geography Locn 表包含每个利润中心的数据。

The Geography Locn table contains data about each Profit Center.

Profit Center 列对于每一行包含一个利润中心标识。此列具有唯一的值，用于定义与财务数据表的关联。

The column Profit Center contains one profit center identity for each row. This column has unique values and is used in defining the relationship with the Finance Data table.

Columns in the Finance Data Table

在财务数据表中，您有以下列 −

In the Finance Data table, you have the following columns −

Column

Description

Data type

Fiscal Month

Month and Year

Text

Profit Center

Profit Center identity

Text

Account

Account number. Each account can have multiple Profit Centers.

Text

Budget

Monthly budget amounts for each Profit Center.

Currency

Actual

Monthly actual amounts for each Profit Center.

Currency

Forecast

Monthly forecast amounts for each profit center.

Currency

Actual People

Month end actual number of employees for each Profit Center of each people Account.

Whole Number

Budget People

Month end budget number of employees for each Profit Center of each people Account.

Whole Number

Forecast People

Month end forecast number of employees for each Profit Center of each people Account.

Whole Number

Types of Tables in the Data Model

帐户表和地理位置表都是维度表，也称为 lookup tables 。

Both Accounts and Geography Locn tables are the dimensional tables, also called as lookup tables.

Finance Data table 是事实表，也称为数据表。财务数据表包含盈利和分析计算所需的数据。您还将在本财务数据表中创建元数据（采用度量和计算列的形式），以便在您进行本教程时为各种类型的盈亏计算建立数据模型。

Finance Data table is the fact table, also known as the data table. Finance Data table contains the data required for the profit and analysis calculations. You will also create metadata in the form of measures and calculated columns in this Finance Data table, so as to model the data for various types of profit and loss calculations, as you proceed with this tutorial.

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,

A Date table can have columns such as Date, Fiscal Month, Fiscal Quarter, and Fiscal Year.
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.

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.
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.
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.

Adding a Date Table to the Data Model

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

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

Importing from a relational database, or any other data source.
Creating a Date table in Excel and then copying or linking to a new table in Power Pivot.
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.

Open a new worksheet in Excel.
Type – Date in the first row of a column.
Type the first date in the date range that you want to create in the second row in the same column.
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.

Click the Date column.
Click the INSERT tab on the Ribbon.
Click Table.
Verify the table range.
Click OK.

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

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

Select the table.
Click Copy on the Ribbon.
Click the Power Pivot window.
Click Paste on the Ribbon.

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

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.

Type Date in the Table Name box.
Preview the data.

Check the box – Use first row as column headers.
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 −

Day =DAY('Date'[Date])
Month =MONTH('Date'[Date])
Year =YEAR('Date'[Date])
Quarter =CONCATENATE ("QTR ", INT 'Date'[Month]+2)/3

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

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

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

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.

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.)
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.)
Name the table as Calendar.
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 Holidays to the Calendar Table

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

Add holidays to the Calendar Table as follows −

Get the list of declared holidays for the year.
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/.
Copy and paste them into an Excel worksheet.
Copy the Excel table and paste it into a new table in the data model.
Name the table as Holidays.

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

=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.

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 −

Add a measure for FYE FYE:=3
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)

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 −

Select Calendar table in the Power Pivot window.
Click the Design tab on the Ribbon.
Click Mark as Date Table in the Calendars group.
Click Mark as Date Table in the dropdown list.

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

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.

Extending the Data Model

在本章中，您将学习如何扩展在前面的章节中创建的数据模型。扩展数据模型包括：

In this chapter, you will learn how to extend the data model created in the previous chapters. Extending a data model includes −

Addition of tables
Addition of calculated columns in an existing table
Creation of measures in an existing table

其中，创建度量至关重要，因为它涉及在数据模型中提供新的数据见解，这些见解将使用户在分析数据和做出决策时可以避免返工并节省时间。

Of these, creating the measures is crucial, as it involves providing new data insights in the data model that will enable those using the data model avoid rework and also save time while analyzing the data and decision making.

由于损益分析涉及使用时间段并且您将使用 DAX 时间智能函数，因此您需要在数据模型中拥有“日期”表。

As Profit and Loss Analysis involves working with time periods and you will be using DAX Time Intelligence functions, you require a Date table in the data model.

如果您不了解“日期”表，请通读章节——理解“日期”表。

If you are new to Date tables, go through the chapter – Understanding Date Tables.

您可以按以下方式扩展数据模型 -

You can extend the data model as follows −

To create a relationship between the data table, i.e. Finance Data table and the Date table, you need to create a calculated column Date in the Finance Data table.
To perform different types of calculations, you need to create relationships between the data table - Finance Data and the lookup tables – Accounts and Geography Locn.
You need to create various measures that help you perform several calculations and carry out the required analysis.

这些步骤实质上构成了使用数据模型进行损益分析的数据建模步骤。但是，这是对您要使用 Power Pivot 数据模型执行的任何类型数据分析执行的一系列步骤。

These steps essentially constitute the data modeling steps for Profit and Loss Analysis using the data model. However, this is the sequence of steps for any type of data analysis that you want to perform with Power Pivot data model.

此外，您还将在后续章节中了解如何创建度量以及如何在 Power 透视表中使用它们。这将让您充分了解使用 DAX 建模数据和使用 Power 透视表对数据进行分析的情况。

Further, you will learn how to create the measures and how to use them in the Power PivotTables in the subsequent chapters. This will give you sufficient understanding of data modeling with DAX and data analysis with Power PivotTables.

Adding a Date Table to the Data Model

为跨越财政年度的时间段创建“日期”表，如下所示 -

Create a Date table for the time periods spanning the fiscal years as follows −

Create a table with a single column with header – Date and contiguous dates ranging from 7/1/2011 to 6/30/2018 in a new Excel worksheet.
Copy the table from Excel and paste it into the Power Pivot window. This will create a new table in the Power Pivot data model.
Name the table as Date.
Ensure that the Date column in the Date table is of data type - Date (DateTime).

接下来，您需要根据以下步骤向“日期”表中添加计算列——财政年度、财政季度、财政月份和月份 -

Next, you need to add the calculated columns – Fiscal Year, Fiscal Quarter, Fiscal Month and Month to the Date table as follows −

Fiscal Year

假设财政年度末为 6 月 30 日。那么，一个财政年度从 7 月 1 日跨度到 6 月 30 日。例如，时期 7/1/2011 (7/1/2011) 至 6/30/2012 (6/30/2012) 将是财政年度 2012 年。

Suppose the fiscal year end is June 30th. Then, a fiscal year spans from 1st July to 30th June. For example, the period July 1st, 2011 (7/1/2011) to June 30th, 2012 (6/30/2012) will be the fiscal year 2012.

在“日期”表中，假设您想表示与 FY2012 相同的内容。

In the Date table, suppose you want to represent the same as FY2012.

You need to first extract the financial year part of the Date and append it with FY. For the dates in the months July 2011 to December 2011, the financial year is 1+2011. For the dates in the months January 2012 to June 2012, the financial year is 0+2012. To generalize, if the Month of Financial Year End is FYE, do the following − Integer Part of ((Month – 1)/FYE) + Year Next, take the rightmost 4 characters to obtain the Financial Year.
In DAX, you can represent the same as − RIGHT(INT((MONTH('Date'[Date])-1)/'Date'[FYE])+YEAR('Date'[Date]),4)
Add the calculated column Fiscal Year in the Date table with the DAX formula − ="FY"&RIGHT(INT((MONTH('Date'[Date])-1)/'Date'[FYE])+YEAR('Date'[Date]),4)

Fiscal Quarter

如果 FYE 表示财政年度结束的月份，则财政季度可获得为

If FYE represents the month of financial year end, the financial quarter is obtained as

Integer Part of ((Remainder of ((Month+FYE-1)/12) + 3)/3)

In DAX, you can represent the same as − INT((MOD(MONTH('Date'[Date])+'Date'[FYE]-1,12)+3)/3)
Add the calculated column Fiscal Quarter in the Date table with the DAX formula − ='Date'[FiscalYear]&"-Q"&FORMAT( INT((MOD(MONTH('Date'[Date]) + 'Date'[FYE]-1,12) + 3)/3), "0")

Fiscal Month

如果 FYE 表示财政年度结束，则财政月份可获得为

If FYE represents the financial year end, the financial month period is obtained as

(Remainder of (Month+FYE-1)/12) + 1

In DAX, you can represent the same as − MOD(MONTH('Date'[Date])+'Date'[FYE]-1,12)+1
Add the calculated column Fiscal Month in the Date table with the DAX formula − ='Date'[Fiscal Year]&"-P" & FORMAT(MOD(MONTH([Date])+[FYE]-1,12)+1,"00")

Month

最后，添加计算的列 Month，表示财年中表示月份数，如下所示 −

Finally, add the calculated column Month that represents the month number in a financial year as follows −

=FORMAT(MOD(MONTH([Date])+[FYE]-1,12)+1,"00") & "-" & FORMAT([Date],"mmm")

生成日期表如下面截图所示。

The resulting Date table looks like the following screenshot.

标记表 – 指定日期为日期表，以 - 日期作为具有唯一值的列，如下面截图所示。

Mark the table – Date as Date Table with the column - Date as the column with unique values as shown in the following screenshot.

Adding Calculated Columns

要创建财务数据表和日期表之间的关系，需要在财务数据表中创建一列日期值。

To create a relationship between the Finance Data table and the Date table, you require a column of Date values in the Finance Data table.

Add a calculated column Date in the Finance Data table with the DAX formula − = DATEVALUE ('Finance Data'[Fiscal Month])

Defining Relationships Between Tables in the Data Model

在数据模型中有以下表 −

You have the following tables in the data model −

Data table - Finance Data
Lookup tables - Accounts and Geography Locn
Date table - Date

若要在数据模型中的表格之间定义关系，请执行以下步骤：

To define Relationships between the tables in the data model, following are the steps −

View the tables in the Diagram View of the Power Pivot.
Create the following relationships between the tables − Relationship between Finance Data table and Accounts table with the column Account. Relationship between Finance Data table and Geography Locn table with the column Profit Center. Relationship between Finance Data table and Date table with the column Date.

Hiding Columns from Client Tools

若数据表格中包含任何不作为任何数据透视表的字段的列，则可将它们隐藏在数据模型中。然后，它们将不会在“数据透视表字段”列表中显示。

If there are any columns in a data table that you won’t be using as fields in any PivotTable, you can hide them in the data model. Then, they will not be visible in the PivotTable Fields list.

在“财务数据”表格中，有 4 个列（财政月份、日期、帐户和利润中心），这些列将不会用作任何数据透视表的字段。因此，可隐藏它们，以便它们不会显示在“数据透视表字段”列表中。

In the Finance Data table, you have 4 columns – Fiscal Month, Date, Account and Profit Center that you won’t be using as fields in any PivotTable. Hence, you can hide them so that they do not appear in the PivotTable Fields list.

Select the columns - Fiscal Month, Date, Account, and Profit Center in the Finance Data table.
Right-click and select Hide from Client Tools in the dropdown list.

Creating Measures in the Tables

你现在已经准备好使用数据模型和 Power 数据透视表来进行数据建模和 DAX 分析。

You are all set for data modeling and analysis with DAX using the data model and Power PivotTables.

在后续章节中，你将学习如何创建度量，以及如何在 Power 数据透视表中使用它们。你将在数据表格（即财务数据表格）中创建所有度量。

In the subsequent chapters, you will learn how to create measures and how to use them in Power PivotTables. You will create all the measures in the data table, i.e. Finance Data table.

你将在数据表（财务数据）中使用 DAX 公式创建度量，你可将其用于任意数量的数据透视表，以进行数据分析。度量本质上是元数据。在数据表中创建度量是数据建模的一部分，而在 Power 数据透视表中对它们进行汇总是数据分析的一部分。

You will create measures using DAX formulas in the data table – Finance Data, which you can use in any number of PivotTables for the data analysis. The measures are essentially the metadata. Creating measures in the data table is part of data modeling and summarizing them in the Power PivotTables is part of data analysis.

Base Finance Measures and Analysis

你可以在数据模型中创建各种度量，用于任意数量的 Power 透视表中。这构成了 DAX 使用数据模型的数据建模和分析过程。

You can create various measures in the data model to be used in any number of Power PivotTables. This forms the data modeling and analysis process with the data model using DAX.

正如你在前几小节中了解到的，数据建模和分析取决于特定的业务和上下文。在本小节中，你将根据利润和亏损示例数据库学习数据建模和分析，以便了解如何创建所需的度量，并在各种 Power 透视表中使用它们。

As you have learnt earlier in the previous sections, data modeling and analysis is dependent on specific business and context. In this chapter, you will learn data modeling and analysis based on a sample Profit and Loss database to understand how to create the required measures and use them in various Power PivotTables.

你可以针对任何业务和上下文使用相同的方法进行数据建模和分析

You can apply the same method for data modeling and analysis for any business and context

Creating Measures Based on Finance Data

要创建任何财务报表，你需要针对特定时间段、组织、帐户或地理位置计算金额。你还要执行人员计数和每人成本计算。在数据模型中，你可以创建基准衡量，以便在创建其他基准衡量时重复使用。这是使用 DAX 进行数据建模的有效方法。

To create any financial report, you need to make calculations of amounts for a particular time period, organization, account, or geographical location. You also need to perform the headcount and cost per headcount calculations. In the data model, you can create base measures that can be reused in creating other measures. This is an effective way of data modeling with DAX.

为了执行利润和亏损数据分析计算，你可以创建总和、同比、年初至今、季初至今、差异、人员数量、每人成本等基准衡量。你可以在 Power 透视表中使用这些基准衡量来分析数据并报告分析结果。

In order to perform calculations for profit and loss data analysis, you can create measures such as sum, year-over-year, year-to-date, quarter-to-date, variance, headcount, cost per headcount, etc. You can use these measures in the Power PivotTables to analyze the data and report the analysis results.

在以下小节中，你将学习如何创建基础财务衡量并使用这些衡量分析数据。这些衡量被称为基础衡量，因为它们可用于创建其他财务衡量。你还会学习如何创建前一时间段的基准衡量并在分析中使用它们。

In the following sections, you will learn how to create the base finance measures and analyze data with those measures. The measures are termed as base measures as they can be used in creating other financial measures. You will also learn how to create measures for the previous time periods and use them in the analysis.

Creating Base Finance Measures

在财务数据分析中，预算和预测起着重要作用。

In the finance data analysis, budget and forecast play a major role.

Budget

预算是对企业经济年度收益和支出的估算值。企业在经济年度初制定预算，还需要参照企业目标设定。需要在经济年度期间时不时分析预算指标，因为市场状况可能发生变化，企业可能需要调整其目标以适应行业中的当前趋势。

A budget is an estimate of a company’s revenues and expenses for a financial year. The budget is calculated at the beginning of a financial year keeping in view the company’s goals and targets. Budget measures need to be analyzed from time to time during the financial year, as the market conditions may change and the company may have to align its goals and targets to the current trends in the industry.

Forecast

财务预测是通过检查企业过去收益和支出的历史数据，估算企业未来的财务状况。你可以将财务预测用于以下方面：

A financial forecast is an estimate of a company’s future financial outcomes by examining the company’s historical data of revenues and expenses. You can use financial forecasting for the following −

To determine how to allocate budget for a future period.
To track the expected performance of the company.
To take timely decisions to address shortfalls against the targets, or to maximize an emerging opportunity.

Actuals

若要执行预算和预测计算，你需要任何时间点的实际收益和支出。

To perform the budgeting and forecasting calculations, you require the actual revenue and expenses at any point in time.

你可以创建以下 3 项财务基础指标，可以在数据模式中用其创建其他财务指标：

You can create the following 3 base finance measures that can be used in creating other financial measures in the data mode −

Budget Sum
Actual Sum
Forecast Sum

这些指标是在财务数据表中针对“预算”、“实际”和“预测”列的聚合总和。

These measures are the aggregation sums over the columns – Budget, Actual, and Forecast in the Finance Data table.

按如下方式创建财务基础指标：

Create the base finance measures as follows −

Budget Sum

Budget Sum:=SUM('Finance Data'[Budget])

Actual Sum

Actual Sum:=SUM('Finance Data'[Actual])

Forecast Sum

Forecast Sum:=SUM('Finance Data'[Forecast])

Analyzing Data with Base Finance Measures

借助财务基础指标和日期表，你可以按如下方式执行分析：

With the base finance measures and the Date table, you can perform your analysis as follow −

Create a Power PivotTable.
Add the field Fiscal Year from the Date table to Rows.
Add the measures Budget Sum, Actual Sum, and Forecast Sum (that appear as fields in the PivotTable Fields list) to Values.

Creating Finance Measures for Previous Periods

借助财务基础指标和日期表，你可以创建其他财务指标。

With the three base finance measures and the Date table, you can create other finance measures.

假设您想要将一个季度实际总额与上一个季度实际总额进行比较。您可以创建度量值 - 上一季度实际总额。

Suppose you want to compare the Actual Sum of a Quarter with the Actual Sum of previous Quarter. You can create the measure - Prior Quarter Actual Sum.

上一季度实际总额：= CALCULATE（[实际总额]，DATEADD（'Date'[日期]，1，季度））

Prior Quarter Actual Sum:=CALCULATE([Actual Sum], DATEADD('Date'[Date],1,QUARTER))

同样，您可以创建度量值 - 上一年的实际总额。

Similarly, you can create the measure - Prior Year Actual Sum.

上一年的实际总额：= CALCULATE（[实际总额]，DATEADD（'Date'[日期]，1，年份））

Prior Year Actual Sum:=CALCULATE([Actual Sum], DATEADD('Date'[Date],1,YEAR))

Analyzing Data with Finance Measures for Previous Periods

使用基本度量值，前几个周期的度量值和日期表，您可以按如下方式执行分析 -

With the base measures, measures for previous periods and the Date table, you can perform your analysis as follows −

Create a Power PivotTable.
Add the field Fiscal Quarter from the Date table to Rows.
Add the measures Actual Sum and Prior Quarter Actual Sum to Values.
Create another Power PivotTable.
Add the field Fiscal Year from the Date table to Rows.
Add the measures Actual Sum and Prior Year Actual Sum to Values.

YoY Finance Measures and Analysis

同比（YoY）是度量增长。它通过从实际总额中减去前一年的实际总额来获得。

Year-over-Year (YoY) is a measure of growth. It is obtained by subtracting the actual sum of the previous year from the actual sum.

如果结果为正，则表示实际金额增加，如果结果为负，则表示实际金额减少，即如果我们将同比计算为：

If the result is positive, it reflects an increase in actual, and if it is negative, it reflects a decrease in actual, i.e. if we calculate year-over-year as −

year-over-year = (actual sum –prior year actual sum)

If the actual sum > the prior year actual sum, year-over-year will be positive.
If the actual sum < the prior year actual sum, year-over-year will be negative.

在财务数据中，诸如费用帐户之类的帐户将具有借方（正）金额，而收入帐户将具有贷方（负）金额。因此，对于费用帐户，上述公式完全适用。

In the financial data, accounts such as the expense accounts will have debit (positive) amounts and the revenue accounts will have credit (negative) amounts. Hence, for the expense accounts, the above formula works fine.

但是，对于收入帐户，应相反，即：

However, for the revenue accounts, it should be the reverse, i.e.

If the actual sum > the prior year actual sum, year-over-year should be negative.
If the actual sum < the prior year actual sum, year-over-year should be positive.

因此，对于收入账户，您需要以以下方式计算同比：

Hence for the revenue accounts, you have to calculate year-over-year as −

year-over-year = -(actual sum – prior year actual sum)

Creating Year-over-Year Measure

您可以使用以下 DAX 公式创建同比度量：

You can create Year-over-Year measure with the following DAX formula −

YoY:=IF(CONTAINS(Accounts,Accounts[Class], "Net Revenue"), - ([Actual Sum] - [Prior Year Actual Sum]), [Actual Sum] - [Prior Year Actual Sum])

YoY:=IF(CONTAINS(Accounts, Accounts[Class],"Net Revenue"),-([Actual Sum]-[Prior Year Actual Sum]), [Actual Sum]-[Prior Year Actual Sum])

在上述 DAX 公式中：

In the above DAX formula −

DAX CONTAINS function returns TRUE, if a row has "Net Revenue" in the column Class in the Accounts table.
DAX IF function then returns –([Actual Sum]-[ Prior Year Actual Sum]).
Otherwise, DAX IF function returns [Actual Sum]-[ Prior Year Actual Sum].

Creating Year-over-Year Percentage Measure

您可以使用比率将同比表示为百分比：

You can represent Year-over-Year as a percentage with the ratio −

(YoY) / (Prior Year Actual Sum)

您可以使用以下 DAX 公式创建同比百分比度量：

You can create the Year-over-Year Percentage measure with the following DAX formula −

YoY %:=IF([Prior Year Actual Sum], [YoY] / ABS([Prior Year Actual Sum]),BLANK())

上述公式中使用了 DAX IF 函数以确保不进行零除。

DAX IF function is used in the above formula to ensure that there is no division by zero.

Analyzing Data with Year-over-Year Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add the fields Class and Sub Class from the Accounts table to Rows.
Add the measures – Actual Sum, Prior Year Actual Sum, YoY and YoY % to Values.
Insert a Slicer on the field Fiscal Year from the Date table.
Select FY2016 in the Slicer.

Creating Budget Year-over-Year Measure

您可以按以下方式创建预算同比度量：

You can create Budget Year-over-Year measure as follows −

预算同比：= IF(CONTAINS(Accounts,Accounts[Class],"Net Revenue"), - ([Budget Sum] - [Prior Year Actual Sum]), [Budget Sum] - [Prior Year Actual Sum])

Budget YoY: = IF(CONTAINS(Accounts,Accounts[Class],"Net Revenue"), - ([Budget Sum] - [Prior Year Actual Sum]), [Budget Sum] - [Prior Year Actual Sum])

Creating Budget Year-over-Year Percentage Measure

您可以按照以下方式创建预算同比百分比指标：

You can create Budget Year-over-Year Percentage measure as follows −

预算同比百分比:=IF([上年实际总计],[预算同比]/ABS([上年实际总计]),BLANK())

Budget YoY %:=IF([Prior Year Actual Sum] , [Budget YoY] / ABS ([Prior Year Actual Sum]) , BLANK())

Analyzing Data with Budget Year-over-Year Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add the fields Class and Sub Class from the Accounts table to Rows.
Add the measures – Budget Sum, Prior Year Actual Sum, Budget YoY and Budget YoY % to Values.
Insert a Slicer on the field Fiscal Year from the Date table.
Select FY2016 in the Slicer.

Creating Forecast Year-over-Year Measure

您可以按照以下方式创建预测同比指标：

You can create Forecast Year-over-Year measure as follows −

预测同比:=IF(CONTAINS(科目,科目[分类],“净利润”), - ([预测总计] - [上年实际总计]),[预测总计] - [上年实际总计])

Forecast YoY:=IF(CONTAINS(Accounts,Accounts[Class],"Net Revenue"), - ([Forecast Sum] - [Prior Year Actual Sum]), [Forecast Sum] - [Prior Year Actual Sum])

Creating Forecast Year-over-Year Percentage Measure

您可以按照以下方式创建预测同比百分比指标：

You can create Forecast Year-over-Year Percentage measure as follows −

预测同比百分比:=IF([上年实际总计],[预测同比]/ABS([上年实际总计]),BLANK())

Forecast YoY %:=IF([Prior Year Actual Sum],[Forecast YoY]/ABS([Prior Year Actual Sum]),BLANK())

Analyzing Data with Forecast Year-over-Year Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add the fields Class and Sub Class from the Accounts table to Rows.
Add the measures – Forecast Sum, Prior Year Actual Sum, Forecast YoY and Forecast YoY % to Values.
Insert a Slicer on the field Fiscal Year from the Data table.
Select FY2016 in the Slicer.

Variance Measures and Analysis

您可以创建诸如预算差异、预测差异和变动预算之差异之类的差异度量。您还可以根据这些度量分析财务数据。

You can create variance measures such as variance to budget, variance to forecast, and forecast variance to budget. You can also analyze the finance data based on these measures.

Creating Variance to Budget Sum Measure

按如下方式创建总预算差异度量（VTB 总和）：

Create Variance to Budget Sum measure (VTB Sum) as follows −

VTB 总和：[预算总和]-[实际总和]

VTB Sum:=[Budget Sum]-[Actual Sum]

Creating Variance to Budget Percentage Measure

按如下方式创建预算差异百分比度量（VTB %）：

Create Variance to Budget Percentage measure (VTB %) as follows −

VTB %：=IF([预算总和]，[VTB 总和]/ABS([预算总和])，BLANK())

VTB %:=IF([Budget Sum],[VTB Sum]/ABS([Budget Sum]),BLANK())

Analyzing Data with Variance to Budget Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add Fiscal Year from the Date table to Rows.
Add the measures Actual Sum, Budget Sum, VTB Sum, VTB % from the Finance Data table to Values.

Creating Variance to Forecast Sum Measure

创建“预测差异总和 (VTF 总和)”度量，如下所示：

Create Variance to Forecast Sum (VTF Sum) measure as follows −

VTF 总和：=[预测总和]-[实际金额总和]

VTF Sum:=[Forecast Sum]-[Actual Sum]

Creating Variance to Forecast Percentage Measure

创建“预测差异百分比 (VTF %)”度量，如下所示：

Create Variance to Forecast Percentage measure (VTF %) as follows −

VTF %：=IF([预测总和], [VTF 总和]/ABS([预测总和]), BLANK())

VTF %:=IF([Forecast Sum],[VTF Sum]/ABS([Forecast Sum]),BLANK())

Analyzing Data with Variance to Forecast Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add Fiscal Year from the Date table to Rows.
Add the measures Actual Sum, Forecast Sum, VTF Sum, VTF % from the Finance Data table to Values.

Creating Forecast Variance to Budget Sum Measure

创建“预测差异与预算总和 (预测 VTB 总和)”度量，如下所示：

Create Forecast Variance to Budget Sum (Forecast VTB Sum) measure as follows −

预测 VTB 总和：=[预算金额总和]-[预测总和]

Forecast VTB Sum:=[Budget Sum]-[Forecast Sum]

Creating Forecast Variance to Budget Percentage Measure

创建“预测差异与预算百分比 (预测 VTB 百分比)”度量，如下所示：

Create Forecast Variance to Budget Percentage (Forecast VTB Percentage) measure as follows −

预测 VTB %：=IF([预算金额总和], [预测 VTB 总和]/ABS([预算金额总和]), BLANK())

Forecast VTB %:=IF([Budget Sum],[Forecast VTB Sum]/ABS([Budget Sum]),BLANK())

Analyzing Data with Forecast Variance to Budget Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add Fiscal Year from the Date table to Rows.
Add the measures Budget Sum, Forecast Sum, Forecast VTB Sum, Forecast VTB % from the Finance Data table to Values.

Year-to-Date Measures and Analysis

若要计算从一个时期（例如一个财政年度）开始到一个特定时间为止的包含期初余额的结果，可使用 DAX 时间智能函数。这将使您能够分析月度数据。

To calculate a result that includes a starting balance from the beginning of a period, such as a fiscal year, up to a specific period in time, you can use DAX Time Intelligence functions. This will enable you to analyze data on a month level.

在本章中，您将学习如何创建“年初至今”度量，并使用它执行数据分析。

In this chapter, you will learn how to create Year-to-Date measures and how to carry out data analysis with the same.

Creating Year-to-Date Actual Sum Measure

按以下方式创建“年初至今实际总和”度量：

Create Year-to-Date Actual Sum measure as follows −

YTD 实际总和：= TOTALYTD（[实际总和]，'日期'[日期]，ALL('日期')，"6/30"）

YTD Actual Sum:=TOTALYTD([Actual Sum], 'Date'[Date], ALL('Date'), "6/30")

Creating Year-to-Date Budget Sum Measure

按以下方式创建“年初至今预算总和”度量：

Create Year-to-Date Budget Sum measure as follows −

YTD 预算总和：= TOTALYTD（[预算总和]，'日期'[日期]，ALL('日期')，"6/30"）

YTD Budget Sum:=TOTALYTD([Budget Sum], 'Date'[Date], ALL('Date'), "6/30")

Creating Year-to-Date Forecast Sum Measure

按以下方式创建“年初至今预测总和”度量：

Create Year-to-Date Forecast Sum measure as follows −

YTD 预测总和：= TOTALYTD（[预测总和]，'日期'[日期]，ALL('日期')，"6/30"）

YTD Forecast Sum:=TOTALYTD([Forecast Sum], 'Date'[Date], ALL('Date'), "6/30")

Creating Prior Year-to-Date Actual Sum Measure

按以下方式创建“上一年年初至今实际总和”度量：

Create Prior Year-to-Date Actual Sum measure as follows −

去年年初至今实际总和：= TOTALYTD（[去年实际总和]，'日期'[日期]，ALL('日期')，"6/30"）

Prior YTD Actual Sum:=TOTALYTD([Prior Year Actual Sum], 'Date'[Date], ALL('Date'), "6/30")

Analyzing Data with Year-to-Date Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add Month from Date table to Rows.
Add the measures Actual Sum, YTD Actual Sum, YTD Budget Sum, and YTD Forecast Sum from the Finance Data table to Values.
Insert a Slicer on the Fiscal Year from the Date table.
Select FY2016 in the Slicer.

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add Month from Date table to Rows.
Add the measures Actual Sum, YTD Actual Sum, Prior Year Actual Sum, and Prior Year YTD Actual Sum from the Finance Data table to Values.
Insert a Slicer on the Fiscal Year from the Date table.
Select FY2016 in the Slicer.

Quarter-to-Date Measures and Analysis

若要计算包括一段时间的起始余额（例如会计季度）直到特定时间的某个结果，您可以使用 DAX 时间智能函数。这使您能够分析按月汇总的数据。

To calculate a result that includes a starting balance from the beginning of a period, such as a fiscal quarter, up to a specific period in time, you can use DAX Time Intelligence functions. This will enable you to analyze data on a month level.

在本章中，您将学习如何创建年初至今度量以及如何使用相同的度量执行数据分析。

In this chapter, you will learn how to create Quarter-to-Date measures and how to carry out data analysis with the same.

Creating Quarter-to-Date Sum Measure

按照以下步骤创建年初至今实际总和度量：

Create Quarter-to-Date Actual Sum measure as follows −

年初至今实际总和：=TOTALQTD([实际总和，'日期'[日期]，ALL('日期')])

QTD Actual Sum:=TOTALQTD([Actual Sum],'Date'[Date],ALL('Date'))

Creating Quarter-to-Date Budget Sum Measure

按照以下步骤创建年初至今预算总和度量：

Create Quarter-to-Date Budget Sum measure as follows −

年初至今预算总和：=TOTALQTD([预算总和]，'日期'[日期]，ALL('日期')])

QTD Budget Sum:=TOTALQTD([Budget Sum], 'Date'[Date], ALL('Date'))

Creating Quarter-to-Date Forecast Sum Measure

按照以下步骤创建年初至今预算总和度量：

Create Quarter-to-Date Budget Sum measure as follows −

年初至今预算总和：=TOTALQTD([预算总和]，'日期'[日期]，ALL('日期')])

QTD Budget Sum:=TOTALQTD([Budget Sum], 'Date'[Date], ALL('Date'))

Creating Quarter-to-Date Forecast Sum Measure

按照以下步骤创建年初至今预测总和度量：

Create Quarter-to-Date Forecast Sum measure as follows −

年初至今预测总和：=TOTALQTD([预测总和]，'日期'[日期]，ALL('日期')])

QTD Forecast Sum:=TOTALQTD([Forecast Sum], 'Date'[Date], ALL('Date'))

Creating Prior Quarter-to-Date Actual Sum Measure

按照以下步骤创建前年初至今实际总和度量：

Create Prior Quarter-to-Date Actual Sum measure as follows −

前年初至今实际总和：=TOTALQTD([上前一个季度实际总和]，'日期'[日期]，ALL('日期')])

Prior QTD Actual Sum:=TOTALQTD([Prior Quarter Actual Sum], 'Date'[Date], ALL('Date'))

Analyzing Data with Quarter-to-Date Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add Fiscal Month from Date table to Rows.
Add the measures Actual Sum, QTD Actual Sum, QTD Budget Sum, and QTD Forecast Sum from Finance Data table to Values.
Insert a Slicer on the Fiscal Quarter from the Date table.
Select FY2016-Q2 in the Slicer.

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add Fiscal Month from Date table to Rows.
Add the measures Actual Sum, QTD Actual Sum, Prior Quarter Actual Sum, and Prior QTD Actual Sum from Finance Data table to Values.
Insert a Slicer on the Fiscal Quarter from Date table.
Select FY2016-Q1 in the Slicer.

Budget Measures and Analysis

预算涉及估算公司的现金流在某一财务年度的变化情况。公司的财务状况、其目标、预期收入和支出都会计算在预算中。

Budgeting involves estimating the cash flows of a company over a financial year. The financial position of the company, its goals, expected revenues, and expenses are taken into account in budgeting.

但是，市场状况可能会在财务年度发生变化，公司可能必须重新设定其目标。这就需要分析财务数据与在财务年度开始时估算的预算（预算总和）和从财务年度开始至今的实际支出总和（YTD 实际总和）。

However, the market conditions may change during the financial year and the company may have to reset its goals. This requires analyzing the financial data with the budget estimated at the beginning of the financial year (Budget Sum) and the actual expended sum from the beginning of the financial year to date (YTD Actual Sum).

在财政年度的任何时间，都可以计算出以下内容 -

At any time during a financial year, you can calculate the following −

Unexpended Balance

未支出余额是在实际支出之后的剩余预算，即

Unexpended Balance is the budget remaining after the actual expenses, i.e.

Unexpended Balance = YTD Budget Sum – YTD Actual Sum

Budget Attainment %

预算达成率百分比是您至今为止已支出预算的百分比，即

Budget Attainment % is the percentage of the budget that you have spent to date, i.e.

Budget Attainment % = YTD Actual Sum/YTD Budget Sum

这些计算可帮助那些使用预算的公司做出决策。

These calculations help those companies that use budgeting to make decisions.

Creating Unexpended Balance Measure

您可以按如下方式创建未支出余额计量：

You can create Unexpended Balance measure as follows −

未支出余额：=CALCULATE( [YTD 实际总和]，ALL('财务数据'[日期]) )-[YTD 实际总和]

Unexpended Balance:=CALCULATE( [YTD Budget Sum],ALL('Finance Data'[Date]) )-[YTD Actual Sum]

Creating Budget Attainment Percentage Measure

您可以按如下方式创建预算达成率百分比计量：

You can create Budget Attainment Percentage measure as follows −

预算达成率百分比：=IF([YTD 实际总和]，[YTD 实际总和]/CALCULATE([YTD 实际总和]，ALL('财务数据'[日期])），空白()）

Budget Attainment %:=IF([YTD Budget Sum],[YTD Actual Sum]/CALCULATE([YTD Budget Sum],ALL('Finance Data'[Date])),BLANK())

Analyzing Data with Budget Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add Month from the Date table to Rows.
Add the measures Budget Sum, YTD Budget Sum, YTD Actual Sum, Budget Attainment % and Unexpended Balance from Finance Data table to Values.
Insert a Slicer on the Fiscal Year field.
Select FY2016 in the Slicer.

Forecast Measures and Analysis

您可以使用预测度量来分析财务数据，并帮助组织对其年度目标进行必要的调整，以使公司的业绩符合不断变化的业务需求。

You can use Forecast measures to analyze the finance data and help an organization make necessary adjustments in its goals and targets for the year, to align the company’s performance to the changing business requirements.

您需要定期更新预测以跟上变化。然后可以将最新的预测与本财政年度剩余期间的预算进行比较，以便公司可以根据业务变化进行必要的调整。

You need to update the forecasts regularly to keep up with the changes. You can then compare the most recent forecast to the budget for the rest of the period in the financial year so that the company can make the required adjustments to meet the business changes.

在财政年度的任何时间，都可以计算出以下内容 -

At any time during a financial year, you can calculate the following −

Forecast Attainment %

预测达到率 % 是您迄今为止花费的预测总和的百分比，即

Forecast Attainment % is the percentage of the forecast sum that you have spent to date, i.e.

Forecast Attainment % = YTD Actual Sum/YTD Forecast Sum

Forecast Unexpended Balance

预测未支余额是实际支出后的预测总和，即

Forecast Unexpended Balance is the Forecast Sum remaining after the actual expenses, i.e

Forecast Unexpended Balance = YTD Forecast Sum – YTD Actual Sum

Budget Adjustment

预算调整是组织根据预测需要进行的预算总和调整（增加或减少）。

Budget Adjustment is the adjustment in the budget sum an organization needs to make (an increase or decrease) based on the forecast.

Budget Adjustment = Forecast Unexpended Balance - Unexpended Balance

如果所得的值为正，则需要增加预算。否则，可以针对其他目的进行调整。

The budget needs to be increased if the resulting value is positive. Otherwise, it can be adjusted for some other purpose.

Creating Forecast Attainment Percentage Measure

您可以按照以下方式创建预测达到率度量：

You can create Forecast Attainment Percentage measure as follows −

预测达到率：=IF([YTD 预算总和]，[YTD 实际总和]/[YTD 预算总和]，BLANK())

Forecast Attainment Percentage:= IF([YTD Forecast Sum], [YTD Actual Sum]/[YTD Forecast Sum], BLANK())

Creating Forecast Unexpended Balance Measure

您可以按照以下方式创建预测未支余额度量：

You can create Forecast Unexpended Balance measure as follows −

预测未支余额：=[YTD 预算总和]-[YTD 实际总和]

Forecast Unexpended Balance:=[YTD Forecast Sum]-[YTD Actual Sum]

Creating Budget Adjustment Measure

您可以按以下步骤创建预算调整度量值 −

You can create Budget Adjustment measure as follows −

预算调整：=[预测未支出余额]-[未支出余额]

Budget Adjustment:=[Forecast Unexpended Balance]-[Unexpended Balance]

Analyzing Data with Forecast Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add Month from Date table to Rows.
Add the measures Budget Sum, YTD Budget Sum, YTD Actual Sum, Budget Attainment % and Unexpended Balance from Finance Data table to Values.
Insert a Slicer on Fiscal Year.
Select FY2016 in the Slicer.

Count of Months Measures

您可以创建可用于创建人数度量和人均成本度量的月数计数度量。这些度量计算财务数据表中实际列/预算列/预测列具有非零值的财政月列的不同值的计数。这是必需的，因为财务数据表中实际列包含零值，在计算人数和人均成本时应排除这些行。

You can create the Count of Months measures that can be used in creating Headcount measures and Cost Per Head measures. These measures count the distinct values of Fiscal Month column where the Actual column / Budget column / Forecast column has non-zero values in the Finance Data table. This is required because the Finance Data table contains zero values in the Actual column and those rows are to be excluded while calculating Headcount and Cost per Head.

Creating Count of Actual Months Measure

您可以按如下方式创建实际月数计数度量 -

You can create Count of Actual Months measure as follows −

实际月数计数 = CALCULATE(DISTINCTCOUNT('FinanceData' [财政月]),'Finance Data'[实际] <> 0)

CountOfActualMonths:=CALCULATE(DISTINCTCOUNT('FinanceData' [Fiscal Month]),'Finance Data'[Actual]<>0)

Creating Count of Budget Months Measure

您可以按如下方式创建预算月数计数度量 -

You can create Count of Budget Months measure as follows −

预算月数计数 = CALCULATE(DISTINCTCOUNT('FinanceData' [财政月]),'Finance Data'[预算] <> 0)

CountOfBudgetMonths:=CALCULATE(DISTINCTCOUNT('FinanceData' [Fiscal Month]),'Finance Data'[Budget]<>0)

Creating Count of Forecast Months Measure

您可以按如下方式创建预测月数计数度量 -

You can create Count of Forecast Months measure as follows −

预测月数计数 = CALCULATE(DISTINCTCOUNT('FinanceData' [财政月]),'Finance Data'[预测] <> 0)

CountOfForecastMonths:=CALCULATE(DISTINCTCOUNT('FinanceData' [Fiscal Month]),'Finance Data'[Forecast]<>0)

Ending Headcount Measures

可以为特定时间段创建结束人数措施。结束人数是指定期间的最后日期的人数总和，对于该日期，我们具有非零人数总和。

You can create Ending Headcount measures for a specific period of time. The Ending Headcount is the sum of the people as on the last date in the specified period for which we have a non-blank sum of people.

结束人数获取方式如下 −

The Ending Headcount is obtained as follows −

For a Month − Sum of People at the end of the specific Month.
For a Quarter − Sum of People at the end of the last Month of the specific Quarter.
For a Year − Sum of People at the end of the last Month of the specific Year.

Creating Actual Ending Headcount Measure

您可以按如下方法创建实际期末人数度量：

You can create Actual Ending Headcount measure as follows −

实际期末人数：=CALCULATE(SUM('Finance Data'[实际人数])，LASTNONBLANK('Finance Data'[日期]，IF(CALCULATE(SUM('Finance Data'[实际人数])，ALL(帐户))=0，BLANK()，CALCULATE(SUM('Finance Data'[实际人数])，ALL(帐户))))，ALL(帐户))

Actual Ending Head Count:=CALCULATE(SUM('Finance Data'[Actual People]),LASTNONBLANK('Finance Data'[Date], IF(CALCULATE(SUM('Finance Data'[Actual People]), ALL(Accounts))=0, BLANK(), CALCULATE(SUM('Finance Data'[Actual People]), ALL(Accounts)))), ALL(Accounts))

DAX LASTNONBLANK 函数按上述方式使用时返回您具有非空白人数总和的最后日期，以便您可以计算该日期的人数总和。

DAX LASTNONBLANK function as used above returns the last date for which you have a non-blank sum of people so that you can calculate the sum of people on that date.

Creating Budget Ending Headcount Measure

您可以按如下方法创建预算末期人数度量：

You can create Budget Ending Headcount measure as follows −

预算末期人数：= CALCULATE(SUM('Finance Data'[预算人数])，LASTNONBLANK('Finance Data'[日期]，IF(CALCULATE(SUM('Finance Data'[预算人数])，ALL(帐户))=0，BLANK()，CALCULATE(SUM('Finance Data'[预算人数])，ALL(帐户))))，ALL(帐户))

Budget Ending Head Count: = CALCULATE(SUM('Finance Data'[Budget People]),LASTNONBLANK('Finance Data'[Date], IF(CALCULATE(SUM('Finance Data'[Budget People]), ALL(Accounts))=0, BLANK(), CALCULATE(SUM('Finance Data'[Budget People]), ALL(Accounts)))), ALL(Accounts))

Creating Forecast Ending Headcount Measure

您可以按如下方法创建预测期末人数度量：

You can create Forecast Ending Headcount measure as follows −

预测期末人数：= CALCULATE(SUM('Finance Data'[预测人数])，LASTNONBLANK('Finance Data'[日期]，IF(CALCULATE(SUM('Finance Data'[预测人数])，ALL(帐户))=0，BLANK()，CALCULATE(SUM('Finance Data'[预测人数])，ALL(帐户))))，ALL(帐户))

Forecast Ending Head Count:= CALCULATE(SUM('Finance Data'[Forecast People]), LASTNONBLANK('Finance Data'[Date], IF(CALCULATE(SUM('Finance Data'[Forecast People]), ALL(Accounts))=0, BLANK(),CALCULATE(SUM('Finance Data'[Forecast People]), ALL(Accounts)))), ALL(Accounts))

Creating Prior Year Actual Ending Headcount Measuree

您可以按如下方法创建去年实际期末人数度量：

You can create Prior Year Actual Ending Headcount measure as follows −

去年实际期末人数：=CALCULATE('Finance Data'[实际期末人数]，DATEADD('Date'[日期]，-1，YEAR))

Prior Year Actual Ending Headcount:=CALCULATE('Finance Data'[Actual Ending Head Count], DATEADD('Date'[Date],-1,YEAR))

Analyzing Data with Ending Headcount Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add the fields Fiscal Year and Month from the Date table to Rows.
Add the measures Actual Ending Headcount, Budget Ending Headcount, Forecast Ending Headcount, Prior Year Actual Ending Headcount from Finance Data table to Values.
Insert a Slicer on the Fiscal Year field.
Select FY2016 in the Slicer.

Average Headcount Measures

在上一章节，您已学习如何计算特定期间的期末人数。同样地，您可以为任何给定月份的选择创建平均每月人数。

In the previous chapter, you have learnt how to calculate ending headcounts for a specific period. Likewise, you can create the average monthly headcount for any given selection of months.

平均每月人数是各个月人数的总和除以选择中的月份数。

The Average Monthly Headcount is the sum of the monthly headcounts divided by the number of months in the selection.

您可以使用 DAX AVERAGEX 函数创建这些计量。

You can create these measures using DAX AVERAGEX function.

Creating Actual Average Headcount Measure

您可以按如下方式创建实际平均人数计量：

You can create Actual Average Headcount measure as follows −

实际平均人员编制：=AVERAGEX(VALUES('Finance Data'[Fiscal Month]), [Actual Ending Head Count])

Actual Average Headcount:=AVERAGEX(VALUES('Finance Data'[Fiscal Month]), [Actual Ending Head Count])

Creating Budget Average Headcount Measure

您可以按如下方式创建实际平均人数计量：

You can create Actual Average Headcount measure as follows −

预算平均人员编制：=AVERAGEX(VALUES('Finance Data'[Fiscal Month]), [Budget Ending Head Count])

Budget Average Headcount:=AVERAGEX(VALUES('Finance Data'[Fiscal Month]), [Budget Ending Head Count])

Creating Forecast Average Headcount Measure

可以按如下创建预测平均人员编制度量值：

You can create Forecast Average Headcount measure as follows −

预测平均人员编制：=AVERAGEX(VALUES('Finance Data'[Fiscal Month]), [Actual Ending Head Count])

Forecast Average Headcount:=AVERAGEX( VALUES('Finance Data'[Fiscal Month]), [Actual Ending Head Count])

Creating Prior Year Actual Average Headcount Measure

可以按如下创建前期实际平均人员编制度量值：

You can create Prior Year Actual Average Headcount measure as follows −

前期实际平均人员编制：=CALCULATE('Finance Data'[Actual Average Headcount], DATEADD('Date'[Date], -1, YEAR))

Prior Year Actual Average Headcount:=CALCULATE('Finance Data'[Actual Average Headcount], DATEADD('Date'[Date], -1, YEAR))

Analyzing Data with Average Headcount Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add the fields Fiscal Year and Month from the Date table to Rows.
Add the measures Actual Average Headcount, Budget Average Headcount, Forecast Average Headcount, Prior Year Actual Average Headcount from Finance Data table to Values.
Insert a Slicer on the Fiscal Year field.
Select FY2016 in the Slicer.

Total Headcount Measures

在前面的章节中，您已学习如何创建月计数度量和平均人数度量。可以使用这些度量计算基础人数度量 -

In the previous chapters, you have learnt how to create Count of Months measures and Average Headcount measures. You can use these measures to calculate the base Headcount Measures −

Actual Total Headcount
Budget Total Headcount
Forecast Total Headcount

在随后的章节中，您将学习如何在其他计算（如同比人数和差异度量）中使用这些基础人数度量。

In the subsequent chapters, you will learn how to use these base Headcount measures in other calculations such as YoY Headcount and Variance measures.

Creating Actual Total Headcount Measure

您可以按如下方式创建实际总数度量 -

You can create Actual Total Headcount Measure as follows −

实际总数 = '财务数据'[实际平均人数]*'财务数据'[实际月数计数]

Actual Total Head Count:= 'Finance Data'[Actual Average Headcount]*'Finance Data'[CountOfActualMonths]

Creating Budget Total Headcount Measure

您可以按如下方式创建预算总数度量 -

You can create Budget Total Headcount Measure as follows −

预算总数 = '财务数据'[预算平均人数]*'财务数据'[预算月数计数]

Budget Total Headcount:= 'Finance Data'[Budget Average Headcount]*'Finance Data'[CountOfBudgetMonths]

Creating Forecast Total Headcount Measure

您可以按如下方式创建预测总数度量 -

You can create Forecast Total Headcount Measure as follows −

预测总数 = '财务数据'[预测平均人数]*'财务数据'[预测月数计数]

Forecast Total Headcount:= 'Finance Data'[Forecast Average Headcount]*'Finance Data'[CountOfForecastMonths]

YoY Headcount Measures and Analysis

在上一章中，您学习了如何创建基本员工人数度量值 – 即实际总员工人数、预算总员工人数和预测总员工人数。

In the previous chapter, you have learnt how to create base Headcount measures – i.e. Actual Total Headcount, Budget Total Headcount, and Forecast Total Headcount.

在本章中，您将学习如何创建同比员工人数度量值以及如何使用这些度量值分析数据。

In this chapter, you will learn how to create Year-Over-Year Headcount measures and how you can analyze the data with these measures.

Creating Year-over-Year Actual Ending Headcount Measure

您可以按如下方式创建同比实际期末员工人数度量值 -

You can create Year-over-Year Actual Ending Headcount Measure as follows −

同比实际期末员工人数：=[实际期末员工人数]-[上一年实际期末员工人数]

YoY Actual Ending Headcount:=[Actual Ending Head Count]-[Prior Year Actual Ending Headcount]

Creating Year-over-Year Actual Average Headcount Measure

您可以按如下方式创建同比实际平均员工人数度量值 -

You can create Year-over-Year Actual Average Headcount Measure as follows −

同比实际平均员工人数：= [实际平均员工人数]-[上一年实际平均员工人数]

YoY Actual Average Headcount:= [Actual Average Headcount]-[Prior Year Actual Average Headcount]

Creating Year-over-Year Actual Total Headcount Measure

您可以按如下方式创建同比实际总员工人数度量值 -

You can create Year-over-Year Actual Total Headcount Measure as follows −

同比实际总员工人数：=[实际总员工人数]-[上一年实际总员工人数]

YoY Actual Total Headcount:=[Actual Total Head Count]-[Prior Year Actual Total Headcount]

Analyzing Data with Year-over-Year Actual Headcount Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add the fields Fiscal Quarter and Month from the Date table to Rows.
Add the measures – Actual Ending Head Count, Prior Year Actual Ending Head Count, YoY Actual Ending Head Count to Values.
Insert a Slicer on the field Fiscal Year.
Select FY2016 in the Slicer.

在同一工作表上创建另一个 Power 透视表，如下所示：

Create another Power PivotTable on the same worksheet as follows −

Add the fields Fiscal Quarter and Month from the Date table to Rows.
Add the measures – Actual Average Head Count, Prior Year Actual Average Head Count, YoY Actual Average Head Count to Values.

将切片器连接到此透视表，如下所示：

Connect the Slicer to this PivotTable as follows −

Click the Slicer.
Click the Options tab under Slicer Tools on the Ribbon.
Click Report Connections.

将出现“报表连接”对话框。

Report Connections dialog box appears.

Select the above two PivotTables.
Click OK.

Creating Year-over-Year Budget Ending Headcount Measure

您可以创建同比预算年末人数度量，如下所示：

You can create Year-over-Year Budget Ending Headcount Measure as follows −

同比预算年末人数：= [预算年末人数]-[上一年度实际年末人数]

YoY Budget Ending Headcount:= [Budget Ending Head Count]-[Prior Year Actual Ending Headcount]

Creating Year-over-Year Budget Average Headcount Measure

您可以创建同比预算平均人数度量，如下所示：

You can create Year-over-Year Budget Average Headcount Measure as follows −

同比预算平均人数：= [预算平均人数]-[上一年度实际平均人数]

YoY Budget Average Headcount:= [Budget Average Headcount]-[Prior Year Actual Average Headcount]

Creating Year-over-Year Budget Total Headcount Measure

您可以创建同比预算总人数度量，如下所示：

You can create Year-over-Year Budget Total Headcount Measure as follows −

同比预算总人数：= [预算总人数]-[上一年度实际总人数]

YoY Budget Total Headcount:=[Budget Total Headcount]-[Prior Year Actual Total Headcount]

Creating Year-over-Year Forecast Ending Headcount Measure

您可以创建同比预测年末人数度量，如下所示：

You can create Year-over-Year Forecast Ending Headcount Measure as follows −

同比预测年末人数：= [预测年末人数]-[上一年度实际年末人数]

YoY Forecast Ending Headcount:= [Forecast Ending Head Count]-[Prior Year Actual Ending Headcount]

Creating Year-over-Year Forecast Average Headcount Measure

您可以创建同比预测平均人数度量，如下所示：

You can create Year-over-Year Forecast Average Headcount Measure as follows −

同比预测平均人数：= [预测平均人数]-[上一年度实际平均人数]

YoY Forecast Average Headcount:= [Forecast Average Headcount]-[Prior Year Actual Average Headcount]

Creating Year-over-Year Forecast Total Headcount Measure

您可以创建同比预测总人数度量，如下所示：

You can create Year-over-Year Forecast Total Headcount Measure as follows −

同比预测总人数：= [预测总人数]-[上一年度实际总人数]

YoY Forecast Total Headcount:=[Forecast Total Headcount]-[Prior Year Actual Total Headcount]

Variance Headcount Measures

您可以根据迄今为止创建的人数度量创建差异人数度量。

You can create the Variance Headcount measures based on the Headcount measures that you have created so far.

Creating Variance to Budget Ending Headcount Measure

您可以按如下方式创建预算变动结束时的人头数指标：

You can create Variance to Budget Ending Headcount Measure as follows −

VTB Ending Head Count：= 'Finance Data'[Budget Ending Head Count]-'Finance Data'[Actual Ending Head Count]

VTB Ending Head Count:= 'Finance Data'[Budget Ending Head Count]-'Finance Data'[Actual Ending Head Count]

Creating Variance to Budget Average Headcount Measure

您可以按如下方式创建预算变动平均人头数指标：

You can create Variance to Budget Average Headcount Measure as follows −

VTB Average Head Count：= 'Finance Data'[Budget Average Headcount]-'Finance Data'[Actual Average Headcount]

VTB Average Head Count:= 'Finance Data'[Budget Average Headcount]-'Finance Data'[Actual Average Headcount

Creating Variance to Budget Total Headcount Measure

您可以按如下方式创建预算变动总人头数指标：

You can create Variance to Budget Total Headcount Measure as follows −

VTB Total Head Count：= 'Finance Data'[Budget Total Headcount]-'Finance Data'[Actual Total Head Count]

VTB Total Head Count:= 'Finance Data'[Budget Total Headcount]-'Finance Data'[Actual Total Head Count]

Creating Variance to Forecast Ending Headcount Measure

您可以按如下方式创建预测变动结束时的人头数指标：

You can create Variance to Forecast Ending Headcount Measure as follows −

VTF Ending Head Count：= 'Finance Data'[Forecast Ending Head Count]-'Finance Data'[Actual Ending Head Count]

VTF Ending Head Count:= 'Finance Data'[Forecast Ending Head Count]-'Finance Data'[Actual Ending Head Count]

Creating Variance to Forecast Average Headcount Measure

您可以按如下方式创建预测变动平均人头数指标：

You can create Variance to Forecast Average Headcount Measure as follows −

VTF Average Head Count：= 'Finance Data'[Forecast Average Headcount]-'Finance Data'[Actual Average Headcount]

VTF Average Head Count:= 'Finance Data'[Forecast Average Headcount]-'Finance Data'[Actual Average Headcount]

Creating Variance to Forecast Total Headcount Measure

您可以按如下方式创建预测变动总人头数指标：

You can create Variance to Forecast Total Headcount Measure as follows −

VTF Total Head Count：= 'Finance Data'[Forecast Total Headcount]-'Finance Data'[Actual Total Head Count]

VTF Total Head Count:= 'Finance Data'[Forecast Total Headcount]-'Finance Data'[Actual Total Head Count]

Creating Forecast Variance to Budget Ending Headcount Measure

您可以按如下方式创建预算的预测变动结束时的人头数指标：

You can create Forecast Variance to Budget Ending Headcount Measure as follows −

Forecast VTB Ending Head Count：= 'Finance Data'[Budget Ending Head Count]-'Finance Data'[Forecast Ending Head Count]

Forecast VTB Ending Head Count:= 'Finance Data'[Budget Ending Head Count]-'Finance Data'[Forecast Ending Head Count]

Creating Forecast Variance to Budget Average Headcount Measure

您可以按如下方式创建预算的预测变动平均人头数指标：

You can create Forecast Variance to Budget Average Headcount Measure as follows −

Forecast VTB Average Head Count：= 'Finance Data'[Budget Average Headcount]-'Finance Data'[Forecast Average Headcount]

Forecast VTB Average Head Count:='Finance Data'[Budget Average Headcount]-'Finance Data'[Forecast Average Headcount]

Creating Forecast Variance to Budget Total Headcount Measure

您可以按如下方式创建预算的预测变动总人头数指标：

You can create Forecast Variance to Budget Total Headcount Measure as follows −

Forecast VTB Total Head Count：= 'Finance Data'[Budget Total Headcount]-'Finance Data'[Forecast Total Headcount]

Forecast VTB Total Head Count:= 'Finance Data'[Budget Total Headcount]-'Finance Data'[Forecast Total Headcount

Cost Per Headcount Measures and Analysis

您已了解关于度量值的两种主要类别：

You have learnt about the two major categories of Measures −

Finance Measures.
Headcount Measures.

您将学习的第三个主要的度量值类别是人力成本度量值。任何组织都会希望知道人均年成本。人均年成本表示公司雇佣一位员工一整年的成本。

The third major category of measures that you will learn is People Cost Measures. Any organization will be interested to know the annualized cost per head. Annualized cost per head represents the cost to the company of having one employee on a full year basis.

要创建人均成本度量值，您首先需要创建某些初步的人力成本度量值。在账户表中，您有一个列 - 子分类，其中包含人员作为值之一。因此，您可以在子分类列中对账户表应用筛选器，以获取财务数据表的筛选器上下文以获取人员成本。

To create Cost Per Head measures, you need to first create certain preliminary People Cost Measures. In the Accounts table, you have a column – Sub Class that contains People as one of the values. Hence, you can apply a filter on the Accounts table on the Sub Class column to obtain the filter context onto the Finance Data table to obtain People Cost.

您可以使用这样获取的人员成本度量值和月数计数度量值来创建年化人员成本度量值。最后，您可以从年化人员成本度量值和平均人员编制度量值创建年化人均成本度量值。

You can use thus obtain People Cost measures and Count of Months measures to create Annualized People Cost measures. You can finally create Annualized Cost Per Head measures from Annualized People Cost measures and Average Head Count measures.

Creating Actual People Cost Measure

您可以按如下创建实际人员成本度量值：

You can create Actual People Cost measure as follows −

实际人员成本：=CALCULATE('Finance Data'[Actual Sum], FILTER('Finance Data', RELATED(Accounts[Sub Class])="People"))

Actual People Cost:=CALCULATE('Finance Data'[Actual Sum], FILTER('Finance Data', RELATED(Accounts[Sub Class])="People"))

Creating Budget People Cost Measure

您可以按如下创建预算人员成本度量值：

You can create Budget People Cost measure as follows −

预算人员成本：=CALCULATE('Finance Data'[Budget Sum], FILTER('Finance Data', RELATED(Accounts[Sub Class])="People"))

Budget People Cost:=CALCULATE('Finance Data'[Budget Sum], FILTER('Finance Data', RELATED(Accounts[Sub Class])="People"))

Creating Forecast People Cost Measure

您可以按如下创建预测人员成本度量值：

You can create Forecast People Cost measure as follows −

预测人员成本：=CALCULATE('Finance Data'[Forecast Sum], FILTER('Finance Data', RELATED(Accounts[Sub Class])="People"))

Forecast People Cost:=CALCULATE('Finance Data'[Forecast Sum], FILTER('Finance Data', RELATED(Accounts[Sub Class])="People"))

Creating Annualized Actual People Cost Measure

您可以按如下创建年化实际人员成本度量值：

You can create Annualized Actual People Cost measure as follows −

年化实际人员成本：=IF([CountOfActualMonths],[Actual People Cost]*12/[CountOfActualMonths],BLANK())

Annualized Actual People Cost:=IF([CountOfActualMonths],[Actual People Cost]*12/[CountOfActualMonths],BLANK())

Creating Annualized Budget People Cost Measure

您可以按如下方式创建年化预算人员成本指标：

You can create Annualized Budget People Cost measure as follows −

Annualized Budget People Cost:=IF([CountOfBudgetMonths], [Budget People Cost]*12/[CountOfBudgetMonths],BLANK())

Creating Annualized Forecast People Cost Measure

您可以按如下方式创建年化预测人员成本指标：

You can create Annualized Forecast People Cost measure as follows −

Annualized Forecast People Cost:=IF([CountOfForecastMonths],[Forecast People Cost]*12/[CountOfForecastMonths],BLANK())

Creating Actual Annualized Cost Per Head Measure

您可以按如下方式创建实际年化人均成本 (CPH) 指标：

You can create Actual Annualized Cost Per Head (CPH) measure as follows −

Actual Annualized CPH:=IF([Actual Average Headcount], [Annualized Actual People Cost]/[Actual Average Headcount],BLANK() )

Creating Budget Annualized Cost Per Head Measure

您可以按如下方式创建预算年化人均成本 (CPH) 指标：

You can create Budget Annualized Cost Per Head (CPH) measure as follows −

Budget Annualized CPH:=IF([Budget Average Headcount],[Annualized Budget People Cost]/[Budget Average Headcount],BLANK())

Creating Forecast Annualized Cost Per Head Measure

您可以按如下方式创建预测年化人均成本 (CPH) 指标：

You can create Forecast Annualized Cost Per Head (CPH) measure as follows −

Forecast Annualized CPH:=IF([Forecast Average Headcount],[Annualized Forecast People Cost]/[Forecast Average Headcount], BLANK())

Creating Prior Year Actual Annualized Cost Per Head Measure

您可以按如下方式创建上一年实际年化人均成本 (CPH) 指标：

You can create Prior Year Actual Annualized Cost Per Head (CPH) measure as follows −

Prior Year Actual Annualized CPH:=CALCULATE([Actual Annualized CPH], DATEADD('Date'[Date],-1,YEAR) )

Analyzing Data with Cost Per Head Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add the fields Fiscal Quarter and Fiscal Month from Date table to Rows.
Add the measures Actual Annualized CPH, Budget Annualized CPH, and Forecast Annualized CPH to Columns.
Add the field Fiscal Year from Date table to Filters.
Select FY2016 in the Filter.

按如下方式创建另一张 Power Pivot 表：

Create another Power PivotTable as follows −

Add the field Fiscal Quarter from Date table to Rows.
Add the measures Actual Annualized CPH, and Prior Year Actual Annualized CPH to Columns.
Insert a Slicer on the field Fiscal Year from Date table.
Select FY2015 and FY2016 on the Slicer.

Rate Variance and Volume Variance

您已了解如何创建全年人均成本和总人数指标。您可以使用这些指标创建比率差异和数量差异指标。

You have learnt how to create measures for Annualized Cost Per Head and Total Headcount. You can use these measures to create Rate Variance and Volume Variance measures.

Rate Variance measures calculate what portion of a Currency Variance is caused by differences in Cost Per Head.
Volume Variance measures calculate how much of the Currency Variance is driven by fluctuation in Headcount.

Creating Variance to Budget Rate Measure

您可以按照以下方式创建预算比率差异指标：

You can create Variance to Budget Rate measure as follows −

VTB 比率:=([预算全年人均成本]/12-[实际全年人均成本]/12)*[实际总人数]

VTB Rate:=([Budget Annualized CPH]/12-[Actual Annualized CPH]/12)*[Actual Total Head Count]

Creating Variance to Budget Volume Measure

您可以按照以下方式创建预算数量差异指标：

You can create Variance to Budget Volume measure as follows −

VTB 数量:=[VTB 总人数]*[预算全年人均成本]/12

VTB Volume:=[VTB Total Head Count]*[Budget Annualized CPH]/12

Analyzing Data with Variance to Budget Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add the fields Fiscal Quarter and Fiscal Month from Date table to Rows.
Add the measures Actual Annualized CPH, Budget Annualized CPH, VTB Rate, VTB Volume, VTB Sum to Values.
Add the fields Fiscal Year from Date table and Sub Class from Accounts table to Filters.
Select FY2016 in the Fiscal Year Filter.
Select People in the Sub Class Filter.
Filter Row Labels for Fiscal Quarter values FY2016-Q1 and FY2016-Q2.

您可以在上面的数据透视表中观察到以下内容：

You can observe the following in the above PivotTable −

VTB Sum value shown is only for Sub Class – People.
For Fiscal Quarter FY2016-Q1, VTB Sum is $4,705,568, VTB Rate is $970,506,297, and VTB Volume is $-965,800,727.
VTB Rate measure calculates that $970,506,297 of the Variance to Budget (VTB Sum) is caused by the difference in Cost per Head, and $-965,800,727 is caused by the difference in Headcount.
If you add VTB Rate and VTB Volume, you will get $4,705,568, the same value as returned by VTB Sum for Sub Class People.
Similarly, for Fiscal Quarter FY2016-Q2, VTB Rate is $1,281,467,662, and VTB Volume is $-1,210,710,978. If you add VTB Rate and VTB Volume, you will get $70,756,678, which is the VTB Sum value shown in the PivotTable.

Creating Year-Over-Year Rate Measure

您可以按如下方法创建同比比率维数

You can create Year-Over-Year Rate measure as follows −

同比比率 := （ [实际年化每小时生产率] / 12 - [上一年实际年化每小时生产率] / 12 ） * [实际总人数]

YoY Rate:=([Actual Annualized CPH]/12-[Prior Year Actual Annualized CPH]/12)*[Actual Total Head Count]

Creating Year-Over-Year Volume Measure

您可以按如下方法创建同比数量维数

You can create Year-Over-Year Volume measure as follows −

同比数量 := [同比实际总人数] * [上一年实际年化每小时生产率] / 12

YoY Volume:=[YoY Actual Total Headcount]*[Prior Year Actual Annualized CPH]/12

Creating Variance to Forecast Rate Measure

您可以按如下方法创建到预测比率的差异维数

You can create Variance to Forecast Rate measure as follows −

到预测比率的差异 := （ [预测年化每小时生产率] / 12 - [实际年化每小时生产率] / 12 ） * [实际总人数]

VTF Rate:=([Forecast Annualized CPH]/12-[Actual Annualized CPH]/12)*[Actual Total Head Count]

Creating Variance to Forecast Volume Measure

您可以按如下方法创建到预测数量的差异维数

You can create Variance to Forecast Volume measure as follows −

到预测数量的差异 := [到预测总人数] * [预测年化每小时生产率] / 12

VTF Volume:=[VTF Total Head Count]*[Forecast Annualized CPH]/12

Analyzing Data with Variance to Forecast Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add the fields Fiscal Quarter and Fiscal Month from Date table to Rows.
Add the measures Actual Annualized CPH, Forecast Annualized CPH, VTF Rate, VTF Volume, VTF Sum to Values.
Add the fields Fiscal Year from Date table and Sub Class from Accounts table to Filters.
Select FY2016 in the Fiscal Year Filter.
Select People in the Sub Class Filter.
Filter Row Labels for Fiscal Quarter values FY2016-Q1 and FY2016-Q2.

Creating Forecast Variance to Budget Rate Measure

您可以按照以下步骤创建“与预算利率的预测差异”度量：

You can create Forecast Variance to Budget Rate measure as follows −

预测 VTB 率：= ([预算年化 CPH12 - [预测年化 CPH12]) * [预测总人数]

Forecast VTB Rate:=([Budget Annualized CPH]/12-[Forecast Annualized CPH]/12)*[Forecast Total Headcount]

Creating Forecast Variance to Budget Volume Measure

您可以按照以下步骤创建“与预算数量的预测差异”度量：

You can create Forecast Variance to Budget Volume measure as follows −

预测 VTB 数量：= [预测 VTB 总人数] * [预算年化 CPH12

Forecast VTB Volume:=[Forecast VTB Total Head Count]*[Budget Annualized CPH]/12

Analyzing Data with Forecast Variance to Budget Measures

按以下步骤创建 Power Pivot 透视表 −

Create a Power PivotTable as follows −

Add the fields Fiscal Quarter and Fiscal Month from Date table to Rows.
Add the measures Budget Annualized CPH, Forecast Annualized CPH, Forecast VTB Rate, Forecast VTB Volume, Forecast VTB Sum to Values.
Add the fields Fiscal Year from Date table and Sub Class from Accounts table to Filters.
Select FY2016 in the Fiscal Year Filter.
Select People in the Sub Class Filter.
Filter Row Labels for Fiscal Quarter values FY2016-Q1 and FY2016-Q2.

data with forecast variance budget measures