Excel Dax 简明教程

Excel DAX - Using Time Intelligence

您已在“理解时间智能”一章中了解了 DAX 强大的功能时间智能。在此章节中，您将学习如何在各种场景中使用 DAX 时间智能函数。

You have learnt about the DAX powerful feature Time Intelligence in the chapter – Understanding Time Intelligence. In this chapter, you will learn how to use DAX time intelligence functions in various scenarios.

DAX 时间智能函数包括：

DAX time intelligence functions include −

Functions that help you retrieve dates or date ranges from your data, which are used to calculate values across similar periods.
Functions that work with standard date intervals, to allow you to compare values across months, years, or quarters.
Functions that retrieve the first and last date of a specified period.
Functions that help you work on the opening and closing balances.

Calculating Cumulative Sales

您可以使用 DAX 时间智能函数创建公式来计算累计销售额。以下 DAX 函数可用于计算期末和期初余额：

You can use DAX time intelligence functions to create formulas for calculating cumulative sales. The following DAX functions can be used to calculate closing and opening balances −

CLOSINGBALANCEMONTH (<expression>,<dates>, [<filter>]) - 在当前上下文中，该函数在该月份的最后一天对表达式求值。

CLOSINGBALANCEMONTH (<expression>,<dates>, [<filter>]) − Evaluates the expression at the last date of the month in the current context.

OPENINGBALANCEMONTH (<expression>,<dates>, [<filter>]) - 在当前上下文中，该函数在该月份的第一天对表达式求值。

OPENINGBALANCEMONTH (<expression>,<dates>, [<filter>]) − Evaluates the expression at the first date of the month in the current context.

CLOSINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) - 在当前上下文中，该函数在该季度的最后一天对表达式求值。

CLOSINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) − Evaluates the expression at the last date of the quarter in the current context.

OPENINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) - 在当前上下文中，该函数在该季度的第一对表达式求值。

OPENINGBALANCEQUARTER (<expression>,<dates>, [<filter>]) − Evaluates the expression at the first date of the quarter, in the current context.

CLOSINGBALANCEYEAR (<expression>,<dates>, [<filter>], [<year_end_date>]) - 在当前上下文中，该函数在该年份的最后一天对表达式求值。

CLOSINGBALANCEYEAR (<expression>,<dates>, [<filter>], [<year_end_date>]) − Evaluates the expression at the last date of the year in the current context.

OPENINGBALANCEYEAR (<expression>, <dates>, <filter>], [<year_end_date>]) - 在当前上下文中，该函数在该年份的第一天对表达式求值。

OPENINGBALANCEYEAR (<expression>, <dates>, <filter>], [<year_end_date>]) − Evaluates the expression at the first date of the year in the current context.

您可以使用以下 DAX 函数针对某个特定时间点的产品清单创建以下计算的字段：

You can create the following calculated fields for the product inventory at a specified time by using the following DAX functions −

