Power BIでSUMIFSを?!(修正版)

@ishiayaya さんと@yugoes1021 が開催されている[Power BI Weekly News](https://pp-weeklynews.connpass.com/)。
いつも楽しく為になる話を聴かせていただいている訳なのですが、以下、第10回の参加者からの質問が興味深かったので試してみたメモです。

https://pp-weeklynews.connpass.com/event/238185/

<font color="red">以降、タイムインテリジェンス関数をカレンダーテーブル無しに使う、不要なパラーメータを関数に与えている、といった点が始めに投稿した内容を含んでおり、その旨アドバイスいただいた為一旦削除しての再投稿となります。　</font>

##質問 ※簡略化しています
Power BIでExcelのSUMIFSのようなことをどうやったら実現出来ますか？
・商品の売上一覧（日付、商品名、販売額）

| 商品名| 日付 | 販売額 |
|:-:|:-:|:-:|
|A|2021/12/10|5000|
|A|2021/12/11|5000|
|A|2021/12/12|5000|
|A|2021/12/13|5000|
|A|2021/12/14|5000|
|A|2021/12/15|5000|
|A|2021/12/16|5000|
|A|2021/12/17|5000|
|A|2021/12/18|5000|
|A|2021/12/19|5000|
|A|2021/12/20|5000|
|B|2021/12/10|10000|
|B|2021/12/11|10000|
|B|2021/12/12|10000|
|B|2021/12/13|10000|
|B|2021/12/14|10000|
|B|2021/12/15|10000|
|B|2021/12/16|10000|
|B|2021/12/17|10000|
|B|2021/12/18|10000|
|B|2021/12/19|10000|
|B|2021/12/20|10000|
|C|2021/12/10|20000|
|C|2021/12/11|20000|
|C|2021/12/12|20000|
|C|2021/12/13|20000|
|C|2021/12/14|20000|
|C|2021/12/15|20000|
|C|2021/12/16|20000|
|C|2021/12/17|20000|
|C|2021/12/18|20000|
|C|2021/12/19|20000|
|C|2021/12/20|20000|
|D|2021/12/10|40000|
|D|2021/12/11|40000|
|D|2021/12/12|40000|
|D|2021/12/13|40000|
|D|2021/12/14|40000|
|D|2021/12/15|40000|
|D|2021/12/16|40000|
|D|2021/12/17|40000|
|D|2021/12/18|40000|
|D|2021/12/19|40000|
|D|2021/12/20|40000|

があり、これに対して、商品名×開始日～終了日の集計を実施したい、というもの。こんな感じに。

|テスト名称|商品名|開始日|終了日|商品別売上合計|
|:-:|:-:|:-:|:-:|:-:|
|A_1|A|2021/12/12|2021/12/14|15000|
|A_2|A|2021/12/18|2021/12/20|15000|
|B_1|B|2021/12/13|2021/12/15|30000|
|B_2|B|2021/12/19|2021/12/21|20000|
|C_1|C|2021/12/14|2021/12/16|60000|
|C_2|C|2021/12/20|2021/12/22|20000|
|D_1|D|2021/12/16|2021/12/17|80000|
|D_2|D|2021/12/20|2021/12/22|40000|

で、元のテストの情報はどうなっているのかと、恐らくこんな感じなのではと思われる。

|テスト名称|開始日|終了日|
|:-:|:-:|:-:|
|A_1|2021/12/12|2021/12/14|
|A_2|2021/12/18|2021/12/20|
|B_1|2021/12/13|2021/12/15|
|B_2|2021/12/19|2021/12/21|
|C_1|2021/12/14|2021/12/16|
|C_2|2021/12/20|2021/12/22|
|D_1|2021/12/16|2021/12/17|
|D_2|2021/12/20|2021/12/22|

※Point: テスト名称の一部が、商品名

##で、どう試してみたか？
方法はいくつもあるだろうし、これが良い方法なのか知らない（むしろ悪そうな気がする）が、ひとまず以下のような方法で実現出来ました。

####1. [Power Query] 売上一覧をロード
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/cf0efd26-6aa1-bacc-c10c-239b5961a924.png)

