DAX関数で移動平均のメジャーを作成(AVERAGEX、DATESINPERIOD関数)

DAX関数 移動平均DATESINPERIOD関数DAX関数
スポンサーリンク

PowerBIやPowerQueryでDAX関数を用いて、移動平均のメジャーを作成する方法を紹介します。 移動平均で複雑なデータを滑らかにして、データの傾向を明確に把握していきましょう!

【本記事で得られること】
・DATESINPERIOD関数×AVERAGEX関数で移動平均の計算
・平均区間を変更できる移動平均のテンプレコード
の取得
・FILTER関数×AVERAGEX関数で移動平均の計算

今回紹介するテンプレコードなどを用いれば、簡単に移動平均のメジャーを作成できますので、ぜひ参考にしてみてください!

DAX関数で移動平均の計算(DATESINPERIOD関数)

移動平均を計算するメジャーの作成には、
・DATESINPERIOD関数
・AVERAGEX関数

などのDAX関数を組み合わせる必要があります。

◆移動平均の計算メジャー
実際の売上データを用いた詳しい事例は後述しますが、DATESINPERIOD関数、AVERAGEX関数を用いた移動平均を計算するメジャーは下記コードになります。

売上移動平均(DATESINPERIOD)_30 = 
AVERAGEX(
     DATESINPERIOD('日付テーブル'[Date],LASTDATE('日付テーブル'[Date]),-30,DAY),
         '売上リスト'[売上合計])

日付列の最終日を取得できるLASTDATE関数を使って、その日から30日前までの期間をDATESINPERIOD関数で取得しています。その期間内をAVERAGEX関数で平均値を計算させています。

【DAX関数 DATESINPERIOD】
指定した期間内の日付データを抽出!
DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)

分かりやすく書き換えると…
=DATESINPERIOD(日付列, 開始日, 期間の数, 期間の種類)
・日付列:日付テーブルの日付列を指定
・開始日:期間の開始日

・期間の数:例えば30日前だと、-30を指定
・機関の種類:年、四半期、月、週、日を指定

▶(参照)Microsoft DAX関数 DATESINPERIOD

【DAX関数 AVERAGEX】
指定したテーブルに対しての平均値を返す関数!
=AVERAGEX(<table>, <expression>)
・table:平均値を求めたいテーブルを指定
・expression:平均値を求めたい式やメジャーを指定

▶(参照)Microsoft DAX関数 AVERAGEX

AVERAGEX関数の詳しい使い方については、下記でも解説していますので、ぜひご参考ください!
▶PowerBIで平均値の計算(AVERAGEX関数)

売上と移動平均の折れ線グラフ
作成した移動平均のメジャーを売上の折れ線グラフに追加すれば、傾向を把握できます!

PowerBI 移動平均 DAX関数 AVERAGEX関数 DATESINPERIOD関数

【結論】移動平均の計算はDATESINPERIOD関数、AVERAGEX関数を組み合わせる!

今回は売上データに移動平均のメジャーを作成し、PowerBIでグラフ化する手順を具体的に解説していきます。

スポンサーリンク

移動平均を計算するメジャーの作成

それでは実際にPowerBIに売上データを取り込み、移動平均を計算するメジャーを作成していきます。

取り込むデータは下記の、各日付での来客数と売上のデータです。

PowerBI 取り込みデータ

▶(参考記事)PowerBIへのデータの取り込み

また、PowerBI内にカレンダーテーブルを作成し、取り込んだ売上データと日付列でリレーションさせています。

PowerBI リレーション

カレンダーテーブルの簡単な作成方法、リレーション方法は下記記事をご参考ください。
▶(参考記事)PowerBIで一瞬でカレンダーテーブルを作成する方法
▶(参考記事)PowerBIのリレーションでデータの連携

次に、取り込んだデータをPowerBIの折れ線グラフで
X軸:カレンダーテーブルの日付列
Y軸:売上データの売上合計
を入れると、下記のグラフが作成されます。

PowerBI グラフ化 売上グラフ 折れ線

このままですと、日ごとの売上にバラツキがあるので、ガタガタして見づらいですよね…。そこで、データの変動を滑らかにする移動平均のメジャーを作成して、売上の傾向を把握していきます。

移動平均とは?
データの変動を滑らかにして傾向を把握するために、期間を移動させて平均値を計算する方法

先ほど紹介した
・DATESINPERIOD関数
・AVERAGEX関数
を組み合わせたDAX関数を使って移動平均を計算するメジャーを作成していきます。

新しいメジャーを追加して、下記コードを入力します。今回は期間30日の移動平均を計算しています。

売上移動平均(DATESINPERIOD)_30 = 
AVERAGEX(
     DATESINPERIOD('日付テーブル'[Date],LASTDATE('日付テーブル'[Date]),-30,DAY),
         '売上リスト'[売上合計])

【解説】
DATESINPERIOD関数を使って、30日前からの期間を取得
=DATESINPERIOD(日付列, 開始日, 期間の数, 期間の種類)
日付列:日付テーブルの日付列を指定
開始日:LASTDATE関数で日付列の最終日を取得
期間の数:-30で30日前を指定
期間の種類:DAYで日を指定

