先輩、去年と今年の売上を比較したいんですが、毎回、前年のデータを探して集計するのが大変です……。
それなら、Power Pivotのタイムインテリジェンス関数を使ってみよう！ 前年や前月の売上、年間累積まで効率よく集計できるよ。
Power Pivotで売上分析をしていると、「去年の同じ月と比べてどうだった?」「先月より増えた?」と聞かれることがよくありますよね。
でも、こうした前年同月比や前月比の集計は、普通のフィルター操作だけでやろうとすると、意外と手間がかかります。毎回、期間を切り替えて、数字を見比べて……となりがちです。
そんなときに頼りになるのが、DAXのタイムインテリジェンス関数です。前年や前月のデータを、短い式で簡単に取り出せます。
この記事では、次の内容を順番に解説します。
- タイムインテリジェンス関数を使うための準備
- 前年・前月の売上や年間累積を集計するDAXの書き方
- うまく集計できないときの確認ポイント
「DAXは難しそう」という方も大丈夫です。画面を見ながら一つずつ進めていきましょう!
タイムインテリジェンス関数を使う前準備をしよう
前年や前月のデータを簡単に集計できる「タイムインテリジェンス関数」。便利な関数ですが、使い始める前にちょっとした準備が必要です。
ここでは、正しく集計できる状態をつくるための手順を、順番に見ていきます。
やることは、大きく次の3つです。
- 集計したいテーブルをデータモデルに追加する
- カレンダーテーブルを作成する
- リレーションシップ(テーブル同士の関連付け)を作成する
集計したいテーブルをデータモデルに追加する
最初に、売上データなど、集計の対象になるテーブルをデータモデルに追加します。
Power Queryで取り込んだテーブルなら、読み込みの際に「このデータをデータモデルに追加する」を設定にしておくとスムーズです。
タイムインテリジェンス関数は、Power Pivotのデータモデル上で使います。対象のテーブルがデータモデルに登録されているか、確認しておきましょう。
カレンダーテーブルを作成する
タイムインテリジェンス関数を使うには、日付を管理するための「カレンダーテーブル」が必要です。ここでは「Power Pivotの管理画面」で作成します。
カレンダーテーブルの条件
カレンダーテーブルには、日付が重複せず、途中の抜けもない連続した日付を用意します。ここがとても大事なポイントです。
既存テーブルの日付列を使う方法もありますが、専用のカレンダーテーブルを作っておくのがおすすめです。日付だけでなく、次のような項目も管理しやすくなります。
- 年
- 四半期
- 月
- 曜日
これらを使えば、年別・月別の集計や曜日ごとの分析など、いろいろな切り口でデータを見られるようになります。
テーブル名を変更する
Power Pivotでカレンダーテーブルを新規作成すると、初期状態では「予定表」という名前になります。この記事では分かりやすいように、テーブル名を「カレンダー」に変更しています。
「月」列を日本語表記にする
自動で作成された「月」列は、英語表記になっている場合があります。「1月」「2月」のように日本語で表示したいときは、計算列の式を次のように変更しましょう。
=FORMAT([Date],"M月")これで、月が日本語で表示されるようになります。
リレーションシップを作成する
続いて、カレンダーテーブルと集計対象のテーブル（売上データ）を関連付けます。この作業はダイアグラムビューで行います。
たとえば、販売テーブルに「販売日」という列がある場合は、次の2つの列をリレーションシップで接続します。
- 販売テーブルの「販売日」
- カレンダーテーブルの「Date」
タイムインテリジェンス関数は、この日付の関連付けを使って、前年や前月のデータを取り出しています。
リレーションシップの編集画面は、こんな感じです。
リレーションシップが正しく設定されていないと、期待どおりの集計結果にならないことがあります。ここもしっかりと確認しておきましょう。
今回使うテーブルの確認
この記事では、売上データをまとめた「販売テーブル」を使います。販売テーブルには、少なくとも次の項目があるものとします。
- 販売日
- 売上
そして、先ほど作成したカレンダーテーブルがこちらです。
この販売テーブルとカレンダーテーブルを関連付けた状態で、実際に集計していきます。
タイムインテリジェンス関数でデータを集計する
タイムインテリジェンス関数を使うための準備(データモデル、カレンダーテーブル、リレーションシップ)ができたら、いよいよ実際に関数を使って集計していきましょう。
前年・前月の売上や年間累積など、よく使うパターンを順番に紹介します。
まずは基本の「売上金額」メジャーを作る
今回は、販売テーブルの「売上」列を集計して、前年や前月の売上を求めていきます。
最初に、売上を集計する基本のメジャーを作成します。この式自体はタイムインテリジェンス関数ではありませんが、このメジャーを土台にして、前年や前月などの売上を計算していきます。
売上金額:=SUM('販売テーブル'[売上])前年売上を集計する
前年の同じ期間の売上は、SAMEPERIODLASTYEAR 関数で集計できます。
前年売上:=CALCULATE(
[売上金額],
SAMEPERIODLASTYEAR(
'カレンダー'[Date]
)
)SAMEPERIODLASTYEAR は、現在の集計対象期間を「前年の同じ期間」に置き換えた日付のテーブルを返してくれる関数です。
たとえば、ピボットテーブルで2022年の売上を集計していれば、前年売上には2021年の売上が表示されます。
下の図で2021年の前年売上が空欄になっているのは、前年(2020年)のデータが販売テーブルに無いためです。エラーではないので、安心してください。
この関数は期間を自動で判断してくれるのも便利なところです。月別に集計していれば前年の同じ月、日別に集計していれば前年の同じ日付が、それぞれ基準になります。
前年差額と前年比も求めてみよう
前年売上が求められるようになれば、前年との差額や前年比も簡単に計算できます。
前年差額は、今年の売上から前年の売上を引いて求めます。
前年差額:=[売上金額] - [前年売上]前年比は、今年の売上を前年の売上で割って求めます。
前年比:=DIVIDE(
[売上金額] ,
[前年売上]
)
割り算には「 / 」ではなく DIVIDE 関数を使うのがおすすめです。前年の売上が「0」や空白のときでもエラーにならず、扱いやすくなります。前年比をパーセントで表示したい場合は、セルの表示形式を「パーセンテージ」に設定しておきましょう。
このように、タイムインテリジェンス関数を使えば、前年との比較に必要なメジャーも簡単に作れます。
前月の売上を集計する
次は、前月の売上です。前月のデータを取得するには、PREVIOUSMONTH 関数を使います。
前月売上:=CALCULATE(
[売上金額],
PREVIOUSMONTH(
'カレンダー'[Date]
)
)PREVIOUSMONTH は、現在の集計対象期間を基準にして、前月の日付全体を返す関数です。
たとえば、ピボットテーブルで2021年2月の売上を集計していれば、前月売上には2021年1月の売上が表示されます。
これで、前月との比較もばっちりできます。
好きな期間だけ前後に動かして集計する(DATEADD)
ここまで紹介した関数のほかに、よく使うものとして DATEADD 関数があります。日付を好きな期間だけ前後にずらして集計できる、柔軟な関数です。
たとえば、前々年の売上を求めたいときは、次のように書きます。
前々年売上:=CALCULATE (
[売上金額],
DATEADD ( 'カレンダー'[Date], -2, YEAR )
)
SAMEPERIODLASTYEAR は「前年の同じ期間」に固定された関数ですが、DATEADD なら、ずらす期間や方向を自分で指定できます。
指定の例は、次のとおりです。
-1, YEAR：1年前-1, QUARTER：1四半期前-1, MONTH：1か月前-7, DAY：7日前
年間累積を集計する
続いて、年間累積の売上を集計してみましょう。
年間累積とは、年の初めから、現在の集計対象日までの売上を合計したものです。たとえば4月の年間累積なら、1月〜4月の売上の合計になります。
月ごとの売上だけでなく、年初からどのくらい売上が積み上がっているかが分かるので、売上の進捗を確認するのにとても便利です。
年間累積は、TOTALYTD と DATESYTD の2つの関数で計算できます。
TOTALYTD を使う方法
まずは TOTALYTD から。
年間累積_TOTALYTD:=TOTALYTD(
[売上金額],
'カレンダー'[Date]
)TOTALYTD は、年初から現在の集計対象日までを対象に、指定したメジャーを集計する関数です。今回は基本メジャーの [売上金額] を使って、年初からの累積売上を求めています。
DATESYTD を使う方法
もうひとつは、DATESYTD を使う方法です。
年間累積_DATESYTD:=CALCULATE(
[売上金額],
DATESYTD('カレンダー'[Date])
)DATESYTD は、年初から現在の集計対象日までの日付のテーブルを返す関数です。それを CALCULATE に渡すことで、その期間の売上を集計できます。
ピボットテーブルで月別に集計すると、次のような結果になります。
- 1月：1月の売上
- 2月：1月～2月の売上
- 3月：1月～3月の売上
- 4月：1月～4月の売上
月が進むにつれて、売上が積み上がっていくのが分かりますね。
決算期が1月始まりじゃない場合は?
年度が4月始まり(3月決算)など、1月〜12月ではない場合は、year_end_date という引数で「1年の終わり」を指定できます。
年間累積_TOTALYTD:=TOTALYTD(
[売上金額],
'カレンダー'[Date],
"3/31"
)年間累積_DATESYTD:=CALCULATE(
[売上金額],
DATESYTD(
'カレンダー'[Date],
"03/31"
)
)
TOTALYTD と DATESYTD、どっちを使う?
2つの違いは、次のとおりです。
- DATESYTD:「日付のテーブルを作る」ことだけを担当する関数
- TOTALYTD:
CALCULATEと「日付のテーブルを作る」処理が1つにまとまった関数
TOTALYTD のほうが書き方はシンプルです。一方で、ほかの条件と組み合わせたり、あとから数式をメンテナンス・拡張したりしそうなときは、CALCULATE + DATESYTD が向いていると思います。
タイムインテリジェンス関数がうまく動かないときの確認ポイント
メジャーを作ってピボットテーブルに配置したのに、結果が空欄になる、エラーが出る……。タイムインテリジェンス関数を使っていると、こんな場面に出会うことがあります。
「式は合っているはずなのに、なんで?」と悩んだときのために、この記事ではよくある原因と確認ポイントをまとめました。うまく集計できないときのチェックリストとして、ぜひ活用してください。
ここをチェック!
エラーと原因は、大きく次の4つに分けられます。
- メジャーの結果が空欄になる・表示位置がおかしい
- カレンダーテーブルを使っていない(または、カレンダーテーブルに問題がある)
- カレンダーテーブルの日付列を使っていない
- ピボットテーブルで、日付に飛び飛びのフィルターをかけている
メジャーの結果が空欄になる・表示位置がおかしい
前年売上を作ったのに、ピボットテーブルで空欄になる、あるいは前年と今年の数字が意図した位置に表示されないことがあります。
そんなときは、次の点を確認してみてください。
エラーメッセージもヒントになります
エラーが表示されたときは、メッセージの内容も大切な手がかりです。
たとえば、「テーブル間のリレーションシップが必要である可能性があります」というメッセージが出たら、テーブル間のリレーションシップ(関連付け)を確認しましょう。
カレンダーテーブルを使っていない
最初に確認したいのは、カレンダーテーブルの状態です。
タイムインテリジェンス関数を使うには、原則として、日付が重複せず、欠けることなく連続しているカレンダーテーブルが必要です。
カレンダーテーブルが次のような状態になっていないか、チェックしてみてください。
こうした問題があると、関数によってはエラーになったり、期待した結果にならなかったりします。
カレンダーテーブルを用意したら、日付が正しく並んでいるかどうかも、あわせて確認しておきましょう。
カレンダーテーブルの日付列を使っていない
次に確認したいのが、どの日付列を使っているかです。
たとえば前年売上を計算するとき、カレンダーテーブルではなく、販売テーブルの日付列を指定してしまうケースがあります。
これはメジャーの式だけでなく、ピボットテーブルの「行」や「列」に日付を置くときも同じです。ここでカレンダーテーブルを使わないと、思ったように集計されません。
タイムインテリジェンス関数では、カレンダーテーブルの日付列を指定するのが基本です。ピボットテーブルで年別・月別に集計するときも、カレンダーテーブルの年・月・日付などの列を使いましょう。
販売テーブルの日付列を直接使ってしまうと、カレンダーテーブルとの連携がうまく働かず、前年や前月の結果が正しく表示されないことがあります。
メジャーの式が合っているように見えても、ピボットテーブル側の指定でつまずいていることもあります。「式は合っているのに結果がおかしい」ときは、日付列の指定を確認してみてください。
カレンダーテーブルの日付にフィルターをかけている
ピボットテーブルで、カレンダーテーブルの日付列にフィルターをかけている場合も要注意です。
たとえば、日付を一部だけ選んで、連続していない日付が集計対象になると、DATEADD などの関数でエラーが発生することがあります。
タイムインテリジェンス関数は、連続した日付を前提に動いています。エラーが出たときは、フィルターの設定を見直してみましょう。
まとめ
今回は、Power Pivotのタイムインテリジェンス関数を使って、前年・前月の売上や年間累積を集計する方法を紹介しました。
タイムインテリジェンス関数を使えば、面倒な日付の計算を自分で組み立てなくても、期間を指定した集計がサクッとできます。一見むずかしそうですが、カレンダーテーブルとリレーションシップさえ整えておけば、短い式で前年比較や累積が作れる、とても頼もしい機能です。
まずは手元の売上データで、「前年売上」のメジャーを作るところから試してみてください。日々の集計作業が、きっとラクになりますよ。
最後までお読みいただき、ありがとうございました!
これなら前年との比較もラクラクですね！タイムインテリジェンス関数をマスターして、サクッと集計、今日も定時で帰ります！