複数のデータ(実績と見込み)を一つのメジャーで表示する

この記事は[sqlbi](https://www.sqlbi.com/)の[Showing actuals and forecasts in the same chart with Power BI](https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/)を参照しています。
備忘も兼ねているので、自分が必要とした部分しか書いていません。
詳しく勉強したい方は[参照した記事](https://www.sqlbi.com/articles/showing-actuals-and-forecasts-in-the-same-chart-with-power-bi/)を是非見てください。

Power BIに限らずExcelのPower Pivotでも出来る内容です。
#やりたい事
実績と見込みのデータがあります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/74fcc7fb-0229-1572-3eaa-9692df91a3f4.png)

線グラフだとこんな感じ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/51159d62-ba38-c5bd-eaf6-a72b7effde5c.png)

これを**実績がまだない期間を見込みに置き換えたい**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/596ddd9b-5b76-f44f-41eb-b13a8d176275.png)

置き換えた線グラフだとこんな感じ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/735acadc-9c33-a34c-35fe-ac695d9127cc.png)

##使うデータと設定
利用するデータセットとリレーションは以下の通り。

###リレーション
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/b215184e-9153-6b43-664b-b9e65fa8de19.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/e17dece0-6c4d-ac43-6217-230d270486b6.png)

###データセット
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/ae437c67-f076-4df8-2e9f-28841f68f4ff.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/54e25ee2-c13e-973c-d6e3-9df0298c67c2.png)

**見込みテーブルは日付がyyyyMM形式の「年月」列で構成されています。**
このままでは日付テーブルとリレーションを組めないので、**年月ブリッジという中間テーブルを使ってブリッジさせます。**

年月ブリッジ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/3398d0b2-18fb-2bfd-68a1-ff63a0006920.png)

ここら辺のお作法はPower BIの神が解説済みなのでそちらをどうぞ。
[予算実績を集計比較する データモデル パターン](https://qiita.com/PowerBIxyz/items/bb9b6dcc44721d77aad8)

###メジャー
事前に定義したメジャーはこの二つ。

```C#:事前に定義したメジャー
実績合計 = SUM('実績'[実績])
見込み合計 = SUM('見込み'[見込み])
```

#どういうデータにすれば出来そうか
こういう形にしてあげれば`実績合計`と`現在の見込み`の足し算で表現できそうです。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/884b968d-f497-9385-884d-07b96454ee59.png)

##メジャーの準備
###実績の最終日を求める
まずは実績の最終日が分からなければ始まりません。
実績の最終日を求める数式で真っ先に思い浮かぶのは`=MAX('実績'[日付])`ですが、**これは正しく動作しません。**

以下の`実績最終日True`は正しく動作します。
何が違うのでしょう？

```C#:実績最終日
実績最終日False = MAX('実績'[日付]) ///こっちはNG
実績最終日True = CALCULATE(MAX('実績'[日付]),ALL('実績')) ///こっちはOK
```

####フィルターコンテキストの影響を取り除く
これは**日付に何もファイルターが掛かっていない状態です。**
どちらも正常に見えます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/726c0372-8995-d83a-d1b2-1dc6ea0dbc49.png)

しかし**日付にフィルターをかけると`実績最終日False`はズレてしましました。**
これでは正しく最終日を求められません。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/dfc4c7fb-512d-28b8-0682-7a9ccdb4a1f7.png)

**原因はフィルターコンテキスト**で、これは**スライサーなどDAX式の外部から来るフィルターの影響を指します。**
テーブルやマトリックスの行や列に使われるディメンション(品名や担当者名など)も、フィルターコンテキストを発生させます。

`実績最終日False`の構文では**フィルターコンテキストの影響を取り除けないのです。**

####ALL関数を使う
`実績最終日True`では**`CALCULATE`を使いフィルターコンテキストをフィルター式`ALL('実績')`上書きしている**ため、常に`実績`テーブル全体が参照され正しい実績最終日が取得できます。