その期間内をAVERAGEX関数で平均値を計算
=AVERAGEX(<table>, <expression>)
table:DATESINPERIOD関数で指定した30日前までの期間
expression:売上合計メジャー ※メジャーか式しか指定できないので注意

スポンサーリンク

移動平均のメジャーをグラフに追加

次に作成した移動平均のメジャーを売上の折れ線グラフに追加して確認します。

売上と移動平均の折れ線グラフ
視覚化のビジュアル:「折れ線グラフ」
X軸:カレンダーテーブルの日付列
Y軸:売上データの売上合計、作成した移動平均のメジャー

DATESINPERIOD関数 移動平均 PowerBI DAX

過去30日の期間で平均された移動平均のグラフになっており、データが滑らかになった移動平均線が追加されたことが確認できます。

ちなみに、DATESINPERIOD関数はDATESBETWEEN関数でも代用可能です。

【DAX関数 DATESBETWEEN】
指定した開始日と終了日の範囲内の日付データを抽出!
=DATESBETWEEN(<dates>, <start_date>, <end_date>)

分かりやすく書き換えると…
=DATESBETWEEN(日付列, 開始日, 終了日)
▶(参照)Microsoft DAX関数 DATESBETWEEN

DATESINPERIOD関数を使った日付期間の指定を、DATESBETWEEN関数に代用すればOKです。その場合は、下記コードになります。

売上移動平均(DATESBETWEEN)_30 = 
AVERAGEX(
     DATESBETWEEN('日付テーブル'[Date],LASTDATE('日付テーブル'[Date])-30,LASTDATE('日付テーブル'[Date])),
'売上リスト'[売上合計])
スポンサーリンク

移動平均メジャーのテンプレコード

移動平均のテンプレートコードとして、変数を指定できるVARを用いて、任意に期間を変更可能にしております。もちろん、先ほどのDATESINPERIOD関数の期間の数を直接変更すればOKなのですが、コードが長くなったりすると、どこの数値を変えてよいのか分からなくなるので、VARで変数設定しておいたほうがベターです。

ご自身のPowerBIやPowerQueryなどにコピーしてご活用ください!

売上移動平均(テンプレ) = 
VAR Days = 30
RETURN
AVERAGEX(
    DATESINPERIOD('日付テーブル'[Date],LASTDATE('日付テーブル'[Date]),-Days,DAY),
        '売上リスト'[売上合計])

Days = 30 の数値部分を任意の期間に変更すれば、欲しい移動平均が取得できます。たとえば、下記は30、60の2つのメジャーを作成しグラフに追加したパターンです。

移動平均 PowerBI DAX PowerQuery
スポンサーリンク

DAX関数で移動平均の計算(FILTER関数)

次に、DATESINPERIOD関数やDATESBETWEEN関数を使わない移動平均の計算方法を紹介します。DATESINPERIOD関数を覚えておけば問題ないのですが、より活用頻度が高く簡単なFILTER関数を使った方法を覚えておけば、さらに応用がききます。

考え方は先ほどと同様で、FILTER関数で移動平均したい期間を指定して、AVERAGEX関数で平均計算をしています。
▶(参考記事)DAX関数 FILTER関数の使い方、事例紹介

売上移動平均 = 
VAR LatestDate = MAX('日付テーブル'[Date])
VAR Days = 30
VAR TableLastDays = 
        FILTER(ALL('日付テーブル'[Date]),
            '日付テーブル'[Date] > LatestDate-Days &&
            '日付テーブル'[Date] <= LatestDate)
VAR moving_ave = AVERAGEX(TableLastDays,'売上リスト'[売上合計]) 
Return moving_ave

DATESINPERIOD関数の代用をしている部分はFILTER関数の部分です。
ポイントは、
ALL(‘日付テーブル'[Date]):全てのフィルターを外す
‘日付テーブル'[Date] > LatestDate-Days:-30日以上
‘日付テーブル'[Date] <= LatestDate):現在以下
で期間を指定している部分です。

【結論】FILTER関数を工夫すればDATESINPERIOD関数の代用ができる!

スポンサーリンク

DAX関数で移動平均の計算方法まとめ

DAX関数で移動平均の計算をするポイントは下記です。
・DATESINPERIOD関数×AVERAGEX関数を組み合わせる
・FILTER関数とAVERAGEX関数を組み合わせる

これらの方法は汎用性も高く、活用場面も多いです。テンプレートコードもご活用いただき、ぜひ理解を深めていってください!

今回同様にPowerBIの便利機能とDAX関数をまとめていますので、ぜひこちらもご参考ください!
▶PowerBIの便利機能、DAX関数まとめ

▶よく使うDAX関数まとめ

最後までみていただき、ありがとうございました!
良かったら↓クリック応援をよろしくお願いします!!

にほんブログ村 サラリーマン日記ブログ IT系サラリーマンへ
にほんブログ村

スポンサーリンク

コメント

タイトルとURLをコピーしました