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时间智能函数比较跨不同时间段的总和。

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

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

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

您可以使用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 (<日期>,<间隔数>,<间隔>) - 返回一个表,其中包含一个日期列,在当前上下文中,从日期向前或向后移动指定的间隔数。

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

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

您可以使用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) 
广告