手機站首頁散文詩歌雜文隨筆日記小小說

散文網(wǎng) » 科技 »學習 » Power BI之DAX神功：答網(wǎng)友問10 計算移動總計必先理解日期區(qū)間

Power BI之DAX神功：答網(wǎng)友問10 計算移動總計必先理解日期區(qū)間

2021-10-26 14:05 作者:孫興華zz 0人讀過 | 我要投稿

一、網(wǎng)友提問

二、學習指導

還記得 《DAX神功》第2卷第3回計算移動平均值再次理解Filter+ALL 嗎？

那是我們在學習時間智能函數(shù)以前，計算移動平均值的方法

那篇文章一開始就告訴大家，關(guān)于計算移動平均值大家可以使用《孫興華講PowerBI火力全開》筆記 27.03? DATESINPERIOD函數(shù)

三、DATESINPERIOD函數(shù)詳解

語法：DATESINPERIOD ( <日期列>, <起始日期>, <偏移量>, <間隔單位>)

返回：給定區(qū)間中的所有日期組成的單列形式的表

詳細案例請看《火力全開》，這里我們只講原理

以下表為例：

例：從最大日期向前平移12個月

利用新建表測試：

表1 = DATESINPERIOD ('Sheet1'[日期], MAX ('Sheet1'[日期]), -1, YEAR )

表2 = DATESINPERIOD ('Sheet1'[日期], MAX ('Sheet1'[日期]), -12, MONTH )

Ps: 可以通過向前1年或向前12個月實現(xiàn)

我們?nèi)庋劭催@張表，是不是取2019年7月1日~2020年6月1日(原表最大日期)這期間的所有日期

四、回答你在看書時遇到的疑惑

在《火力全開》筆記27課時我們講了Datesbetween取某日期列中的區(qū)間值，書中是用這個函數(shù)來代替DATESINPERIOD，讓你理解它的計算過程。說白了就是找到開始和結(jié)束日期取這一段日期的區(qū)間。

語法：Datesbetween(表[日期列],開始日期,結(jié)束日期)

1、結(jié)束日期我們可以通過MAX或LASTDATE得到：

表3= Datesbetween ('Sheet1'[日期],DATE(2019,7,1),MAX('Sheet1'[日期]))

表4 = Datesbetween ('Sheet1'[日期],DATE(2019,7,1),LASTDATE('Sheet1'[日期]))

以上兩個表結(jié)果相同

問：網(wǎng)友又會產(chǎn)生疑問，《火力全開》第27課補充1：LastDate與max的區(qū)別 中講到，MAX返回一個值，LastDate返回一張表，為什么這里LastDate可以代碼結(jié)束日期？

答：詳見《DAX神功》第1卷第10集將表做標量值，LastDate返回最大日期那張表，最大日期只能有一個，所以是可以返回標量值的。即便你是多端表，有多個日期都是2020/6/1，LastDate函數(shù)最終只返回一個值的表。

2、開始日期的獲取有問題，是因為你沒有用日期表

《The Definitive Guide to DAX》上講的獲取開始日期的方法，只能獲取連續(xù)日期

PS: 《DAX神功》答網(wǎng)友問08中提到時間智能函數(shù)第一參數(shù)可以是只有日期列的表，也可以是日期列。

因為我們表中沒有2019年6月2日，上面公式返回的是空，當你用上面的公式做篩選條件時，相當于沒有篩選條件，所以返回整個表的銷售（1+2+...+15）=120? 而不是114

在實際工作中，這樣不連續(xù)的時間日期經(jīng)常發(fā)生

我們需要的是2019年7月1日，在這個案例上你使用NEXTMONTH是可以得到2019/7/1

如果我的Sheet1表中最大日期是2020/6/2，你用上述方法就行不通了

現(xiàn)在你體會到DATESINPERIOD函數(shù)存在的意義了嗎？

書中之所以這樣寫，因為他使用了日期表，日期表的時間肯定是連續(xù)的，當你使用日期表時，它就會找到2019/6/2~2020/6/1的區(qū)間做篩選條件，由于我表中是從2019年7月1日開始的，所以2019/6/2~2019/6/30不會返回任何值。就好比有三個都是2000年出生的人，我讓你從他們當中找到1980~2010年之間出生的，你還是找到他們3個。

五、求移動平均值

在《火力全開》中，我們學會了計算指定天數(shù)的移動平均值，也是最常用的，下表為《火力全開》筆記27課截圖：

但是，你們想過沒有，求30天、15天、7天的移動平均，是固定的數(shù)值，我們可以直接除以指定數(shù)值，其實還有一種省去了再除以固定值的方法。但是你要注意單位。

例如，向前平移一年，就是移動了12個月，這時，我會要考慮年+月才能確定有多少個月

由于我們只使用了一張表，沒有新建日期表，我們就在Sheet1中新建列，制造年月

【新建列】年月 = year('Sheet1'[日期])*100 + MONTH('Sheet1'[日期])

因為我們的表格是一端表，在實現(xiàn)工作中大多數(shù)情況是多端表，所以要考慮年月列的去重計數(shù)

Distinctcount('Sheet1'[年月])? //可以計算去重后的年月列有多少行，就是總計有多少個月

（1+2+...+15）/?Distinctcount('Sheet1'[年月])? //月平均銷售

綜上所述：我們可以將度量值寫成下面兩種方式：

近12個月的移動平均1 = CALCULATE([總銷售],DATESINPERIOD('Sheet1'[日期],MAX('Sheet1'[日期]),-1,YEAR))/12

近12個月的移動平均2 = CALCULATE(DIVIDE([總銷售],DISTINCTCOUNT(Sheet1[年月])),DATESINPERIOD('Sheet1'[日期],MAX('Sheet1'[日期]),-1,YEAR))

原理：DIVIDE([總銷售],DISTINCTCOUNT(Sheet1[年月])做為Calculate的計算器，其實它本身就是度量值。

【近12個月的移動平均1】是先計算區(qū)間值總和再除12。

【近12個月的移動平均2】在計算器內(nèi)進行平均值的計算，根據(jù)篩選器的月份總數(shù)篩選對哪些月份進行計算。

我們驗算一下：（1+15）*15/2=120-（1+2+3）=114/12=9.5

友情提示: 【近12個月的移動平均1】在生成度量值后，它的數(shù)據(jù)類型可能是整數(shù)，因為我之前的數(shù)值都是整數(shù)類型，所以結(jié)果可能是10，需要修改數(shù)據(jù)類型為十進制數(shù)字，保留小數(shù)點后2位。然而近12個月的移動平均2就不會有這個問題，因為在做安全除法時，必然會出現(xiàn)小數(shù)點，數(shù)據(jù)類型已經(jīng)自動改變。