Excel Dax 简明教程

Excel DAX - Using Time Intelligence

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

DAX 时间智能函数包括：

用于帮助您从数据中检索日期或日期范围的函数，可用于计算类似期间的值。
与标准日期区间一起使用的函数，以便您比较不同月份、年份或季度的值。
检索指定期间的第一天和最后一天的函数。
帮助您处理期初和期末余额的函数。

Calculating Cumulative Sales

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

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

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

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

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

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

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

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

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

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.

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

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

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

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.

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 −

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

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

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

Calculating a Value over a Custom Date Range

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

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

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

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

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

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

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

计算 2016 年 7 月 17 日前 15 天销售额的 DAX 公式。

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

创建计算字段以计算 2016 年第一季度销售额的 DAX 公式。

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

创建计算字段以获取当前上下文中在西区进行销售时的第一个日期的 DAX 公式。

= FIRSTDATE (WestSales [SaleDateKey])

创建计算字段以获取当前上下文中在西区进行销售时的最后一个日期的 DAX 公式。

= LASTDATE (WestSales [SaleDateKey])

计算当前上下文中日期前一年的日期的 DAX 公式。

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