####2. [Power Query] 売上一覧の参照から、商品名列以外を削除＆商品名の重複を削除し商品クエリを作る
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/6978ccdd-4717-9f99-19fd-1e960b147e5a.png)

####3. [Power Query] テスト情報をロード
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/ef455b6c-714b-c611-8201-2bddb673ca1f.png)

####4. [Power Query] テスト情報のテスト名称の1文字目を切り出し、「商品名」列を作る
「テスト名称列」列を選択し、「例から列を追加する」で、テスト名称の1文字目「A」を入力して作成
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/5b63485e-b8ef-a673-5945-61697dc70e2d.png)

####5. [Power Query] 閉じて適用

####6. [データ] カレンダーを作る。
CALENDARAUTO()を使い、カレンダーテーブルを作る。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/d8133ab9-e4d4-b933-02dc-fda6460be3e1.png)

####7. [モデル] 「商品名」列を使い、テスト情報と商品、売上一覧と商品のリレーションシップを作る。また、カレンダーテーブルの日付と売上一覧とのリレーションシップも作る。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/f3e099a9-27bf-0288-c166-98ccb7c7ee08.png)

####8. [レポート] 売上一覧に、合計値のメジャー「売上合計」を作る。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/841a4551-2619-df8e-8e03-f46f69bd5a87.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/9d833287-e4a4-4a85-0bff-e955d9f3f204.png)

####9. [データ] DAX計算式でテスト情報に売上合計の列を作る。
Calculate関数で、日付の期間と商品名をフィルタ条件にして、合計を算出する。

テスト期間の売上合計 = CALCULATE('売上一覧'[売上合計],DATESBETWEEN('Calendar'[Date],'テスト情報'[開始日],'テスト情報'[終了日])                           )

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/32c01fa9-750f-4b53-86fa-0fd29b00c80a.png)

####10. [レポート] テーブルビジュアルを使い、テスト情報を配置する。 
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/f6feef30-5421-e68c-0b50-3b6c56c0cb5b.png)

###別アプローチ：テスト実績のファクトテーブル作成で対応する

Power BIでのモデリングに関しては、一般的に列のマージを多用することは、データ保持の効率性やパフォーマンスの観点から推奨されていません。

ですが、上記のモデルはスタースキーマで無いため使いづらいものとなっています。

スキーマをよりシンプルにする為に、列のマージを行い、テスト実績に売上金額を結合させ、売上一覧はデータモデルとして読み込まない、というアプローチなんてのもあるのかな、と思います（以下）。

####1. [Power Query] テスト情報のテスト期間を日付の一覧に展開する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/c9b78afb-aacb-95db-5238-ebf454bfcad7.png)

= Table.AddColumn(区切り記号の前に挿入されたテキスト, "日付", each List.Dates([開始日], Duration.Days([終了日]-[開始日])+1, #duration(1,0,0,0)))
→ 期間のFrom,Toを使い日付Listの列を追加する。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/6cdb0c4d-0c95-948a-7d72-cca42f3029db.png)
→ 日付Listの列を展開すると、上記のようになる。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/791d8619-9ef4-3f6b-624f-80e98c6abcd4.png)
→ 「開始日」「終了日」は不要のため列を削除

###2. [Power Query] テスト情報に売上一覧の金額をマージさせる
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/5383232d-ad7e-b30e-3919-a1d5b06f4459.png)

→ 左外部結合で、売上の金額をテスト情報にマージさせる。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/94275d27-277d-49a6-c39b-b199e4d9a6a9.png)

###3. [Power Query] 売上一覧がモデルとして作られないよう「読み込みを有効にする」チェックを外す。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/cc32cd61-e667-5667-3719-91c2b80b7877.png)

###4. [Power Query] 閉じて適用
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/8e109c32-905d-c143-b2eb-29110ae7155f.png)
→ モデルは上記のような形で作られる。

###5. [レポート] テスト情報に、以下のメジャーを追加
開始日 = MIN('テスト情報'[日付])
終了日 = MAX('テスト情報'[日付])

###6. [レポート] テーブルビジュアルを作る。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2331634/2de29fb3-b1c9-393b-60d8-68c0f4df6da1.png)
以上