工坊的小伙伴们大家好:
好久不见啦!今天我们一起来探索一下半累加度量相关的内容。首先,介绍一下什么是半累加度量。
半累加度量:只针对包含度量值的某些(而不是全部)维度进行聚合。例如银行账户或者产品库存可以在其他维度上进行聚合,但时间除外。我们不能考虑所选的时间段(例如,一个月),只能考虑与之所选周期相关的特定时间。可能是第一天,最后一天的交易,等等。对于这种的计算我们可以使用SUM对某些维度进行聚合,但需要对其他维度使用不同的聚合度量,这种度量称为半累加度量。
那这种半累加度量怎么计算呢?接下来我们通过一个案例来了解具体计算方法。
在下面的表中,您可以看到一个产品库存表的摘录。相同的产品在每个日期都有一个Units
Balance值,但你不能用两个不同的日期(你可能想要计算不同日期的平均值)来对这一列求和。如果您想计算2005年7月的单位余额的值,您需要在每个月的最后一天筛选这些行,而忽略所有其他日期的行。
http://s9/mw690/006vR4QNzy7lLxcwmDKd8&690
First and Last Date
为了在DAX计算半累加度量,我们需要使用LASTDATE来筛选特定日期的上下文作为参数传递,公式如下:
Units LastDate :=
CALCULATE ( SUM ( Inventory[UnitsBalance] ),
LASTDATE ( 'Date'[Date] ) )
|
结果显示,每个季度的总金额对应于本季度最后一个月的值(例如,Q1的值与3月份相同)。每个月的值对应于当月最后一天的价值(在这里没有体现每天的余额)。
http://s10/mw690/006vR4QNzy7lLxdAtYZ09&690
此处LastDate计算假定每年的每个月都有数据。如果库存数据是每日的,这不是一个问题,但它如果库存只写在工作日而一个月的月底是星期六,可能会有些问题,在下面的图中,您可以看到使用单元的LastDate所发生的情况,库存表中有直到2007年12月15日的数据,但是您没有看到2007年的总数,Q4和12月的数据。
http://s10/mw690/006vR4QNzy7lLxeTaOta9&690
First and Last Non Blank
原因是,LASTDATE公式在筛选上下文中的日期操作,日期表包含2007年的所有日期(这是最佳的方式,否则其他时间的智能功能将无法正常工作)。这时您可以使用另一个DAX函数,LASTNONBLANK,它返回的最后一个日期满足作为第二个参数传递的表达式的非空白条件。
Units LastNonBlank :=
CALCULATE (
SUM ( Inventory[UnitsBalance]
),
LASTNONBLANK (
'Date'[Date],
CALCULATE ( SUM ( Inventory[UnitsBalance] )
)
)
) |
重要的是它的的第二个参数使用隐式或显式的计算来应用上下文转换,如果没有第二个参数,您将使用未筛选的表达式,结果将与LASTDATE相同。您可以在下图中看到结果,其中12月、Q4和2007年的总数都显示出来了。
http://s6/mw690/006vR4QNzy7lLxfDftr35&690
如果你需要一个周期的第一个日期而不是最后一个日期,您可以使用FIRSTDATE。还可以使用FIRSTNONBLANK获取第一个数据,这与您在取最后一个数据中使用LASTNONBLANK所做的类似。其实还可以用MAX函数计算,但要注意的是MAX函数返回一个标量值,语法与LASTNONBLANK不同的是结合FILTER函数使用,表达式如下:
Units MaxDate :=
CALCULATE (
SUM ( Inventory[UnitsBalance] ),
FILTER (
ALL( 'Date'[Date] ),
'Date'[Date] = MAX ( 'Date'[Date]
)
)
) |
此外,您还可以通过将MAX值存储在一个变量中来简化语法,如下:
Units MaxDate :=
VAR LastDayInSelection = MAX ( 'Date'[Date]
)
RETURN CALCULATE (
SUM ( Inventory[UnitsBalance] ),
'Date'[Date] =
LastDayInSelection
) |
最好的做法是在编写FILTER表达式时使用LASTDATE,而当您在行上下文中编写逻辑表达式时,使用MAX会更好一些,因为LASTDATE暗示了隐藏外部筛选上下文的上下文转换。
Opening and Closing Periods
在半累加度量中你也可以使用其他的时间智能函数,以获得一段时间(年、季度或月)的第一个和最后一个日期。例如,查看月份级别(可以用行形式显示),您可能希望在同一行中显示季度末和年底的值,如下图所示。
http://s12/mw690/006vR4QNzy7lLxhieDh5b&690
上述数据透视表中,对ClosingMonth、ClosingQuarter、ClosingYear、OpeningMonth、OpeningQuarter和OpeningYear的定义如下:
ClosingMonth := CLOSINGBALANCEMONTH
(
SUM (
Inventory[UnitsBalance] ), 'Date'[Date]
)
ClosingQuarter := CLOSINGBALANCEQUARTER (
SUM (
Inventory[UnitsBalance] ),
'Date'[Date] )
ClosingYear := CLOSINGBALANCEYEAR (
SUM (
Inventory[UnitsBalance] ),
'Date'[Date] )
OpeningMonth := OPENINGBALANCEMONTH (
SUM (
Inventory[UnitsBalance] ),
'Date'[Date] )
OpeningQuarter := OPENINGBALANCEQUARTER
(
SUM (
Inventory[UnitsBalance] ),
'Date'[Date] )
OpeningYear := OPENINGBALANCEYEAR (
SUM (
Inventory[UnitsBalance] ), 'Date'[Date]
)
|
本文参考SQL BI的相关文章,想看原文可以查阅:
https://www.sqlbi.com/articles/semi-additive-measures-in-dax/
*Power
Pivot工坊原创文章,转载请注明出处。
延伸阅读:
Power Pivot工坊文章目录大全
参数第三弹—模拟参数
参数第二弹 - 最近N天的实现
工坊实验室之部分切片的实现
Power BI 也能做这样的组合图?
工坊实验室之失灵的筛选
工坊实验室之R脚本视觉对象
工坊实验室之数据标签的位置
工坊实验室之桑基图排序
工坊实验室之流向图展现
工坊实验室之使用瀑布图
一张图看懂微软PowerBI系列组件
一张图看懂Power BI 架构
亮瞎双眼的PowerBI可视化图表
当Power BI 遇上洪灾
当Power
BI 遇上欧冠决赛
当Power
BI遇上条形码
当Power
BI遇上Visio
如果您想深入学习微软Power BI,欢迎登录网易云课堂试听学习我们的“从Excel到Power
BI数据分析可视化”系列课程。点击左下角“阅读原文”可直达云课堂。或者关注我们的公众号(PowerPivot工坊)后猛戳”在线学习”
http://s7/mw690/006vR4QNzy7lLxnkGX4e6&690
长按下方二维码关注“Power Pivot工坊”获取更多微软Power
BI、PowerPivot相关文章、资讯。欢迎小伙伴儿们转发分享~
http://s15/mw690/006vR4QNzy7lLxo3Nimbe&690