Excel DAX 使用时间智能
你已在“了解时间智能”一章中了解了 DAX 的强大功能时间智能。在本章中,你将学习如何在各种场景中使用 DAX 时间智能函数。
DAX时间智能功能包括:
-
帮助你从数据中检索日期或日期范围的函数,这些函数用于计算相似时期的值。
-
使用标准日期间隔的函数,允许你比较月、年或季度的值。
-
检索指定期间的第一个和最后一个日期的函数。
-
帮助你处理期初余额和期末余额的函数。
计算累计销售额
你可以使用 DAX 时间智能函数来创建计算累计销售额的公式。以下 DAX 函数可用于计算期末余额和期初余额:
CLOSINGBALANCEMONTH(<表达式>、<日期>、[<过滤器>]) : 评估当前上下文中当月最后一天的表达式。
OPENINGBALANCEMONTH(<表达式>、<日期>、[<过滤器>]) : 评估当前上下文中当月第一个日期的表达式。
CLOSINGBALANCEQUARTER(<表达式>,<日期>,[<过滤器>]) : 评估当前上下文中季度最后日期的表达式。
OPENINGBALANCEQUARTER(<表达式>、<日期>、[<过滤器>]) : 在当前上下文中评估季度第一个日期的表达式。
CLOSINGBALANCEYEAR(<表达式>、<日期>、[<过滤器>]、[
OPENINGBALANCEYEAR(<表达式>、<日期>、<过滤器>]、[
你可以使用以下 DAX 函数为指定时间的产品库存创建以下计算字段:
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] )
比较不同时间段的值
DAX 支持的默认时间段是月、季度和年。
你可以使用以下 DAX 时间智能函数来比较不同时间段的总和。
-
上个月(<日期>) : 在当前上下文中,基于日期列中的第一个日期,返回一个包含上个月所有日期的列的表。
-
上一季度(<日期>) : 根据当前上下文中日期列中的第一个日期,返回一个包含上一季度所有日期列的表。
-
上一年度(<日期>,<年结束日期>]) :返回一个表,其中包含上一年所有日期的列,给定日期列中的最后一个日期,在当前上下文中。
你可以使用 DAX 函数创建以下计算字段,用于计算西部地区在指定时间段的销售额总和进行比较:
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]) )
比较并行时间段的值
你可以使用 DAX 时间智能函数 PARALLELPERIOD 来比较与指定时间段平行的时间段内的总和。
PARALLELPERIOD (
此 DAX 函数返回一个表,其中包含一列日期,该列表示与当前上下文中指定日期列中的日期平行的时间段,日期在时间上向前或向后移动了多个间隔。
你可以创建以下计算字段,用于计算西部地区上一年的销售额:
Previous Year Sales: = CALCULATE ( SUM (West_Sales[SalesAmount]), PARALLELPERIOD (DateTime[DateKey],-1,year) )
计算运行总计
你可以使用以下 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 (
DATESBETWEEN(<日期>,<开始日期>,
DATEADD (
第一天(<日期>) :返回当前上下文中指定日期列的第一个日期。
LASTDATE (<日期>) :返回当前上下文中指定日期列的最后一个日期。
你可以使用 DAX 函数创建以下 DAX 公式,用于计算指定日期范围内西部地区的销售额总和:
-
用于计算 2016 年 7 月 17 日之前 15 天的销售额的 DAX 公式。
CALCULATE ( SUM (WestSales[SalesAmount]), DATESINPERIOD (DateTime[DateKey], DATE(2016,17,14), -15, day) )
-
DAX 公式创建一个计算字段,用于计算 2016 年第一季度的销售额。
= 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)