Power BI -移動平均(Moving Average)の計算／レポートの設定方法

<使用したDAX関数＞
IF(), ISBLANK(), SUM(), CALCULATE(), LASTDATE(), DATESINPERIOD(), SELECTVALUE()

1. 使用するサンプルデータ

今回はMicrosoftのサンプルデータのうち、financials.xlsxを使用します。同じファイルは下記にあります。

Power BI 用の Financial Sample のダウンロード - Power BI

2. Power BI desktopに取り込んで、カレンダーテーブルも作っておく

今回使用するサンプルデータはエクセルブックなので、ここから取り込みます。（サンプルデータはfinancialsというテーブル名があらかじめ設定されているので、それを選択。Query上は何も編集せず、そのまま読み込み）

取り込んだら、（移動平均の計算を説明するだけであれば不要だが、一応実務で使用する場合はカレンダーテーブルを用意していると思うのでそれに合わせて）カレンダーテーブルを別途設定する。

モデリング＞新しいテーブル から、テーブル名dim_periodでカレンダーテーブルを作成します。

dim_period = CALENDARAUTO()

テーブルができたら、リレーションシップを設定します。Dimentionテーブルであるdim_periodからFactテーブルであるfinancialへ、DateとDateを結びつけます。

これで、メジャーを作成する準備が整いました。

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

メジャーを作成する際は、どんなレポートを作りたいのかを先にイメージする必要がある。今回は、financialテーブルのSegment や Country や Product別に、時系列でProfitの移動平均を算出することを目的としてみます。（つまり、グラフのX軸は時間(dim_period)で、縦軸はProfitということ）

（参考）使用するのは緑の列と、黄色の列だけ（その他は、使わないからメジャーの作成方法を理解することが目的なので、消してもOK）

今回の例は移動平均の計算期間(12カ月とする)におけるProfitの合計を、データ数で除算するという単純な内容なので、以下のDAX式でメジャーを作ればいい。（でも、少しだけエラー対処のため工夫している）

Profitの12カ月移動平均 = 
VAR interval = -12
RETURN
   IF (
       NOT ISBLANK ( SUM ( financials[Profit] ) ),
       DIVIDE (
           CALCULATE (
               SUM ( financials[Profit] ),
               DATESINPERIOD (
                   dim_period[Date],
                   LASTDATE ( dim_period[Date] ),
                   interval,
                   MONTH
               )
           ),
           CALCULATE (
               COUNTA ( financials[Profit] ),
               DATESINPERIOD (
                   dim_period[Date],
                   LASTDATE ( dim_period[Date] ),
                   interval,
                   MONTH
               )
           )
       )
   )

※VARを書いたらRETURNを必ず書くこと。

ここで、仮に時系列分析で最も過去に近い時点に差し掛かった時に、過去12か月分のデータがそろわない期間が出てくる。そんな時でもエラーを出さずに、「あるデータだけで」移動平均を算出させるため、IF～NOT ISBLANK～ を入れている。

4. 実際に使う場合は、移動平均の計算区間をレポート上で調整できるようにしておく

VARで設定している変数「-12」は、メジャーに置き換えても機能する。つまり、別途12、24、36 を持つテーブルを作ったら、それをフィルターにして、選択された値の計算期間(12カ月 or 24カ月 or 36カ月)を集計できるようにしてもいい。一応、やってみると以下のようになる。

filter用のテーブル(filter_計算区間)を作成して、

※リレーションは設定しない(フィルターのためだけに使用するテーブルは、たいてい独立して(リレーションシップを持たずに)存在させる)

で、メジャーはこんな感じで適当に修正する。
（修正したのは、VARの箇所だけです。（あと、メジャーの名前も）)

Profitの移動平均 = 
VAR interval = SELECTEDVALUE('filter_計算区間'[計算区間],12)　　//<--ここだけ修正
RETURN
   IF (
       NOT ISBLANK ( SUM ( financials[Profit] ) ),
       DIVIDE (
           CALCULATE (
               SUM ( financials[Profit] ),
               DATESINPERIOD (
                   dim_period[Date],
                   LASTDATE ( dim_period[Date] ),
                   interval,
                   MONTH
               )
           ),
           CALCULATE (
               COUNTA ( financials[Profit] ),
               DATESINPERIOD (
                   dim_period[Date],
                   LASTDATE ( dim_period[Date] ),
                   interval,
                   MONTH
               )
           )
       )
   )

レポートを作成する際は、filter_計算区間をフィルターとして設定し、メインの移動平均は、横軸にカレンダーテーブルのDate、値として上記で作成した[Profitの移動平均]を入れる。

次は、標準偏差をRollingで算出するDAXについて書く予定です。