この記事はsqlbiのShowing actuals and forecasts in the same chart with Power BIを参照しています。
備忘も兼ねているので、自分が必要とした部分しか書いていません。
詳しく勉強したい方は参照した記事を是非見てください。
Power BIに限らずExcelのPower Pivotでも出来る内容です。
やりたい事
実績と見込みのデータがあります。
線グラフだとこんな感じ。
これを実績がまだない期間を見込みに置き換えたい
置き換えた線グラフだとこんな感じ。
使うデータと設定
利用するデータセットとリレーションは以下の通り。
リレーション
データセット
見込みテーブルは日付がyyyyMM形式の「年月」列で構成されています。
このままでは日付テーブルとリレーションを組めないので、年月ブリッジという中間テーブルを使ってブリッジさせます。
年月ブリッジ
ここら辺のお作法はPower BIの神が解説済みなのでそちらをどうぞ。
予算実績を集計比較する データモデル パターン
メジャー
事前に定義したメジャーはこの二つ。
実績合計 = SUM('実績'[実績])
見込み合計 = SUM('見込み'[見込み])
どういうデータにすれば出来そうか
こういう形にしてあげれば実績合計と現在の見込みの足し算で表現できそうです。
メジャーの準備
実績の最終日を求める
まずは実績の最終日が分からなければ始まりません。
実績の最終日を求める数式で真っ先に思い浮かぶのは=MAX('実績'[日付])ですが、これは正しく動作しません。
以下の実績最終日Trueは正しく動作します。
何が違うのでしょう？
実績最終日False = MAX('実績'[日付]) ///こっちはNG
実績最終日True = CALCULATE(MAX('実績'[日付]),ALL('実績')) ///こっちはOK
フィルターコンテキストの影響を取り除く
これは日付に何もファイルターが掛かっていない状態です。
どちらも正常に見えます。
しかし日付にフィルターをかけると実績最終日Falseはズレてしましました。
これでは正しく最終日を求められません。
原因はフィルターコンテキストで、これはスライサーなどDAX式の外部から来るフィルターの影響を指します。
テーブルやマトリックスの行や列に使われるディメンション(品名や担当者名など)も、フィルターコンテキストを発生させます。
実績最終日Falseの構文ではフィルターコンテキストの影響を取り除けないのです。
ALL関数を使う
実績最終日TrueではCALCULATEを使いフィルターコンテキストをフィルター式ALL('実績')上書きしているため、常に実績テーブル全体が参照され正しい実績最終日が取得できます。
実績と重複しない範囲の見込みを求める
実績の最終日を求められたので、取り出すべき見込み合計を算出できそうです。
しかし、ExcelライクにDAX式を書こうとしていませんか？
現在の見込み = CALCULATE([見込み合計],'日付テーブル'[Date]>CALCULATE(MAX('実績'[日付]),ALL('実績')))
このDAX式ではエラーが出ます。
これはCALCULATEのフィルター式で、CALCULATEとMAXを使う事が許可されていないためです。
実績最終日を変数に格納する
実はCALCULATEのフィルター式に直接使う事が許可されていないだけで、一度メジャーか変数にしてしまえば問題無く使えます。
現在の見込み =
VAR LastDateWithActual=CALCULATE(MAX('実績'[日付]),ALL('実績'))
RETURN
CALCULATE([見込み合計],'日付テーブル'[Date]>LastDateWithActual)
これでエラーは出なくなりました。
しかし、このDAX式では正しい結果を求められません。
何故でしょう？
フィルターコンテキスト影響を取り除かない
左が先ほどのDAX式を使った現在の見込みNG、右が望む結果です。
現在の見込みNGではCALCULATEのフィルター式に'日付テーブル'[Date]>LastDateWithActualを使っています。
これが原因で列年_yyyy年と月_MM月から発生するフィルターコンテキストが全て上書きされてしまいました。
つまり'日付テーブル'[Date]>実績最終日以外のフィルターが掛かっていない状態のため、総計と同じ数字が全ての行に表示されています。
ではどうやってフィルターコンテキストを残すのでしょうか？
KEEPFILTERS関数を使う
KEEPFILTERS 関数をCALCULATEのフィルター式に使う事で、フィルターコンテキストの影響を上書きせずにフィルターを掛けることが出来ます。
その結果、式はこうなります。
現在の見込み =
VAR LastDateWithActual=CALCULATE(MAX('実績'[日付]),ALL('実績'))
RETURN
CALCULATE([見込み合計],KEEPFILTERS('日付テーブル'[Date]>LastDateWithActual))
これで目的の範囲で見込み合計を取得出来ました。
実績&見込みのメジャーを作る
実績合計と出来上がった現在の見込みを足し算するだけです。
実績&見込み = [実績合計]+[現在の見込み]
これで完成です。
翌月の実績データが入ってきても、現在の見込みは自動でスライドしてくれます。
まとめ
-
正しくリレーションを組もう
- これがないと無駄な苦労をします。
-
必ず日付テーブルを用意しよう
- 日付が絡む計算の殆どは、日付テーブルとリレーションが組まれてる事で解決します。
-
何を条件にすれば目的の値を取り出せるか考えよう
- 作法が違うだけで、ExcelでもDAXでも条件を元に値を取り出すのは同じです。
この手の情報はsqlbiのコラムにちょくちょく書かれています。
Power BIやDAXを使いこなしたい場合、英語の情報を毛嫌いせず定期的にチェックしましょう！