移動平均（３か月間） - DATESINPERIOD

移動平均（３か月間） - DATESINPERIOD

目標

今回のテーマは移動平均（単純移動平均）の計算です。

移動平均とは「今ここ」の時点を基準とした一定期間の数値の平均のことです。平均をとる開始日が基準日の移動に伴って変化するのが特徴です。

移動平均を使うことで、短い期間の細かなバラツキを丸めて、大局的な数字の傾向を把握することができます。

移動平均には、将来の時間を含めた平均もありますが、今回は過去３か月間の売上移動平均を計算します。（この場合、基準日＝最終日となります。）

技術的なポイント

• 「今ここ」から一定時間さかのぼった日付リストを手にいれる
• そのためにタイムインテリジェンス関数 DATESINPERIODを使う

なお、「分子を計算する」に当たっては、拙著『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』の「第５章 当期売上累計」の当期売上累計メジャーとよく似た計算をします。

ただし、当期売上累計では開始日は常に固定の期初（4月1日）であるのに対し、移動平均では、テーブル上の基準点から常に３か月間さかのぼった日付が開始日となります。つまり、開始日が「今ここ」を起点として相対的に決まるという点が異なります。

実践

ならべるステップ

まず、『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』の「第５章 当期売上累計」のピボットテーブルを以下のようにレイアウト変更してください。

かぞえるステップ

「今ここ」から3カ月間さかのぼった日付リストを手にいれる

一定期間の日付リストを用意するには、DATESINPERIODという関数を使います。

書式
　DATESINPERIOD ( <カレンダーテーブルの日付項目>, <基準日>, <間隔数>, <間隔タイプ> )

アウトプット
　上の指定を受けた日付リスト

この中で注意しなくてはならないのは、<基準日>です。これは基準日なのでデータが複数行あってはいけません。

例えば、ピボットテーブルが８月という月レベルまでドリルアップされ、日にちが非表示である場合、その文脈における「今ここ」の日にちは「8月1日から8月31日」までの日付群となってしまいます。

日付が複数あっては基準点となりえないので、日付を何らかの基準で一つの値＝代表値にする必要があります。複数のデータを受け取って一つの代表値にするにはMIN、MAXといった集計関数を使います。今回は、MAXを使ってその文脈における月末日にします。

それでは、まず以下のメジャーを作って日付リストの中身を確認してみましょう。

なお、移動平均開始日と移動平均終了日については、カテゴリを日付にしてください。

移動平均日数:
=CALCULATE (
COUNTROWS('カレンダー'),
DATESINPERIOD ( 'カレンダー'[日付], MAX( 'カレンダー'[日付] ), -3, MONTH )
)

※　COUNTROWSはその文脈におけるデータ件数を取得する関数です。今回は「今ここ」を起点としてさかのぼった3か月間の日数となります。

移動平均開始日:
=CALCULATE (
MIN('カレンダー'[日付]),
DATESINPERIOD ( 'カレンダー'[日付], MAX ( 'カレンダー'[日付] ), -3, MONTH )
)

※　「今ここ」を起点としてさかのぼった3か月前の日です。

移動平均終了日（基準点）:
=CALCULATE (
MAX('カレンダー'[日付]),
DATESINPERIOD ( 'カレンダー'[日付], MAX ( 'カレンダー'[日付] ), -3, MONTH )
)

※　基準点＝最終日となります。

会計年度、四半期、月、日のそれぞれのレベルで、移動平均日数、移動平均開始日、移動平均終了日（基準点）が正しく表示されていることを確認してください。

なお、ここまで「日付リスト」という言葉を使ってきましたが、ピボットテーブル上ではこのフィルターコンテキスト内の日付を直接見ることはできません。フィルターコンテキストは想像しなければいけない部分があり、それがDAXの理解のハードルを上げています。

したがって、上のように中間メジャーを使ってフィルターコンテキストの中身を可視化しながら実感をもって理解していただくように勧めています。

３か月間の売上を合計する

DATESINPERIODの動作は確認できたので、メジャーでこの期間の売上合計を出します。

移動平均売上合計:
=CALCULATE (
[売上合計],
DATESINPERIOD ( 'カレンダー'[日付], MAX ( 'カレンダー'[日付] ), -3, MONTH )
)

ピボットテーブルで直近３か月の売上合計と移動平均売上合計の値が一致していることを確認してください。

移動平均を求める

最後に、上で作成した移動平均売上合計を３で割って売上移動平均を算出します。

売上移動平均:
=DIVIDE( [移動平均売上合計], 3)

合わせて分母を移動平均日数にした売上移動平均（日）も作ります。

売上移動平均（日）:
=DIVIDE([移動平均売上合計], [移動平均日数])

えがくステップ

最後に、グラフにプロットして売上と売上移動平均の違いを見てみます。

例によって新規にピボットグラフを作成し、以下の設定にしてください。

またグラフの種類は「折れ線グラフ」にします。

スライサーで「商品カテゴリー」を追加して出来上がりです。

上下する売上合計と緩やかな売上移動平均を比較してください。

最終月補正の追加（2020年4月25日追記）

上記までのステップで移動平均が算出されましたが、一点、問題があります。

移動平均の計算が過去3か月であるため、以下の図のように実績が2018年度で終了しているにもかかわらず、移動平均が2019年第一四半期まで伸びている点です。

今回はこの延長を補正するために、実績の最終日までしか移動平均を表示しないように修正します。

まずは実績の最終日を取得するメジャーを作ります。

F_売上明細最終日:
=CALCULATE(MAX('F_売上明細'[日付]), ALL('カレンダー'))

これで売上の入力された最終日を取得しました。ピボットテーブルで確認してみると3月29日で一致していることが分かります。

次にフィルターコンテキスト上のカレンダーの日付とF_売上明細最終日を比較して、F_売上明細最終日より以前である場合のみ移動平均を表示するように売上移動平均のメジャーを変更します。

売上移動平均:
=IF(
MIN( 'カレンダー'[日付] ) <= [F_売上明細最終日],
DIVIDE( [移動平均売上合計], 3 )
)

ちなみにDAXではIF文の条件に一致しなかった場合の数式を省略するとブランクとなり、結果としてピボットテーブル・グラフではブランクは非表示になるので、これをうまく利用します。

これで結果をピボットテーブル・グラフで確認してみます。

ピボットテーブルでは3月29日で移動平均がストップしているのが分かります。

また、ピボットグラフでもきれいに2018年度で折れ線が止まるようになりました。