PowerBI之DAX神功：第2卷第3回 计算移动平均值再次理解Filter+ALL
关于计算移动平均值大家可以使用《孙兴华讲PowerBI火力全开》笔记 27.03 DATESINPERIOD函数
我们今天使用Calculate+filter+all的方法来实现
一、带着网友的两个疑问来学习
我们从日期表中取近3天的日期，《The Definitive Guide to DAX》中推荐方法如下：
【新建表】表 = FILTER(all('日期表'),'日期表'[日期]>max('日期表'[日期])-3 && '日期表'[日期]<=MAX('日期表'[日期]))
书中介绍的方法是正确的，只是书中并没有告诉你为什么这样做？
网友疑问1：filter为什么要增加第2条件？
比如，我们只用第1条件
【新建表】表 = FILTER(all('日期表'),'日期表'[日期]>max('日期表'[日期])-3)
max(日期表[日期])：2021/1/15
max(日期表[日期])-3：2021/1/12
FILTER('日期表','日期表'[日期]>max('日期表'[日期])-3) : 2021/1/13、2021/1/14、2021/1/15
我为什么还要在日期表中限定小于等于2021/1/15这个条件？加不加效果都一样呀？
其实，书中让你这么做必有它的道理，肯定不一样。
网友疑问2：filter(表)与filter(all(表))效果一样，加不加ALL有什么意义？
接下来，我们就带着这两个疑问来学习今天的知识。
二、尽量采用动态方法创建日期表
《孙兴华讲PowerBI火力全开》笔记27课 补充2：动态创建日期表
【新建表】
日期表 = ADDCOLUMNS(
CALENDAR(FIRSTDATE('销售表'[日期]),LASTDATE('销售表'[日期])),
"年", YEAR ( [Date] ),
"季度", ROUNDUP(MONTH([Date])/3,0),
"月", MONTH([Date]),
"周", weeknum([Date]),
"年季度", year([date]) & "Q" & ROUNDUP(MONTH([Date])/3,0),
"年月", year([Date]) * 100 + MONTH([Date]),
"年周", year([Date]) * 100 + weeknum([Date]),
"星期几", WEEKDAY([Date])
)
Ps：你试想一下，如果出现下面的情况，你的数据还正确吗？
所以我们有必要，限定日期表中最大日期与最小日期，同我们的销售表一致
三、Filter(表)与Filter(ALL(表))的区别（暂不涉及原理）
切记：原理我稍候讲，现在我们只看区别，不说原理
我们先忽略filter第2参数，看看filter(表)与filter(all(表))区别
完整步骤：
【度量值】平均销量 = AVERAGE('销售表'[销量])
【度量值】近3天移动平均1 = CALCULATE([平均销量],FILTER('日期表','日期表'[日期]>MAX('日期表'[日期])-3))
【度量值】近3天移动平均2 = CALCULATE([平均销量],FILTER(ALL('日期表'),'日期表'[日期]>MAX('日期表'[日期])-3))
解释：以A商品为例
平均销量：A商品销量之和除以15天，就是平均销量。（2021/1/1~2021/1/15 共15天）
近3天的移动平均：A商品在2021/1/13~2021/1/15这3天的销售之和除以3。
Ps: 关于移动平均在业务上的应用不属于我们的DAX原理知识。
到目前为止，你看不到filter(表)与filter(all(表))的区别
如果你只是想看每种商品最近3天移动平均值是多少，这两种写法都OK
可是，当你想看具体每一天的近3日移动平均值时，第1种写法就不OK了
我们将'日期表'[日期]放到行标题上
发现2个问题：
【1】【度量值】近3天移动平均1 = CALCULATE([平均销量],FILTER('日期表','日期表'[日期]>MAX('日期表'[日期])-3))
通过日期表中的日期字段进行筛选后，第一种写法其实与【平均销售】每日显示值相同，因为你用的Filter(表)，就证明你那张表可以筛选，如你所愿，日期表可以筛选这个度量值，每天的销量是多少就是多少，只是这个度量值【总计】算的是近3天的移动平均值。
【2】【度量值】近3天移动平均2 = CALCULATE([平均销量],FILTER(ALL('日期表'),'日期表'[日期]>MAX('日期表'[日期])-3))
可是第二种写法也不对呀？我们的确使用了Filter(all(表))限制了这张表的筛选功能。但是当我们倒着看，A商品在1月15日算近3天的移动平均值=14这是正确的，可是1月14日的近3天平均值，他计算的是1月12日至1月15日近4天的移动平均值，正确的应该是计算1月12日至1月14日。以此类推上面都是这样。我们需要的是每个日期做为最大值，求近3天的移动平均值
处理方法如下：
四、Filter为什么要限定第二条件
秘密在这里，我们将日期表中的日期列放到行标题上筛选，会这样显示
我们通常的方法是这样操作的：点下箭头，选择日期
就是因为这样的习惯，让我们错过了真相，其实每个人都可以成为江户川柯南，但最终大多数人变成了毛利小五郎。
我们点叉子只留一个日（就是每一天）
当我们再次展开A商品时，真相出现了
明明只有15天，为什么第2个度量值16日至31日还有数据？
原因：你使用的是filter+all声明了日期表任何字段都无法筛选，所以日期表上只有15天和你的【度量值】近3天移动平均2 有毛线的关系？
处理方法：你需要限定不能超过日期表中的最大日期
【度量值】近3天移动平均3 = CALCULATE([平均销量],FILTER(all('日期表'),'日期表'[日期]>max('日期表'[日期])-3 && '日期表'[日期]<=MAX('日期表'[日期])))
现在4个度量值：
【平均销量】"总计"算的是平均值，日期筛选是每一天的销量。
【近3天移动平均1】"总计"算的是最后3天的平均值，日期筛选是每一天的销量。
【近3天移动平均2】"总计"算的是最后3天的平均值，日期筛选是错的。
【近3天移动平均3】"总计"算的是最后3天的平均值，日期筛选是正确的。
我们将4个度量值放到图表上，切片器筛选商品A
每一天的【平均销量】与【近3天移动平均1】重合了
【近3天移动平均2】是错的，我们需要的是【近3天移动平均3】也就是紫色的线
五、Filter(表)与Filter(ALL(表))的原理
我一直跟大家强调，不是哪里疼就一定要治哪里，有的时候头疼治脚，有的时候腿疼治腰。
我的想法：
【1】理解表、all表、all列
《DAX神功》第1卷第8回 基础表函数之ALL与ALLEXCEPT函数
filter(表) 这张表中所有字段都可以筛选
filter(all(表)) 这张表中所有字段都不可以筛选
filter(all(表[列])) 这张表中指定字段不可以筛选
Ps: 这个若不理解，可以弃坑了。
【2】英语只能看懂文字，数学才能理解原理
很多学习好的人上数学课犯困的时候就是老师讲证明，讲等价公式时。什么时候兴奋？套公式做题得高分时，好同学都兴奋了。他们不知道，自己重复劳动做的题，计算机1秒可以做他几年的计算量，而老师讲的证明和等价公式是逻辑，是未来工作中隐形的工具。
写等价公式很重要：
刚才上面那4个度量值是不是只有【近3天移动平均3】是我们需要的且正确的？
【度量值】近3天移动平均3 = CALCULATE([平均销量],FILTER(all('日期表'),'日期表'[日期]>max('日期表'[日期])-3 && '日期表'[日期]<=MAX('日期表'[日期])))
我们就写它的等价度量值：
近3天移动平均4 =
var m=max('日期表'[日期])
return
CALCULATE([平均销量],'日期表'[日期]>m-3 && '日期表'[日期]<=m,all('日期表'))
Ps:注意我们之前一直强调的懒惰计算
公式解释：
Calculate的筛选器是布尔类型时，它的条件列就是取消筛选，也就是说日期表中的日期列不能筛选。
我们的案例日期表只有日期这一个列，所以不写all('日期表')也是一样的，但是真实的操作中，你的日期表肯定有很多列，所以限制日期表所有列，我们必须加上all('日期表')
然后我们看看结果：一样，一模一样
截止到现在，Filter(ALL(表))是怎么回事你应该明白了吧？我再看一下它的计算过程
刚才我们一直是倒着说的，现在我们正着说：
filter是迭代函数，逐行计算，从第1行开始
（1）1日，销量为1，前面没有日期，所以 1/1=1
（2）2日，销量为2，前面只有1天，所以，（1+2）/2=1.5
（3）3日，销量为3，近3日，（1+2+3）/3=2
（4）4日，销量为4，近3日，（2+3+4）/3=3
.......................以此类推
【3】最后我们再说说Filter(表)
【度量值】近3天移动平均1 = CALCULATE([平均销量],FILTER('日期表','日期表'[日期]>MAX('日期表'[日期])-3))
我们可以将filter第2个条件也加上，效果是一样的
【度量值】近3天移动平均1 = CALCULATE([平均销量],FILTER('日期表','日期表'[日期]>MAX('日期表'[日期])-3 && '日期表'[日期]<=MAX('日期表'[日期])))
我们写出【近3天移动平均1】的等价公式：
近3天移动平均5 =
var m=max('日期表'[日期])
return
CALCULATE([平均销量],KEEPFILTERS('日期表'[日期]>m-3 && '日期表'[日期]<=m))
Ps:我们恢复了日期表中日期列的筛选功能，又没有限制其它列，是不是日期表所有列均可以筛选？果然效果一致。
详见《DAX神功》第1卷第17回 KEEPFILTERS函数调节器与Values做筛选器
今天我们讲的是理想状态下的情况，销售表中日期都是连续的，不连续的情况如何处理？我们下节课再说。
《孙兴华讲PowerBI火力全开》PowerBI必学课程
https://www.bilibili.com/video/BV1qa4y1H7wp
《DAX神功》文字版合集：
https://www.bilibili.com/read/readlist/rl442274
《DAX神功》视频版合集：
https://www.bilibili.com/video/BV1YE411E7p3
PowerBI(DAX函数)、PowerQuery(M函数)、Python办公自动化、Python爬虫、Python数据分析、ExcelVBA、WordVBA、AccessVBA、MySQL等等
