ユーザーが選択した任意の範囲で集計するには

Power BI の レポートに利用できるフィルターにはいくつかあり、集計される範囲を指定することができる。

- レポート全体に影響する **レポート レベル フィルター**
- ページごとに設定できる **ページ レベル フィルター**
- ビジュアルごとに設定できる **ビジュアル レベル フィルター**
- **ドリルスルー フィルター**(特定の用途だけれども...)

また、ディメンジョン テーブルである "日付テーブル" の "年"フィールドを スライサー ビジュアルに使用し、該当する"年"ごとの集計などはできる。

だけど、ユーザーが選択した範囲で集計できるレポートはどうしたらよいのか？ということ。

[デモファイル](https://1drv.ms/u/s!AsDIQL5V4mBOhyHFljcV9HL7zcP6) もありますよ。

# 先にポイント
フィルター 機能では **列(フィールド)** を指定しその値をもってフィルターを適用するのだけど、ビジュアル レベル フィルターは **メジャー(measure)** を使用することができる。対象の範囲にあるかをこれで判定すればよい。そして、リレーションシップ が設定されない パラメーター用のテーブルが必要。

# レポートの仕様
Power BI レポート の ページ に配置した スライサーで集計する範囲を指定する。

- 範囲の指定は 年月(YYYY-MM) と 月数 で
- 集計するのは 月ごとの集計 と その累計額

## データモデルの概要
受注日ごとの受注額を含まれる "受注"テーブル と 時系列の集計に必要な 日付テーブル "カレンダー" テーブルがある。
それらのリレーションシップは "カレンダー" の日付列 "Date"  <- 1 対 多 -> "受注" の日付列 "受注日"

### ファクト テーブル：受注

|...|受注日|明細行計|...|
|:-:|:-:|:-:|:-:|
|...|...|...|...|
|...|2015-1-11|\30,000|...|
|...|...|...|...|
|...|2017-12-11|\50,000|...|
|...|2017-11-30|\80,000|...|
|...|...|...|...|

### ディメンジョン テーブル：カレンダー
[CALENDARAUTO 関数][CALENDARAUTO] を使用し、集計に使用する "年月"列を追加

```text:カレンダー
カレンダー =
ADDCOLUMNS (
    CALENDARAUTO (),
    "年", YEAR ( [Date] ),
    "月", FORMAT ( [Date], "mmm" ),
    "年月", FORMAT ( [Date], "yyyy-mm" ),
    "MonthNumber", MONTH ( [Date] )
)
```
# ここから本題に関する作業
## パラメーター用のテーブルを追加
テーブルには、年月(YYYY-MM) と 月数 に使用する列を用意

### テーブル Period

```text:テーブル：Period
Period = 
SUMMARIZECOLUMNS( 'カレンダー'[年月], "日付", MIN( 'カレンダー'[Date] ) )
```
![2017-12-13_12-41-37.png](https://qiita-image-store.s3.amazonaws.com/0/193509/da793507-3d7b-4a19-6ad5-f3acab286dee.png)

### テーブル Durations

```text:テーブル：Durations
Durations =
SELECTCOLUMNS( GENERATESERIES( 3, 12 ), "Months", [Value] )
```
![2017-12-13_12-41-29.png](https://qiita-image-store.s3.amazonaws.com/0/193509/e84a21c2-bd12-c83e-f3ee-be573a456d25.png)


## メジャーを作成
### 受注額を集計するメジャー
ファクトテーブルに計算済みの列があるのでそのまま SUM 関数で集計

```text:メジャー：受注額
受注額 = SUM ( '受注'[明細行計] )
```

### 選択された月数を計算するメジャー
[SELECTEDVALUE 関数][SELECTEDVALUE] を使用して 選択された値を計算
フィルターが適用された 'Durations'[Months] 列が単一の値となったとき、その値を返す。一意の値を特定できない場合は ( 6 ) とした。

```text:メジャー：SelectedDuration
SelectedDuration = SELECTEDVALUE( 'Durations'[Months], 6 )
```

この関数は比較的新しくて、以前の書き方であれば以下の通り。

```text
SelectedDuration =
IF( HASONEVALUE( 'Durations'[Months]), VALUES( 'Durations'[Months] ), 6 )
```

### 選択した範囲の開始日を計算するメジャー
ここでも [SELECTEDVALUE 関数][SELECTEDVALUE] を使用して 選択された値を計算
[FILTER 関数][FILTER] では、テーブル"Period" に 選択した年月を条件にフィルターを適用。そして、[日付]列を参照するために [MAX 関数][MAX] を使用した。列からスカラー値を取り出すためのものなので、[MIN 関数][MIN] でも同じ結果になる。なぜなら、フィルタ適用後1行のテーブルになるから。

```text:メジャー：FirstDateInPeriod 
FirstDateInPeriod = 
CALCULATE(
    MAX( 'Period'[日付] ),
    FILTER( 'Period', 'Period'[年月] = SELECTEDVALUE( 'Period'[年月] ) )
)
```

### 選択した範囲であることを計算するメジャー
選択した範囲で集計するビジュアルの **ビジュアル レベル フィルター** に使用
ディメンジョン テーブル の 'カレンダー' を基に集計が行われる場合、'カレンダー' すべての行ごと(日付ごと)に対しこのメジャーで評価することができる。

```text:メジャー：IsSelectedDates
IsSelectedDates = 
IF(
       MAX( 'カレンダー'[Date] ) >= [FirstDateInPeriod]
    && MAX( 'カレンダー'[Date] ) <= EOMONTH( [FirstDateInPeriod], [SelectedDuration] - 1)
    , 1, 0
)
```

選択した期間の開始日 [FirstDateInPeriod] と 期間の月数 [SelectedDuration] で表現できる範囲に 'カレンダー'[Date] の日付が含まれるとき( 1 )、そうでないときを ( 0 ) とした。

### 動作の確認

![2017-12-13_14-52-23.png](https://qiita-image-store.s3.amazonaws.com/0/193509/caec9ac6-55a0-bdf7-9f89-7fa0ecb115f2.png)

## レポートページの作成
必要なビジュアルは、

- 期間を指定するための スライサー ビジュアル
- 特定した期間で集計するお好みのビジュアル

の 2種類

### スライサー
2つのスライサーは 範囲の開始年月 と その期間(月数)。
スライサーの [フィールド]には それぞれ 'Period'[年月] と 'Durations'[Months]

### お好みのビジュアル
棒グラフ(積み上げ縦棒グラフなど)を例にすると、"軸" を 'カレンダー'[年月] 列、"値" は メジャー [受注額]
メジャー [IsSelectedDates] を **ビジュアル レベル フィルター** に配置し、条件の値を ( 1 ) にしてフィルターの適用


![2017-12-13_15-05-13.png](https://qiita-image-store.s3.amazonaws.com/0/193509/977b0d3a-bc35-6901-5351-129d728bd326.png)

![2017-12-13_13-12-03.png](https://qiita-image-store.s3.amazonaws.com/0/193509/04c31faa-fb3e-5028-ed69-28274ea9442a.png)

### 選択された範囲での累計
受注額の累計金額も表示したいが、独自のメジャーを定義することが必要
タイム インテリジェンス 関数のひとつ [TOTALYTD 関数][TOTALYTD] とかが便利なのだけど、これだと累計の開始区切りの手当が必要だし、12ヵ月を超える累計ということもあるし。

#### 選択した範囲で受注額の累計を計算するメジャー
'カレンダー'[年月] と メジャー [受注額] のテーブルがあったとして、

|'カレンダー'[年月]|[受注額]|
|:-:|:-:|
|...|...|
|2017-01||
|2017-02||
|2017-03||
|...|...|

"2017-01" の行 で集計対象となる 'カレンダー'[日付] は 2017-01-01 から 2017-01-31 の範囲となり、メジャー [受注額] はこの範囲で評価が行われる。次の行以降でも同様に **行ごと** に評価されるので、月ごとの集計表となる。

[受注額]を月ごと累計したいので、行ごとに適用される範囲は置き換える必要がある。行ごとで適用されている範囲を置き換えるには [CALCULATE 関数][CALCULATE] を使用する。

```text:メジャー：受注額CumulativeTotalInPeriod
受注額CumulativeTotalInPeriod = 
VAR CumulativeTotal =
    CALCULATE(
        [受注額],
        FILTER(
            ALL( 'カレンダー'[Date] ),
               'カレンダー'[Date] >= [FirstDateInPeriod]
            && 'カレンダー'[Date] <= MAX( 'カレンダー'[Date] )
        )
    )
RETURN
    CumulativeTotal
-------------------------------------------------------------
//  IF(
//        COUNTROWS( FILTERS( 'カレンダー'[年月] ) ) = 1
//        && NOT( ISBLANK( [受注額] ) )
//      , CumulativeTotal
//  )
```

[FILTER 関数][FILTER] で 置き換える集計範囲(フィルター テーブル)を定義

```
FILTER(
    ALL( 'カレンダー'[Date] ),
       'カレンダー'[Date] >= [FirstDateInPeriod]
    && 'カレンダー'[Date] <= MAX( 'カレンダー'[Date] )
)
```
**ALL( 'カレンダー'[Date] )**

各行が集計されるときすでに 'カレンダー'[Date] 列の範囲が適用されているので、[ALL 関数][ALL] でその範囲を解除

**'カレンダー'[Date] >= [FirstDateInPeriod]**

'カレンダー'[Date] が 指定する集計範囲の開始日 [FirstDateInPeriod] 以上(日付なので当日含む以降)

**'カレンダー'[Date] <= MAX( 'カレンダー'[Date] )**

各行で適用されている範囲の最大値 MAX( 'カレンダー'[Date] ) 以下(日付なので当日含む以前ということ)
左辺の 'カレンダー'[Date] は ALL( 'カレンダー'[Date] ) で 評価されたテーブルの 'カレンダー'[Date] 列

```
FILTER(
    ALL( 'カレンダー'[Date] ),
       [Date] >= [FirstDateInPeriod]
    && [Date] <= MAX( 'カレンダー'[Date] )
)
```
って表記したら少し区別つくのかな。機会があればそのうち整理。

# できあがり

![2017-12-13_14-52-31.png](https://qiita-image-store.s3.amazonaws.com/0/193509/bb498772-bbaf-7090-952c-43da246df38b.png)

[デモファイル](https://1drv.ms/u/s!AsDIQL5V4mBOhyHFljcV9HL7zcP6) はご自由にどうぞ。

# その他
- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)


[SELECTEDVALUE]:https://msdn.microsoft.com/en-us/library/mt842608.aspx
[CALENDARAUTO]:https://msdn.microsoft.com/en-us/library/dn802534.aspx
[SUMMARIZECOLUMNS]:https://msdn.microsoft.com/en-us/library/mt163696.aspx
[SUM]:https://msdn.microsoft.com/en-us/library/ee634387.aspx
[CALCULATE]:https://msdn.microsoft.com/en-us/library/ee634825.aspx
[ALL]:https://msdn.microsoft.com/en-us/library/ee634802.aspx
[FILTER]:https://msdn.microsoft.com/en-us/library/ee634966.aspx
[TOTALYTD]:https://msdn.microsoft.com/en-us/library/ee634400.aspx
[MAX]:https://msdn.microsoft.com/en-us/library/ee634228.aspx
[MIN]:https://msdn.microsoft.com/en-us/library/ee634870.aspx