Power BIを使う場合、DAX*1を用いて計算列*2や計算テーブル*3の構築、あるいはメジャーを算出します。メジャーは定義済計算式であり、[売上高]や[粗利益率]等の指標を算出できます。DAXはデータモデルにおけるビジネス用の数式を算出するものですが、その中でも特に多く使用されるものが「時系列メジャー」となります。
- 時系列メジャーとは
- TI関数の種類
- 移動総計の算出（DATESINPERIOD関数）
- 切り口別に期待する結果を返さない落とし穴①
- 切り口別に期待する結果を返さない落とし穴②
- 最終的な正しいDAX式
- まとめ
時系列メジャーとは
時系列メジャーはDAXのタイムインテリジェンス関数（以降「TI関数」）を使って算出されます。TI関数は多く存在しますが、その役割は単純で、
時系列データを操作できる関数
となります。全てのTI関数はデータモデルにおける日付テーブル（通常、Date、DimDate、dCalendar等のDimension Table）のDate列（日付型列）に対するフィルター条件を適用するものとなります。また、TI関数は全てテーブルを返すことが特徴であり、LASTDATEはそのうちの1つであるが、スカラー値を返すMAXと良く混同されがちであることに注意が必要です。
TI関数の種類
TI関数は多く存在しますが、個人的に良く使うものは以下のものとなります。
LASTDATE
DATEADD
DATESINPERIOD
DATESYTD
DATESMTD
DATESQTD
SAMEPERIODLASTYEAR
使用している数があまり多くはないというのが印象ですが、公式ドキュメントによると、執筆時点では計35個あるTI関数のうち、その1/5しか使っていないことになります。
使用数が少ない理由は2つあり、1つはこれらのTI関数で時系列を操作するニーズが殆ど満たされること、もう1つはTI関数を使用できない場合、カスタムTI関数を自分で作っているからです。後者はややアドバンスなトピックになりますが、今回の記事ではDATESINPERIODを使った計算の落とし穴について見ていきたいと思います。
移動総計の算出（DATESINPERIOD関数）
DATESINPERIODは個人的に最も使用するTI関数の1つです。理由は[年間売上]等のメジャーを異なる月で見たい場合に、このTI関数が一番便利だからです。例えば、下図のように21/03時点の年間売上はスライサーを選べば見られます。
同じく、21/02を選べば、同月から遡って12ヵ月の累積売上を見ることが可能です。
21/01も同様です。
各月を時系列に結んだ場合はどうでしょうか？下図の通り、年次総計売上（MAT Sales = Moving Annual Total）と売上トレンド（右肩上がりであれば、直近において売上増加トレンド、右肩下がりであれば売上減少トレンド）の推移を知ることができます（注：下記チャートは月次売上ではなく、その月における直近12ヵ月の累積売上の推移）。
このように、スナップショット（その時点）の年間〇〇数値を見るのに、DATESINPERIODは非常にお手軽に使えるTI関数となります。この[MAT Sales]メジャーは以下の通りです。
DATESINPERIODの詳細は上記公式ドキュメント、もしくは下記DAX Guideをご参考。
切り口別に期待する結果を返さない落とし穴①
前置きが長くなりましたが、このDATESINPERIODを使用した場合の落とし穴について見ていきます。前述のメジャーが正しく機能するためには全体条件として
カレンダーテーブルと売上実績の最後が一致していること（同じ年月日で終わる）
が必要となります。言い換えれば、売上実績が21/03までしかない場合、日付テーブルも21/04や21/12ではなく、21/03になっている必要があることです。日付テーブル(dCalendar)の日付 > 売上実績の日付となった場合、安心できる時系列メジャー（今回の例では移動総計）を作るためにはDAXが一気に難しくなります。
簡単な例として、直近7日間の移動総計売上を計算したデータモデルを用意しました。
テーブルの定義は以下の通りです。
Sales: 売上実績テーブル（2021年1月1日～5月1日まで）
dProduct: 商品マスタ
dCalendar: 日付テーブル（2021年1月1日～12月31日まで）
リレーションシップは以下の通り、2つのみ（N=多、1 = 1）。
Sales(N) - dProduct(1)
Sales(N) - dCalendar(1)
このシンプルなデータモデルからは以下2つのメジャーを作ります。
この2つのメジャーを表にすると以下のようになります。
① dCalendarとSalesの日付が同じ2021年5月31日までの場合
② dCalendarだけが2021年12月31日時点まで伸びていた場合
②が正しく計算結果を返さないのは数年前に分かっていたことですが、当時はなぜ？というのが分からず、一人ですごく悩んだのを覚えています。
この結果を理解するためには、評価コンテキスト（Evaluation Context）のうち、フィルターコンテキストを理解する必要があるのですが、全てのビジュアルにおける数値はそれぞれセル別に評価されている、という概念を知っていることが必要になります。つまり、上図は概念的にExcelのPivotテーブルに似ていますが、実際にはフィルターコンテキストを変更できるCALCULATEを使ったことで、各セル別に異なるフィルター条件が掛かっており、DAXによって算出される最終的な値はCALCULATE及び外部のフィルター（スライサー等）に従って計算結果を返します。そのため、ExcelのPivotのように、各セルを集計したものが合計値になるとは限らないのです。
※ フィルターコンテキストの詳細については以下より
docs.microsoft.com
上記リンクを見ても良いのですが、もう少し直観的に分かりやすく話をしますと、それぞれのフィルター条件を見るために、以下の式で確認してみます。
この[date_filter]というアドホックなメジャーをテーブルに入れると、①と②では以下のようになります。
[SalesAmt.7Days]というメジャーはDATESINPERIODを使用し、まずは各セルのその時点から遡って7日分の日付をテーブルとして抽出した後、CALCULATEのフィルター条件にこれを適用して、[SalesAmt]というメジャーを評価します。すなわち、上記「①期待通りのパターン」では、左表も右表も正しい日付が抽出されていることに対して、「上記②期待とは違うパターン」は
データがない遥か先の日付（左表の12/25～12/31）、もしくは一部のデータだけが存在する日付（右表の2021年6月1日以降）
を抽出してしまったが故、結果が空白あるいは（6/1～6/6までのみ）間違っていたのです。[date_filter]というメジャーは各パターンが抽出した日付をリストにして表内に表したものであり、フィルター条件を調べるためのテクニック（DAXのデバッグ）に使用しています。なお、CONCATENATEXの使用方法は下記DAX Guideを参照されたい。
切り口別に期待する結果を返さない落とし穴②
BIレポートを構築する際、私が最も重要視しているのがどのような状態でも正しい結果を返すレポートに仕上げることです。初期設定でスライサー等で特定の状況を創り出すことで期待する結果（画面）を表示することは可能ですが、それによってユーザーに混乱を与えてしまうのでは意味がありません。そのため、やはりDAXコード、あるいはデータモデルの修正を行うことが望ましいと考えています。
コードの修正ですが、DAXに少し慣れた方であればもしかすると以下のよう書き換えることを考えるのかもしれません（※ここから先はdCalendarが12/31まである前提で話を進めていきます）。
変更点は変数_current_dateの式をMAX( dCalendar[Date] )からMAX( Sales[日付] )に変更しただけです。dCalendarが12月まで伸びているのが原因であれば、MAX値はSalesのほうから持ってくれば良いと考えるわけです。このDAXで作ったメジャーは以下③の結果を返します。
ご覧の通り、ほぼ「① 期待通りのパターン」と同じになりました。ただし、ここで良く分からないことが起こってしまいました。右図の2021年6月1日から、[SalesAmt.7Days]がなぜか全て同じ数字（44）となって出現し始めました。[date_filter]メジャーを見ると、日付が全て1-01（2021年1月1日）となって出現したのです。
詳細は不明だが、どうやらSales[日付]がない（＝空白）となる6月1日以降、DATESINPERIODは
dCalendarの最も古い日付を抽出してくる
仕様になっているようで、下図の通り、2021年1月1日時点の値がこれに当たります。
なお、dCalendarの最も古い日には売上があったため、44という数字になったが、試しにdCalendarを2020年12月22日からスタートさせた場合、6月1日時点のフィルター条件が12/22となるものの、Salesテーブルにはその時点の売上がないため、[SalesAmt.7Days]は空白となってしまいました（下図）。
DATESINPERIODの仕様とは言え、さすがに気持ち悪い動作なので、[SalesAmt.7Days]のメジャーにKEEPFILTERSを加えて、フィルターコンテキストがDate列を上書き（Override）しないようにします。
※ date_filterは何も手を加えていないため、1-01（2021年1月1日）のまま
確かに44という数字は出現しなくなりましたが、[SalesAmt.7Days]は[SalesAmt]と同じ数字になり、このDAXは間違っていることになります。そこで、日付判定用のメジャーを作り、売上日付がない場合、それよりも先の日付を出現させないようにロジックを変更してみると、良さげな結果に。
これにてようやく一件落着。。。かと思いきや、実は最もやっかいなロジックエラーがこの式に存在しており、更に別の切り口別で見た場合に間違った結果を算出してしまうDAXとなっているのです。
非常に微妙で気づかないミスですが、例えば以下の切り口で見た場合がこれに相当します。比較のため、正解メジャーである[SalesAmt.7Days_〇]と[date_filter_〇]の2つを追加しています。この表を見ると、一番最初に目に入るのが大分類「U-Z」の値が違うこと、次に多くの大分類で[date_filer]と[date_filer_〇]の最終日が違っているのに気づくかと思います。
ここで[SalesAmt.7Days]というメジャーが何を算出しようとしているのかをもう一度考えてみます。
- 直近日から遡って7日間の累積売上
- 上表のように、日付のフィルターが掛かっていない場合、売上がある全期間における直近日から遡って7日の累積売上
- ？？？（最終的に考慮すべき条件）
最後の？？？がポイントになりますが、大分類「U-Z」をドリルダウンしてみます。
ドリルダウンしてみると、非常に面白い結果が出ました。正しいDAXである[SalesAmt.7Days_〇]は合計234、うち分類Vが90、分類Wが144となって、分類の合計は大分類の合計と一致します。一方、[SalesAmt.7Days]のほうは全ての分類に売上が計上されており、分類別の合計は全体合計（633）と一致しません。
なぜこのようなことが起こったかと言いますと、[date_filter]をベースとした[SalesAmt.7Days]は各分類にある商品で最後に売上が計上された日を基準として直近7日間の移動総計売上を算出しているのに対して、[SalesAmt.7Days_〇]は商品に関係なく、売上が計上された最終日（上表の場合、5/31）を基準に直近7日の移動総計売上を算出しているところにあります。微妙な違いですが、全体合計と各分類(あるいは商品)の合計が一致する正しい値を算出するのに必ず認識すべきポイントとなります。
最終的な正しいDAX式
最終的に使用するDAX式は以下の通りです。
この式のポイントはALLEXCEPTでSalesと紐づく全てのDimテーブル（この場合、商品マスタであるdProductのみ）によってかかるフィルターを無視（Expanded Tableの概念、詳述は省略）し、dCalendarのフィルターだけを尊重することにあります。これにより、CALCULATEのフィルター条件が上記[date_filter_〇]となり、商品売上の有無に関係なく、最終売上日とリレーションシップがあるdCalendarでフィルターされた日付から遡って7日の累積売上が正しく算出できるようになります。
なお、CALCULATE( MAX( Sales[日付] ), ALLEXCEPT( Sales, dCalendar ) )はパターンとして覚えておくと非常に便利です。ALLEXCEPTの事例については以下を参照されたい。
まとめ
- TI関数は時系列メジャーを算出するのに非常にシンプルかつパワフルなDAXである
- 現在のフィルター条件を考慮したDAXを記述しないと、期待する結果を算出することはできない
- 洗練されたBIレポートを構築するため、どのような状態でも正しい結果を表示できるよう、DAX式やデータモデルに工夫をする
- 時系列メジャーの落とし穴はビジュアル（表やチャート）で判別しやすいものと、判別しにくいものがあり、後者は数字を精査しないと殆ど気づかない