Month Start Inventory Value: = OPENINGBALANCEMONTH (
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Month End Inventory Value: = CLOSINGBALANCEMONTH (
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Quarter Start Inventory Value: = OPENINGBALANCEQUARTER (
   SUMX ProductInventory, (ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Quarter End Inventory Value: = CLOSINGBALANCEQUARTER (
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Year Start Inventory Value: = OPENINGBALANCEYEAR (
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Year End Inventory Value: = CLOSINGBALANCEYEAR (
   SUMX (ProductInventory, ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
)

Comparing Values across Different Time Periods

DAX 支持的默认时间段为月、季度和年。

The default time periods supported by DAX are months, quarters, and years.

您可以使用以下 DAX 时间智能函数比较不同时间段内的总和。

You can use the following DAX time intelligence functions to compare the sums across different time periods.

PREVIOUSMONTH (<dates>) − Returns a table that contains a column of all the dates from the previous month, based on the first date in the dates column, in the current context.
PREVIOUSQUARTER (<dates>) − Returns a table that contains a column of all the dates from the previous quarter, based on the first date in the dates column, in the current context.
PREVIOUSYEAR (<dates>, <year_end_date>]) − Returns a table that contains a column of all dates from the previous year, given the last date in the dates column, in the current context.

您可以使用 DAX 函数创建以下计算的字段，用于计算 West 区域在指定时间段内的销售总和，以进行比较：

You can create the following calculated fields for calculating sum of sales in the West region at the specified time periods for comparison, by using the DAX functions −

Previous Month Sales: = CALCULATE (
   SUM (WestSales[SalesAmount]), PREVIOUSMONTH (DateTime [DateKey])
)

Previous Quarter Sales: = CALCULATE (
   SUM (WestSales[SalesAmount]), PREVIOUSQUARTER (DateTime [DateKey])
)

Previous Year Sales: = CALCULATE (
   SUM (WestSales[SalesAmount]), PREVIOUSYEAR (DateTime [DateKey])
)

Comparing Values Across Parallel Time Periods

您可以使用 DAX 时间智能函数 PARALLELPERIOD 比较在与指定时间段平行的某个时间段内的总和。

You can use the DAX time intelligence function PARALLELPERIOD to compare the sums across a period parallel to the specified time period.

PARALLELPERIOD (<dates>, <number_of_intervals>, <interval>)

此 DAX 函数返回一个包含日期列的表，该日期列表示在当前上下文中与指定日期列中的日期并行的时期的日期，且日期向前或向后偏移了指定数量的间隔。

This DAX function returns a table that contains a column of dates representing a period parallel to the dates in the specified dates column, in the current context, with the dates shifted a number of intervals either forward or backward in time.

可以使用以下计算字段计算西部地区的去年销售额 −

You can create the following calculated field for calculating the previous year’s sales in West region −

Previous Year Sales: = CALCULATE (
   SUM (West_Sales[SalesAmount]), PARALLELPERIOD (DateTime[DateKey],-1,year)
)

Calculating Running Totals

可以使用以下 DAX 时间智能函数计算运行总计或运行合计。

You can use the following DAX time intelligence functions to calculate running totals or running sums.

TOTALMTD (<expression>,<dates>, [<filter>]) − Evaluates the value of the expression for the month to date in the current context.
TOTALQTD (<expression>,<dates>, <filter>]) − Evaluates the value of the expression for the dates in the quarter to date, in the current context.
TOTALYTD (<expression>,<dates>, [<filter>], [<year_end_date>]) − Evaluates the year-to-date value of the expression in the current context.

可以使用 DAX 函数创建以下计算字段以计算在指定时间段内西部地区的销售额运行合计 −

You can create the following calculated fields for calculating running sum of sales in the West region at specified time periods, by using the DAX functions −

月份运行合计：= TOTALMTD (SUM (West_Sales[SalesAmount]), DateTime[DateKey])

Month Running Sum: = TOTALMTD (SUM (West_Sales[SalesAmount]), DateTime[DateKey])

季度运行合计：= TOTALQTD (SUM (WestSales[SalesAmount]), DateTime[DateKey])

Quarter Running Sum: = TOTALQTD (SUM (WestSales[SalesAmount]), DateTime[DateKey])

年份运行合计：= TOTALYTD (SUM (WestSales[SalesAmount]), DateTime[DateKey])

Year Running Sum: = TOTALYTD (SUM (WestSales[SalesAmount]), DateTime[DateKey])

Calculating a Value over a Custom Date Range

使用 DAX 时间智能函数可以检索一组自定义日期，你可将其用作 DAX 函数的输入，从而执行计算，以跨时间段创建自定义聚合。

You can use DAX time intelligence functions to retrieve a custom set of dates, which you can use as an input to a DAX function that performs calculations, to create custom aggregates across time periods.

DATESINPERIOD (<dates>, <start_date>, <number_of_intervals>, <interval>) − 返回一个表，其中包含一列从 start_date 开始并持续指定 number_of_intervals 个时间间隔的日期。

DATESINPERIOD (<dates>, <start_date>, <number_of_intervals>, <interval>) − Returns a table that contains a column of dates that begins with the start_date and continues for the specified number_of_intervals.

DATESBETWEEN (<dates>, <start_date>, ) − 返回一个表，其中包含一列从 start_date 开始并持续到 end_date 的日期。

DATESBETWEEN (<dates>, <start_date>, ) − Returns a table that contains a column of dates that begins with the start_date and continues until the end_date.

DATEADD (<dates>,<number_of_intervals>,<interval>) − 返回一个表，其中包含一列日期，从当前上下文的日期中向前或向后移动指定时间间隔数。

DATEADD (<dates>,<number_of_intervals>,<interval>) − Returns a table that contains a column of dates, shifted either forward or backward in time by the specified number of intervals from the dates in the current context.

FIRSTDATE (<dates>) − 返回当前上下文中指定日期列中的第一个日期。

FIRSTDATE (<dates>) − Returns the first date in the current context for the specified column of dates.

LASTDATE (<dates>) − 返回当前上下文中指定日期列中的最后一个日期。

LASTDATE (<dates>) − Returns the last date in the current context for the specified column of dates.

通过使用 DAX 函数，可以创建以下用于计算指定日期范围内西区销售总和的 DAX 公式 −

You can create the following DAX formulas for calculating the sum of sales in the West region over a specified date range, by using the DAX functions −

DAX Formula to calculate the sales for the 15 days prior to July 17, 2016.

CALCULATE (
   SUM (WestSales[SalesAmount]), DATESINPERIOD (DateTime[DateKey], DATE(2016,17,14), -15, day)
)

DAX Formula to create a calculated field that calculates the first quarter 2016 sales.

= CALCULATE (
   SUM (WestSales[SalesAmount]),DATESBETWEEN (DateTime[DateKey], DATE (2016,1,1), DATE (2016,3,31))
)

DAX Formula to create a calculated field that obtains the first date when a sale was made in the West region for the current context.

= FIRSTDATE (WestSales [SaleDateKey])

DAX Formula to create a calculated field that obtains the last date when a sale was made in the West region for the current context.

= LASTDATE (WestSales [SaleDateKey])

DAX Formula to calculate the dates that are one year before the dates in the current context.

= DATEADD (DateTime[DateKey],-1,year)