###実績と重複しない範囲の見込みを求める
実績の最終日を求められたので、取り出すべき`見込み合計`を算出できそうです。
しかし、ExcelライクにDAX式を書こうとしていませんか？

```C#:Excelライクな式
現在の見込み = CALCULATE([見込み合計],'日付テーブル'[Date]>CALCULATE(MAX('実績'[日付]),ALL('実績')))
```
**このDAX式ではエラーが出ます。**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/b71b84fb-d607-d74b-30fd-acb6ee07eb96.png)

これは**CALCULATEのフィルター式で、CALCULATEとMAXを使う事が許可されていない**ためです。
####実績最終日を変数に格納する
実はCALCULATEのフィルター式に直接使う事が許可されていないだけで、**一度メジャーか変数にしてしまえば問題無く使えます。**

```C#:変数LastDateWithActualに実績最終日を格納
現在の見込み = 
VAR LastDateWithActual=CALCULATE(MAX('実績'[日付]),ALL('実績'))
RETURN
CALCULATE([見込み合計],'日付テーブル'[Date]>LastDateWithActual)
```
これでエラーは出なくなりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/a5bb111e-84df-1aaf-1a06-a49802f86496.png)
**しかし、このDAX式では正しい結果を求められません。**
何故でしょう？

####フィルターコンテキスト影響を取り除かない
左が先ほどのDAX式を使った`現在の見込みNG`、右が望む結果です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/dcc5006e-67cf-9d31-281d-2d4fb0b81578.png)

`現在の見込みNG`では`CALCULATE`のフィルター式に`'日付テーブル'[Date]>LastDateWithActual`を使っています。
これが原因で列`年_yyyy年`と`月_MM月`から発生するフィルターコンテキストが**全て上書き**されてしまいました。

つまり`'日付テーブル'[Date]>実績最終日`以外のフィルターが掛かっていない状態のため、**総計と同じ数字が全ての行に表示されています。**

ではどうやってフィルターコンテキストを残すのでしょうか？

####KEEPFILTERS関数を使う
**[KEEPFILTERS 関数](https://dax.guide/keepfilters/)を`CALCULATE`のフィルター式に使う事で、フィルターコンテキストの影響を上書きせずにフィルターを掛けることが出来ます。**

[Using KEEPFILTERS in DAX](https://www.sqlbi.com/articles/using-keepfilters-in-dax/)

その結果、式はこうなります。

```C#:現在の見込み
現在の見込み = 
VAR LastDateWithActual=CALCULATE(MAX('実績'[日付]),ALL('実績'))
RETURN
CALCULATE([見込み合計],KEEPFILTERS('日付テーブル'[Date]>LastDateWithActual))
```

これで目的の範囲で見込み合計を取得出来ました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/e5695130-5817-23cc-3906-62278d953afa.png)

#実績&見込みのメジャーを作る
`実績合計`と出来上がった`現在の見込み`を足し算するだけです。

```C#:実績&見込み
実績&見込み = [実績合計]+[現在の見込み]
```

これで完成です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/313502/064d781c-175d-d962-a3c0-cab2b238f41d.png)

翌月の実績データが入ってきても、現在の見込みは自動でスライドしてくれます。

#まとめ
- **正しくリレーションを組もう**
    - これがないと無駄な苦労をします。
- **必ず日付テーブルを用意しよう**
    - 日付が絡む計算の殆どは、日付テーブルとリレーションが組まれてる事で解決します。
- **何を条件にすれば目的の値を取り出せるか考えよう**
    - 作法が違うだけで、ExcelでもDAXでも条件を元に値を取り出すのは同じです。

この手の情報は[sqlbi](https://www.sqlbi.com/)のコラムにちょくちょく書かれています。
Power BIやDAXを使いこなしたい場合、英語の情報を毛嫌いせず定期的にチェックしましょう！