Data Modeling With Dax 简明教程

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

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

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 −

  1. Add Month from the Date table to Rows.

  2. Add the measures Budget Sum, YTD Budget Sum, YTD Actual Sum, Budget Attainment % and Unexpended Balance from Finance Data table to Values.

  3. Insert a Slicer on the Fiscal Year field.

  4. Select FY2016 in the Slicer.

budget measures