關(guān)于計(jì)算移動(dòng)平均值大家可以使用《孫興華講PowerBI火力全開》筆記 27.03??DATESINPERIOD函數(shù)

我們今天使用Calculate+filter+all的方法來(lái)實(shí)現(xiàn)

一、帶著網(wǎng)友的兩個(gè)疑問(wèn)來(lái)學(xué)習(xí)

我們從日期表中取近3天的日期，《The Definitive Guide to DAX》中推薦方法如下：

【新建表】表 = FILTER(all('日期表'),'日期表'[日期]>max('日期表'[日期])-3 && '日期表'[日期]<=MAX('日期表'[日期]))

書中介紹的方法是正確的，只是書中并沒(méi)有告訴你為什么這樣做？

網(wǎng)友疑問(wèn)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這個(gè)條件？加不加效果都一樣呀？

其實(shí)，書中讓你這么做必有它的道理，肯定不一樣。

網(wǎng)友疑問(wèn)2：filter(表)與filter(all(表))效果一樣，加不加ALL有什么意義？

接下來(lái)，我們就帶著這兩個(gè)疑問(wèn)來(lái)學(xué)習(xí)今天的知識(shí)。

二、盡量采用動(dòng)態(tài)方法創(chuàng)建日期表

《孫興華講PowerBI火力全開》筆記27課 補(bǔ)充2：動(dòng)態(tài)創(chuàng)建日期表

【新建表】

日期表 = 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：你試想一下，如果出現(xiàn)下面的情況，你的數(shù)據(jù)還正確嗎？

所以我們有必要，限定日期表中最大日期與最小日期，同我們的銷售表一致

三、Filter(表)與Filter(ALL(表))的區(qū)別（暫不涉及原理）

切記：原理我稍候講，現(xiàn)在我們只看區(qū)別，不說(shuō)原理

我們先忽略filter第2參數(shù)，看看filter(表)與filter(all(表))區(qū)別

完整步驟：

【度量值】平均銷量 = AVERAGE('銷售表'[銷量])

【度量值】近3天移動(dòng)平均1 = CALCULATE([平均銷量],FILTER('日期表','日期表'[日期]>MAX('日期表'[日期])-3))

【度量值】近3天移動(dòng)平均2?= CALCULATE([平均銷量],FILTER(ALL('日期表'),'日期表'[日期]>MAX('日期表'[日期])-3))

解釋：以A商品為例

平均銷量：A商品銷量之和除以15天，就是平均銷量。（2021/1/1~2021/1/15 共15天）

近3天的移動(dòng)平均：A商品在2021/1/13~2021/1/15這3天的銷售之和除以3。

Ps: 關(guān)于移動(dòng)平均在業(yè)務(wù)上的應(yīng)用不屬于我們的DAX原理知識(shí)。

到目前為止，你看不到filter(表)與filter(all(表))的區(qū)別

如果你只是想看每種商品最近3天移動(dòng)平均值是多少，這兩種寫法都OK

可是，當(dāng)你想看具體每一天的近3日移動(dòng)平均值時(shí)，第1種寫法就不OK了

我們將'日期表'[日期]放到行標(biāo)題上

發(fā)現(xiàn)2個(gè)問(wèn)題：

【1】【度量值】近3天移動(dòng)平均1?= CALCULATE([平均銷量],FILTER('日期表','日期表'[日期]>MAX('日期表'[日期])-3))

通過(guò)日期表中的日期字段進(jìn)行篩選后，第一種寫法其實(shí)與【平均銷售】每日顯示值相同，因?yàn)槟阌玫腇ilter(表)，就證明你那張表可以篩選，如你所愿，日期表可以篩選這個(gè)度量值，每天的銷量是多少就是多少，只是這個(gè)度量值【總計(jì)】算的是近3天的移動(dòng)平均值。

【2】【度量值】近3天移動(dòng)平均2?= CALCULATE([平均銷量],FILTER(ALL('日期表'),'日期表'[日期]>MAX('日期表'[日期])-3))

可是第二種寫法也不對(duì)呀？我們的確使用了Filter(all(表))限制了這張表的篩選功能。但是當(dāng)我們倒著看，A商品在1月15日算近3天的移動(dòng)平均值=14這是正確的，可是1月14日的近3天平均值，他計(jì)算的是1月12日至1月15日近4天的移動(dòng)平均值，正確的應(yīng)該是計(jì)算1月12日至1月14日。以此類推上面都是這樣。我們需要的是每個(gè)日期做為最大值，求近3天的移動(dòng)平均值

處理方法如下：

四、Filter為什么要限定第二條件

秘密在這里，我們將日期表中的日期列放到行標(biāo)題上篩選，會(huì)這樣顯示

我們通常的方法是這樣操作的：點(diǎn)下箭頭，選擇日期

就是因?yàn)檫@樣的習(xí)慣，讓我們錯(cuò)過(guò)了真相，其實(shí)每個(gè)人都可以成為江戶川柯南，但最終大多數(shù)人變成了毛利小五郎。

我們點(diǎn)叉子只留一個(gè)日（就是每一天）

當(dāng)我們?cè)俅握归_A商品時(shí)，真相出現(xiàn)了

明明只有15天，為什么第2個(gè)度量值16日至31日還有數(shù)據(jù)？

原因：你使用的是filter+all聲明了日期表任何字段都無(wú)法篩選，所以日期表上只有15天和你的【度量值】近3天移動(dòng)平均2 有毛線的關(guān)系？

處理方法：你需要限定不能超過(guò)日期表中的最大日期

【度量值】近3天移動(dòng)平均3 = CALCULATE([平均銷量],FILTER(all('日期表'),'日期表'[日期]>max('日期表'[日期])-3?&& '日期表'[日期]<=MAX('日期表'[日期])))

