[DAX] 第５章 当期売上累計 - VARを使った別解
今回は、拙著『Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本』の「第５章 当期売上累計」についてVAR（変数）を使った別解を紹介します。
元の数式
第5章 のP251では、売上の累計を出すために以下の式を使っています。
=
CALCULATE (
[売上合計],
FILTER ( ALL ( 'カレンダー' ), 'カレンダー'[日付] <= MAX ( 'カレンダー'[日付] ) )
)
※ 数式を見やすくするため、改行を入れています。
ここで少し補足すると、FILTER関数とは、テーブルをふるいに掛ける関数です。
つまり、
- 一つ目の引数で受け取ったテーブル（ALL'カレンダー'）に対して、
- 第二引数のふるい（'カレンダー'[日付] <= MAX ( 'カレンダー'[日付] )）に掛けて、
- 残った行をアウトプットのテーブルとして返す
働きをします。
本文でも述べていますが、この数式を難しくしているのは右側のMAX関数がFILTER関数の中を飛び越えてオリジナル・フィルター・コンテキストの値を参照しているためです。
つまり、
- FILTER関数の中では、カレンダーの行ループの中で「 'カレンダー'[日付] <= MAX ( 'カレンダー'[日付] )」の条件を一行一行チェックしていますが、
- 「MAX ( 'カレンダー'[日付] )」は、FILTER関数を飛び越えて、ピボットテーブルの絞り込み条件＝フィルター・コンテキストを参照しているためです。
ちなみに①の一行一行のことを行コンテキスト (Row Context )と言い、FITLER関数のように一行一行のループ処理を作り出す関数をイタレイター (Iterator)と言います。
とっつきにくい言葉ですが、行ループ処理だと思っていただければ大丈夫かと思います。
別解
上記の②のMAXの部分を最初からFILTERの外に出してしまえば、式が分かりやすくなります。そのためにはVAR（変数）を使います。
書式
VAR <変数> = <式>
[VAR <変数2> = <式2> [...]]
RETURN <結果の式>
P251の式をこれに当てはめると以下の式になります。
=
VAR MaxDate = MAX ( 'カレンダー'[日付] )
RETURN
CALCULATE (
[売上合計],
FILTER ( ALL ( 'カレンダー' ), 'カレンダー'[日付] <= MaxDate )
)
こんどは、MaxDateを変数として外に出したので分かりやすくなりました。RETURN以下の数式は、MaxDateの部分を除いて全く同じです。
つまり、MaxDateはFILTER, CALCULATEの外で最初にフィルターコンテキストの値を拾ってきて、それをFILTERの中の行ループのふるいに掛けるイメージがしやすくなったと思います。
まずは、ピボットテーブルの個々のセルのフィルターコンテキストを受けたMAX日付を拾ってきて・・・
MaxDate（フィルターコンテキスト）のイメージ
その値を、FILTER関数の行コンテキストの１行１行で比較しています。
FILTER関数の中（行コンテキスト）のイメージ
二つの式の集計結果も同じになっています
集計結果
参考までに「当期」の条件も含めた2変数の式も以下に記します。
=
VAR MaxDate = MAX ( 'カレンダー'[日付] )
VAR MaxFinYear = MAX ( 'カレンダー'[会計年度] )
RETURN
CALCULATE (
[売上合計],
FILTER (
ALL ( 'カレンダー' ),
'カレンダー'[日付] <= MaxDate
&& 'カレンダー'[会計年度] = MaxFinYear
)
)
第５章では最終的にタイムインテリジェンス関数を使用するので、こちらの数式は使用しなくても良いですが、この書き方を覚えておくと別なケースで応用できます。
