Excel DAX - 使用时间智能

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

DAX 时间智能功能包括 -

  • 帮助您从数据中检索日期或日期范围的函数，这些函数用于计算相似时期内的值。

  • 使用标准日期间隔的函数，允许您比较月份、年份或季度的值。

  • 检索指定时间段的第一个和最后一个日期的函数。

  • 帮助您处理期初余额和期末余额的功能。

计算累计销售额

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

CLOSINGBALANCEMONTH (<表达式>,<日期>, [<过滤器>]) - 计算当前上下文中该月最后一个日期的表达式。

OPENINGBALANCEMONTH (<表达式>,<日期>, [<过滤器>]) - 计算当前上下文中该月第一个日期的表达式。

CLOSINGBALANCEQUARTER (<表达式>,<日期>, [<过滤器>]) - 评估当前上下文中季度最后一个日期的表达式。

OPENINGBALANCEQUARTER (<表达式>,<日期>, [<过滤器>]) - 在当前上下文中评估季度第一个日期的表达式。

CLOSINGBALANCEYEAR (<表达式>,<日期>, [<过滤器>], [<年份结束日期>]) - 计算当前上下文中一年的最后一个日期的表达式。

OPENINGBALANCEYEAR (<表达式>, <日期>, <过滤器>], [<年份结束日期>]) - 计算当前上下文中一年的第一个日期的表达式。

您可以使用以下 DAX 函数在指定时间为产品库存创建以下计算字段 -

月份开始库存值：= OPENINGBALANCEMONTH (
   SUMX（产品库存、产品库存[单位成本]*产品库存[单位余额]）、日期时间[日期键]
）


月末库存价值： = CLOSINGBALANCEMONTH (
   SUMX（产品库存、产品库存[单位成本]*产品库存[单位余额]）、日期时间[日期键]
）


季度开始库存值：= OPENINGBALANCEQUARTER (
   SUMX ProductInventory, (ProductInventory[UnitCost]*ProductInventory[UnitsBalance]), DateTime[DateKey]
）


季末库存价值： = CLOSINGBALANCEQUARTER (
   SUMX（产品库存、产品库存[单位成本]*产品库存[单位余额]）、日期时间[日期键]
）


年开始库存值： = OPENINGBALANCEYEAR (
   SUMX（产品库存、产品库存[单位成本]*产品库存[单位余额]）、日期时间[日期键]
）


年末库存价值： = CLOSINGBALANCEYEAR (
   SUMX（产品库存、产品库存[单位成本]*产品库存[单位余额]）、日期时间[日期键]
）

比较不同时间段的值

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

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

  • PREVIOUSMONTH (<dates>) - 返回一个表，其中包含上个月的所有日期的列，基于当前上下文中日期列中的第一个日期。

  • PREVIOUSQUARTER (<dates>) - 返回一个表，其中包含上一季度的所有日期的列，基于当前上下文中日期列中的第一个日期。

  • PREVIOUSYEAR (<dates>, <year_end_date>]) - 返回一个表，其中包含上一年所有日期的列，给定当前上下文中日期列中的最后一个日期。

您可以使用DAX函数创建以下计算字段来计算指定时间段内西部地区的销售额总和以进行比较 -

上个月销售额：= 计算 (
   SUM (WestSales[SalesAmount]), PREVIOUSMONTH (DateTime [DateKey])
）


上一季度销售额：= 计算 (
   SUM (WestSales[SalesAmount]), PREVIOUSQUARTER (DateTime [DateKey])
）


上一年销售额： = 计算 (
   SUM (WestSales[SalesAmount]), PREVIOUSYEAR (DateTime [DateKey])
）

比较平行时间段内的值

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

PARALLELPERIOD（<日期>、<间隔数>、<间隔>）

此 DAX 函数返回一个表，其中包含一列日期，表示与当前上下文中指定日期列中的日期平行的时间段，其中日期在时间上向前或向后移动了多个间隔。

您可以创建以下计算字段来计算西部地区上一年的销售额 -

上一年销售额： = 计算 (
   SUM (West_Sales[SalesAmount]), PARALLELPERIOD (DateTime[DateKey],-1,年)
）

计算运行总计

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

  • TOTALMTD (<表达式>,<日期>, [<过滤器>]) - 计算当前上下文中本月至今的表达式值。

  • TOTALQTD (<表达式>,<日期>, <过滤器>]) - 评估当前上下文中本季度至今日期的表达式值。

  • TOTALYTD (<表达式>,<日期>, [<过滤器>], [<年结束日期>]) - 评估当前上下文中表达式的年初至今值。

您可以使用 DAX 函数创建以下计算字段，用于计算西部地区在指定时间段内的销售额运行总和 -

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

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

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

计算自定义日期范围内的值

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

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

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

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

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

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

您可以使用 DAX 函数创建以下 DAX 公式来计算指定日期范围内西部地区的销售额总和 -

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

计算 （
   SUM (WestSales[SalesAmount]), DATESINPERIOD (DateTime[DateKey], DATE(2016,17,14),-15, 天)
）

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

= 计算 (
   SUM (WestSales[销售金额]),DATESBETWEEN (DateTime[DateKey], DATE (2016,1,1), DATE (2016,3,31))
）

  • DAX 公式创建一个计算字段，该字段获取当前上下文中在西部地区进行销售的首次日期。

= FIRSTDATE（WestSales [SaleDateKey]）

  • DAX 公式，用于创建一个计算字段，该字段获取当前上下文中西部地区进行销售的最后日期。

= LASTDATE（WestSales [SaleDateKey]）

  • DAX 公式，用于计算当前上下文中的日期早一年的日期。

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