前年比ってよく使いますよね。Power BI の場合「クイック メジャー」を使用することで簡単に作ることができるのですが、意外とそれを説明している記事に出会えないようなので、解説してみます。
クイック メジャーとは
まずは公式ドキュメントから
簡単に説明すると「よく使う計算式だけど、DAX をイチから書いてメジャーを作るのは大変だから、お手軽に作れるようにしたよ」って感じの機能です。
暗黙的なメジャー
Power BI では数値データをビジュアルに追加すると暗黙的にメジャーを作ってくれる (「Σ」のアイコンで表示) ので、わざわざメジャーを作らなくても合計値を表示してくれます。ただ「前年売上」とか「前年比」とか、より複雑な集計用のメジャーを作るときのためや、Excel で分析するために、単純な集計を行うものもメジャーとして作っておくのがおすすめです。
というわけで、サンプルデータには「売上」という列があるのですが、合計する「売上計」というメジャーを定義します。
売上計 = SUM('売上テーブル'[売上])
クイック メジャーを使ってみる
「売上計」について、前年比を出したいなーというときに使えるのが、クイック メジャーです。「売上計」を右クリックして [新しいクイック メジャー] を選択すると [クイック メジャー] ペインが現れます。
[計算を選択してください] のプルダウン リストから「タイム インテリジェンス」の下にある「前年比の変化」を選択し、[基準値] に先程作った「売上計」、[日付] に「日付テーブル」の「Date」、[期間数]には1年前と比較したいので「1」を入力します。
[追加]ボタンをクリックすると、次のように定義されたメジャーが「売上テーブル」に作成されます。
売上計 YoY% =
VAR __PREV_YEAR = CALCULATE([売上計], DATEADD('日付テーブル'[Date], -1, YEAR))
RETURN
DIVIDE([売上計] - __PREV_YEAR, __PREV_YEAR)
タイム インテリジェンス の前提条件
注意が必要なのは「タイム インテリジェンス」にある計算を行うためには次の条件があることです。
- "日付列" と呼ばれる、日付 (または 日付/時刻) データ型の列が必要です。
- 日付列には一意の値が含まれている必要があります。
- 日付列に空白を含めることはできません。
- 日付列に欠落している日付があってはなりません。
- 日付列は年間全体にわたっている必要があります。 1 年は必ずしも暦年 (1 月から 12 月) ではありません。
- 日付テーブルには日付テーブルとしてマークされている必要があります。
日付テーブルについては、Qiita にたくさんの記事があるので割愛します。公式ドキュメントはこちらです。
作成されたメジャーの説明
もう出来上がったのでおーわり！ではなく、作成されたメジャーの中身を理解しましょう。
売上計 YoY% =
VAR __PREV_YEAR = CALCULATE([売上計], DATEADD('日付テーブル'[Date], -1, YEAR))
RETURN
DIVIDE([売上計] - __PREV_YEAR, __PREV_YEAR)
| 行 | 説明 |
|---|---|
| 1 | メジャーの名前。基準値に「売上計」を指定したので「売上計 YoY%」となる。「YoY」は Year over Year の略で前年比を意味します。 |
| 2 | 左辺「VAR」 で変数名「__PREV_YEAR」を宣言し、右辺で計算した前年売上を格納しています。 |
| 3 | この「RETURN」に続くのがメジャーの計算式。 |
| 4 | DIVIDE() 関数を使って比率を計算。 |
2行目の左辺：VAR で変数を宣言
VAR 変数名 = 式
名前付きの変数として、右辺の式の結果を格納します。
変数を使用することで、次のような利点があります。
- パフォーマンスの向上
- 読みやすさの向上
- デバッグの簡略化
- 複雑さの軽減
詳細はこちらをご覧ください。
2行目の右辺：CALCULATE() と DATEADD() で前年売上を計算
CALCULATE([売上計], DATEADD('日付テーブル'[Date], -1, YEAR))
よく使う DAX (たぶん) No.1 の CALCULATE() 関数です。変更されたフィルター コンテキストでテーブル式を評価します。きちんとドキュメントを読み込んでいただきたいのですが、ざっくり説明すると、第1引数に集計値、第2引数以降は集計する際の絞り込み条件を入れます。最初に第2引数（複数条件）でテーブルフィルターによる絞り込みが行われ、その結果（条件で絞られたテーブルのサブセット）をベースに、第1引数の集計値が評価されます。 引数に入れた絞り込み条件以外の絞り込み (フィルターやスライサー、グラフの軸など) は効きません。
訂正：取り消し線部分のような動きになるのは引数に ALL() などの関数を使う場合なので、削除しました。
ここでは第2引数として DATEADD('日付テーブル'[Date], -1, YEAR) が入っています。
DATEADD() 関数は、現在のコンテキストの日付から、指定された間隔数だけ時間を前後にシフトした日付の列を含むテーブルを返します。
このコンテキストという言葉が効き慣れなくて思考停止しがちなのですが、フィルターだったりスライサーだったり、グラフの軸だったり、そういった様々なデータを切り取る条件、みたいなもののことを表す、と私は理解しています。作るレポートやビジュアルや、その時の操作によって常に変わるものなのが、分かりにくい原因なのかなと思います。
ここでは日付テーブルの「Date」列に対して、YEAR 単位で「-1」つまり「1年前」の日付をテーブルとして返します。なので、スライサーなどで「2022年」が選択されていれば、2021年の日付を返します。
「前年売上」というメジャーを次のように定義すると、レポートでは下図のようになります。
前年売上 = CALCULATE([売上計], DATEADD('日付テーブル'[Date], -1, YEAR))
上記のメジャーで「YEAR」を「MONTH」に書き換えれば「前月売上」、「DAY」とすれば「前日売上」となります。とても便利な関数です。
4行目：DIVIDE() で前年比を計算
DIVIDE() 関数は割り算を行う関数で、DIVIDE(割られる数, 割る数) という具合です。
DIVIDE([売上計] - __PREV_YEAR, __PREV_YEAR)
上記は、割られる数が「[売上計] - __PREV_YEAR」、割る数が「__PREV_YEAR」です。
「__PREV_YEAR」は変数で、前年売上を表していました。
よって、(売上計 - 前年売上) ÷ 前年売上 を表しています。
DIVIDE() の代わりに「/」を使えばいいじゃん、というのも間違いではないのですが、DIVIDE() 関数は割る数が「0」だった場合の処理が織り込まれています。それがなぜ良いのかはこちらのドキュメントに書かれていますので、ぜひご覧ください。
まとめ
- クイック メジャーは便利ですが、中身をきちんと理解しましょう。ものによってはイマイチな DAX を返すことがあります。。。
- いっぱいリンク貼りましたが、ぜひ読んでください！