現(xiàn)在4個(gè)度量值：

【平均銷量】"總計(jì)"算的是平均值，日期篩選是每一天的銷量。

【近3天移動(dòng)平均1】"總計(jì)"算的是最后3天的平均值，日期篩選是每一天的銷量。

【近3天移動(dòng)平均2】"總計(jì)"算的是最后3天的平均值，日期篩選是錯(cuò)的。

【近3天移動(dòng)平均3】"總計(jì)"算的是最后3天的平均值，日期篩選是正確的。

我們將4個(gè)度量值放到圖表上，切片器篩選商品A

每一天的【平均銷量】與【近3天移動(dòng)平均1】重合了

【近3天移動(dòng)平均2】是錯(cuò)的，我們需要的是【近3天移動(dòng)平均3】也就是紫色的線

五、Filter(表)與Filter(ALL(表))的原理

我一直跟大家強(qiáng)調(diào)，不是哪里疼就一定要治哪里，有的時(shí)候頭疼治腳，有的時(shí)候腿疼治腰。

我的想法：

【1】理解表、all表、all列

《DAX神功》第1卷第8回?基礎(chǔ)表函數(shù)之ALL與ALLEXCEPT函數(shù)

filter(表) 這張表中所有字段都可以篩選

filter(all(表))??這張表中所有字段都不可以篩選

filter(all(表[列]))??這張表中指定字段不可以篩選

Ps: 這個(gè)若不理解，可以棄坑了。

【2】英語(yǔ)只能看懂文字，數(shù)學(xué)才能理解原理

很多學(xué)習(xí)好的人上數(shù)學(xué)課犯困的時(shí)候就是老師講證明，講等價(jià)公式時(shí)。什么時(shí)候興奮？套公式做題得高分時(shí)，好同學(xué)都興奮了。他們不知道，自己重復(fù)勞動(dòng)做的題，計(jì)算機(jī)1秒可以做他幾年的計(jì)算量，而老師講的證明和等價(jià)公式是邏輯，是未來(lái)工作中隱形的工具。

寫等價(jià)公式很重要：

剛才上面那4個(gè)度量值是不是只有【近3天移動(dòng)平均3】是我們需要的且正確的？

【度量值】近3天移動(dòng)平均3 = CALCULATE([平均銷量],FILTER(all('日期表'),'日期表'[日期]>max('日期表'[日期])-3?&& '日期表'[日期]<=MAX('日期表'[日期])))

我們就寫它的等價(jià)度量值：

近3天移動(dòng)平均4 =?

? ? var m=max('日期表'[日期])

return

? ? CALCULATE([平均銷量],'日期表'[日期]>m-3 && '日期表'[日期]<=m,all('日期表'))

Ps:注意我們之前一直強(qiáng)調(diào)的懶惰計(jì)算

公式解釋：

Calculate的篩選器是布爾類型時(shí)，它的條件列就是取消篩選，也就是說(shuō)日期表中的日期列不能篩選。

我們的案例日期表只有日期這一個(gè)列，所以不寫all('日期表')也是一樣的，但是真實(shí)的操作中，你的日期表肯定有很多列，所以限制日期表所有列，我們必須加上all('日期表')

然后我們看看結(jié)果：一樣，一模一樣

截止到現(xiàn)在，F(xiàn)ilter(ALL(表))是怎么回事你應(yīng)該明白了吧？我再看一下它的計(jì)算過(guò)程

剛才我們一直是倒著說(shuō)的，現(xiàn)在我們正著說(shuō)：

filter是迭代函數(shù)，逐行計(jì)算，從第1行開始

（1）1日，銷量為1，前面沒(méi)有日期，所以? 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】最后我們?cè)僬f(shuō)說(shuō)Filter(表)

【度量值】近3天移動(dòng)平均1 = CALCULATE([平均銷量],FILTER('日期表','日期表'[日期]>MAX('日期表'[日期])-3))

我們可以將filter第2個(gè)條件也加上，效果是一樣的

【度量值】近3天移動(dòng)平均1 = CALCULATE([平均銷量],FILTER('日期表','日期表'[日期]>MAX('日期表'[日期])-3 && '日期表'[日期]<=MAX('日期表'[日期])))

我們寫出【近3天移動(dòng)平均1】的等價(jià)公式：

近3天移動(dòng)平均5 =?

? ? var m=max('日期表'[日期])

return

? ? CALCULATE([平均銷量],KEEPFILTERS('日期表'[日期]>m-3 && '日期表'[日期]<=m))

Ps:我們恢復(fù)了日期表中日期列的篩選功能，又沒(méi)有限制其它列，是不是日期表所有列均可以篩選？果然效果一致。

詳見(jiàn)《DAX神功》第1卷第17回?KEEPFILTERS函數(shù)調(diào)節(jié)器與Values做篩選器

今天我們講的是理想狀態(tài)下的情況，銷售表中日期都是連續(xù)的，不連續(xù)的情況如何處理？我們下節(jié)課再說(shuō)。

《孫興華講PowerBI火力全開》PowerBI必學(xué)課程

https://www.bilibili.com/video/BV1qa4y1H7wp

《DAX神功》文字版合集：

https://www.bilibili.com/read/readlist/rl442274

《DAX神功》視頻版合集：

https://www.bilibili.com/video/BV1YE411E7p3

PowerBI(DAX函數(shù))、PowerQuery(M函數(shù))、Python辦公自動(dòng)化、Python爬蟲、Python數(shù)據(jù)分析、ExcelVBA、WordVBA、AccessVBA、MySQL等等

https://www.bilibili.com/read/cv10222110