Power BIで「前年比」＋「他テーブルのデータ」を合わせた折れ線グラフの作成方法

Question

Power BI初心者です。
有識者の方にお伺いしたいのですが、画像のような「当年実績」「前年実績」「当年予算」の3つを同じ積み上げグラフにまとめたものを作成したいと考えております。
軸は年月になります。（簡易的な画像で失礼します。）

「凡例」にデータを置くことで「当年」と「前年」の凡例が自動で作成され
簡単に前年比のグラフを作成することが出来るというのは調べて分かったのですが他テーブルのデータも一緒に表示したい際はどうすればよろしいでしょうか？

「当年実績」「前年実績」は同じテーブル、
「当年予算」のみ別のテーブルになります。
どちらのテーブルも、年・月・日などの同じ項目で構成されています。
各テーブルには日付列（年月日でデータ型は日付）を作成しリレーションを行っております。

powerQueryエディターで「条件列」から「年＝2022」などで指定し
その年だけのデータ列を作成することは出来た為、
2年分だけのデータ量のグラフであれば一旦はそちらでよかったのですが
大元データは数年の蓄積があります。

「年月」の日付スライサーも設置したいと考えており、
どの「年月」を指定しても3つの棒グラフが動くようにしたいです。

DAX関数で「1年前の日付列」を作成することは出来たのですが
その「1年前の日付列」と「現在の年」を参照し
該当のデータ列を作成する方法などはあるのでしょうか？

画像のような「当年実績」「前年実績」「当年予算」の3つを同じ折れ線グラフにまとめられたグラフが作成できれば、
「1年前の日付列」は使用しなくても勿論問題ございません。

また、積み上げグラフのため日付毎にデータが積み上がっていく様子を表したいため、SUMが使用できない状況です。
SWITCH関数やIF関数も、SUM等を用いて1つの値にしていないためにエラーが出ておりました。

長らく困っているので、お力をお貸しいただきたいです。
よろしくお願いします。

ーーーーーーーーーーーーーーーーーーーーーーーーーー
他サイトに投稿してからteratailの存在を知り、投稿いたしました。

Comments

[mhoby_vt]

お手数ですが、数行で構いませんのでインプットデータ(各テーブル)のサンプルを頂けますでしょうか？

[POCHAA]

初めまして、ご依頼ありがとうございます。
サンプルですが画像での用意が出来ず申し訳ございません。

それぞれのテーブル全て共通で、同じ列になります。
下記のような列があります。
「年」「月」「日」「店名」「商品区分」
「日別金額」（その日のみの金額）
「累計金額」（該当月の1日から累計した金額）

こちらでよろしかったでしょうか？
よろしくお願いします。

[mhoby_vt]

ありがとうございます。
上記テーブルに、POCHAAさんが「年」「月」「日」を合わせた「日付」列がある認識で合っていますでしょうか？

また、実績には何年分かのデータが存在しており、例えばスライサーで2022/03のみを選択した場合は、実績テーブルから2022/03の実績,2021/03の実績、予算テーブルから2022/03の予算が表示されるイメージで合っていますでしょうか？

[POCHAA]

お返事ありがとうございます。
はい、どちらも仰っていただいている認識でお間違いございません。
よろしくお願いいたします。

[mhoby_vt]

追加で質問させて下さい。
表示は累計とのことですが、年や年度の切り替わりでリセットされることはありますでしょうか？

[POCHAA]

本文中の説明が足りておらず申し訳ございません。
「月毎の累計のグラフ」で、最初にご提示したグラフの横軸は日付を想定しております。
毎月1日でデータが「0」に戻るため、スライサーで何年月かを指定し、グラフで日毎を見る形を取りたいと考えております。

Answer 1

回答させて頂きます。

①日付テーブルの作成
下記リンクにある日付テーブルのソースをコピーし、PowerBIの「新しいテーブル」を押下して貼り付けます。
日付テーブル

その際、ソースを少し書き換えます。

VAR StartDate="2016/04/01"
VAR EndDate="2020/3/31"

こちらを

VAR StartDate=MIN('実績'[日付])
VAR EndDate=MAX('実績'[日付])

のように書き換え、実績のテーブルの最小日付～最大日付を取り出します。

②日付テーブル[Date]と実績テーブル[日付],予算テーブル[日付]をリレーションで繋げる

③メジャーを3つ作成

・当年実績 = SUM('実績'[累計金額])
・前年実績 = CALCULATE(SUM('実績'[累計金額]),DATEADD('実績'[日付],-1,YEAR))
・当年予算 = SUM('予算'[累計金額])

④スライサーに日付テーブルから年月を設定

⑤グラフの軸に日付テーブルから日付を設定

⑥グラフの値に3つのメジャーを設定

※日付テーブルで定義されている列に使用したい形式のものがなければ、新たに定義する形となります。
※[Date]列をグラフ等に設定すると時間まで表示されるため、"yyyy/mm/dd"等で定義した列を作成して使用すると良いと思います。

以上となります。
不明点や認識がズレている点等ございましたらご連絡下さい。

Comments

[POCHAA]

ご回答いただきありがとうございます。
順々に対応させていただきうまく作成できそうだったのですが、
前年のメジャーだけ、データが空になっているようでグラフとしても表示されない状況です。

・ご助言いただいた通り、実績テーブルの日付を取り出し作成した日付テーブルで前年同月があることを確認（2022年1月～3月を選択すれば2021年1月～3月データもあるため、空にはならないはず）
・マトリックスに「前年実績 = CALCULATE(SUM('実績'[累計金額]),DATEADD('実績'[日付],-1,YEAR))」メジャーを挿入したところ、どの年月日でも数値が空であることを確認

何かもう一工夫必要なのでしょうか、たびたびお力をお貸しいただき申し訳ございません。
よろしくお願いいたします。

[mhoby_vt]

スライサーには日付テーブルの列を設定していますでしょうか？

[POCHAA]

はい、しております。
他テーブルとリレーションをした「Date」を入れております。

[mhoby_vt]

グラフの軸にも日付テーブルの列を設定されていますでしょうか？
何かしら実績テーブル(トランザクションテーブル)に直接フィルターがかけられているのだと思うのですが、他にスライサーやフィルタ設定されている等ありますでしょうか？

[POCHAA]

お世話になっております、確認したところ無事に解決いたしました。
「前年実績 = CALCULATE(SUM('実績'[累計金額]),DATEADD('実績'[日付],-1,YEAR))」メジャーの
「'実績'[日付]」部分を作成した日付テーブルの[Date]へ変更したところ、前年の数値が出るようになりました。
date関数で作成した日付列だったため、何か齟齬があったのかもしれません。失礼いたしました。
この度は貴重なお時間を割いていただき、誠にありがとうございました！

[mhoby_vt]

承知致しました。
無事解決に至って良かったです。