テクテク日記

テクテク＝テクノロジー&一歩ずつ（テクテク）

Power BIで月次分析をさらにスマートに①

DAX / Data Model Power Query 分析 Power BI

Power BIは、時間単位まで詳細な分析を可能にする一方、財務分析など多くの企業では月次ベースの分析が一般的だと思われます。今回は、月次データの分析について考えていきたいと思います。

使用データモデル

使用するモデルはブログでいつも出てくる、在庫・売上のMonthlyデータをベースとしています。

  • テーブル
    • Sales: 201809～202103まで、売上実績データ（31ヵ月分）
    • Inv: 201809～202103まで、在庫実績データ（31ヵ月分）
    • dCalendar_monthly: Monthlyベースで作った日付テーブル（計31レコード）
      ※ dCalendar_dailyも本記事の途中で登場
    • dProduct: Sales, InvのProductをベースに作成した商品マスタ
  • リレーションシップ
    • 下図の通り、InvとSalesに対して、dProductとdCalendar_monthlyでリレーションシップを張ったもの

  • データの粒度
    • Monthlyデータ
    • 時系列分析が可能な切り口は月次・四半期・年度
    • 商品軸での分析はGroup、Category、Product

総じて、このモデルは月次ベースの在庫・売上を分析するため、Monthlyベースの日付テーブルで紐づけされたものとなります。

月次べースの日付テーブル vs 日次ベースの日付テーブル

Power BIで時系列分析を行う場合、SalesやInvテーブルのDate列をそのまま使用するのではなく、あらかじめ作成した日付テーブルをリレーションシップで関連付けることがベストプラクティスとされています。

通常、時間分析以外のほぼすべてのシナリオに対応できるため、日次（以下、Daily）ベースの日付テーブルの作成が推奨されています。SQLBIなど、有名なサイトでも日付テーブルのテンプレートが提供されており、Bravo for Power BIでも簡単に日付テーブルを作成する方法が提供されています。

Dailyベースの日付テーブルを作ることは時系列分析において時間分析（Hourly Analysis）以外、ほぼ全てのシナリオに対応できるため、あらゆるシナリオで使うべき、というバイアスが入っています。しかしながら、Monthlyのデータしかない場合、Dailyベースの日付テーブルだと、データの粒度がMonthlyの実績データと違っていることから、必ずしもDailyベースの日付テーブルを使用しないといけない、というルールはありません。

大切なことは、

Monthlyベースの日付テーブルを使用しないことが重要ではなく、使用する場合の動作と制限を理解してから使用する

ことが重要であると思います。

動作検証を行わずに”これは正しい”と信じるのは避けるべきですが、それだけでは説得力があるわけではありません。今回の記事は数年間にわたる私自身のBI構築経験に基づいて執筆されており、この経験に基づく知見が説得力を持つと考えています

ちなみに、「日付テーブルの議論」と同様に、「Power BIにおいて、多対多のリレーションシップは絶対避けるべき」という誤ったメッセージが存在しますが、本題と逸れるため、こちらは別途どこかのタイミングで話をしたいと思います。

以下では、日付テーブルについて、MonthlyベースとDailyベースそれぞれのメリットとデメリットについて見ていきます。一般的にはDailyベースの日付テーブルを使用することが推奨されていますが、ここではMonthlyベースの日付テーブルの利点と欠点に焦点を当てます。

  • MonthlyのPro（賛成）
    • 日付テーブルを構築するのがお手軽（M言語に慣れている人は特に）
    • (もしかすると)Dailyベースの日付テーブルを使った場合よりも、DAXクエリのパフォーマンスが向上するかもしれません
  • MonthlyのCon（反対）
    • Monthlyベースの日付テーブルを使用する場合、タイムインテリジェンス関数を使った計算でエラーが発生する可能性がある
    • Monthlyベースの日付テーブルを使用した場合、Dailyベースのデータに対する計算がやや難しくなることがある

細かいことを挙げるとキリがないため、ここでは先述の4つのメリット・デメリットについて説明します。まず、日付テーブルを作成するのが簡単である点において、以下のカスタム関数を使用するだけで、月次ベースの日付テーブルを簡単に作成することができます。

  • Monthlyベースの日付テーブルの作成（カスタム関数）
    ※ 列のデータ型は、後から列を追加することを考慮して、あえて以下のコードで設定していません
let
    Date_monthly = (year_month_day as date, months_to_go_back as number) =>
    let
        Source = 
            List.Generate(
            () => Date.StartOfMonth(year_month_day), 
            each true, 
            each Date.AddMonths(_, -1)
            ),
        KeepMonths = List.FirstN(Source, months_to_go_back),
        Date = Table.FromColumns({KeepMonths}, {"Date"}), 
        DateRecord = Table.AddColumn(Date, "Record", each Date.ToRecord([Date])), 
        Expanded = Table.ExpandRecordColumn(
            DateRecord, 
            "Record", 
            {"Year", "Month", "Day"}, 
            {"Year", "Month", "Day"}
        ),
        ChangeType = Table.TransformColumnTypes(
            Expanded, 
            {
                {"Date", type date}, 
                {"Year", Int64.Type}, 
                {"Month", Int64.Type}, 
                {"Day", Int64.Type}
            }
            )
    in
        ChangeType
in
    Date_monthly

空のクエリをPower BI Desktopから作り、上記コードを下のように張り付けます。

クエリ1をfnGetDateに名前を変更し、下記①と②に値を入力。

「year_month_day」というパラメーターは、直近の月を指定するため、年月日の形式で入力する必要があります。例えば、2022/3/20のような日付を入力します。一方、「months_to_go_back」は、直近の月から何ヶ月前のMonthlyの日付テーブルを作成するかを指定するパラメーターです。例えば、24を入力すると、24ヶ月前からMonthlyの日付テーブルが作成されます。

③の「呼び出し」をクリックすると、下図のように、Monthlyベースの日付テーブルが生成されます。

なお、Date列は全て各月の初日で統一していますが、個人的にはyyyy/mm/1で統一することで、実績テーブルにある日付列とのリレーションシップがより簡単に組み立てられると考えています（上記カスタム関数では、どのような日付を入力しても必ず直近月の月初が返ってきます）。

ちなみに、List.Generateという難しい関数を使っていますが、tanukiさんやPowerBIxyzさん、spumoniさんのブログが分かりやすいと思います。

可視化に際してのエラー

パフォーマンスについての話は最後に行うこととして、ここでは先程紹介したdCalendar_monthlyテーブルと実績データを使って、可視化を行ってみたいと思います。

  • Monthlyベース売上メジャー
----------------------
-- Measure: [SalesAmt]
----------------------
MEASURE Sales[SalesAmt] = SUM( Sales[NetSalesAmount] )
    FormatString = "#,0"

-------------------------
-- Measure: [SalesAmt.PY]
-------------------------
MEASURE Sales[SalesAmt.PY] =
    CALCULATE(
        [SalesAmt],
        SAMEPERIODLASTYEAR( dCalendar_monthly[Date] )
    )
    FormatString = "#,0"

「SalesAmt（売上）」と「SalesAmt.PY（前年売上）」という2つの指標は非常にシンプルなメジャーであり、これらを年月（YM）で可視化したものが下図です。

ここでは、複数の月を選択してみます。当然ですが、選択された月だけが表示されるグラフとなります。

しかしここでは、年月が連続していないYMを選択してみます。すると、チャートビジュアルでは問題ないものの、テーブルビジュアルではエラーが発生してしまいます。

この問題に遭遇したのは前の職場で在庫分析を行っていた時でした。実は、当時チャートの視覚化でも同じようなエラーが発生していたことを記憶しています。

これが前述の「MonthlyのCon（反対）」において、タイムインテリジェンス関数を使用した場合に、Monthlyベースの日付テーブルを使用すると、エラーが発生する項目があることを指しています。

ここで以下3つの質問が気になるところです。

  • Excelでやった場合はどうか？
  • Dailyベースの日付テーブルではどうか？
  • Monthlyベースでエラーを回避するためには、どうすれば良いか？
Excel Power Pivotでの検証

Excel Pivotで行うためには、いくつか方法がありますが、全て試してみます。

  1. Power BI DesktopからAnalyze in ExcelでPivotテーブルで検証
  2. Power BI ServiceからExcelで分析（Analyze in Excel）を検証
  3. Power Pivotにデータを読み込み、Power Pivotで検証

1ですが、Power BI Desktopから外部ツールのAnalyze in ExcelでExcel Pivotテーブルを起動し、Power BIと同じ条件で検証を行います。

ここまではPower BIと同じ手順ですが、年月（YM）のうち1つを除外しようとすると、同じエラーメッセージが表示されます。

一方、Power BI ServiceからExcelで分析を行った場合は以下のようになります。

  1. Power BI DesktopからPower BI Serviceへ発行
  2. エクスポート > Excelで分析

  3. 同じように実施してみるが、結果は従前と同じ

最後に、全てのデータをExcelに読み込んでExcel Power Pivotで表示した場合、どのような結果が得られるでしょうか？

  • ステップ
    • Power BI Desktopのクエリを全てコピー
    • ExcelのPower Queryエディタに全て貼り付け
    • 読み込んでリレーションシップとDAXを再構築
    • 同じ状態で比較

結残念ながら結果はやはり同じでした。

「Excelで分析」を行う場合でも、Excel Power Pivotを使用して分析を行う場合でも、動作がPower BI Desktopと同じであることが分かりました。

Dailyベースの日付テーブルでの検証

Dailyベースの日付テーブルの場合はどうでしょうか？

  • Dailyベースの日付テーブルの作成（カスタム関数）
 let
    Source = (start_year as number, last_year as number, optional country_code as nullable text) =>

    let
      Source = 
        List.Generate(
          () => [Date = #date(start_year, 1, 1) - Duration.From(1), Year = 0],
          each [Year] <= last_year,
          each 
          [
            Date = [Date] + Duration.From(1),
            Year = Date.Year(Date),
            Month = Date.Month(Date),
            Day = Date.Day(Date),
            StartOfMonth = Date.StartOfMonth(Date),
            YearMonthNumber = Date.Year(Date) * 12 + Date.Month(Date), 
            YearMonthName = Date.ToText(Date, "yyyy-MM"),
            QuarterNumber = 
              if 
                country_code <> "jp" then Date.QuarterOfYear(Date)
              else if 
                Date.QuarterOfYear(Date) - 1 = 0 then 4
              else 
                Date.QuarterOfYear(Date) - 1,
            QuarterName = "Q" & Number.ToText(QuarterNumber),
            YearQuarterNumber = Year * 4 + Date.QuarterOfYear(Date),
            YearQuarterName = 
              if country_code <> "jp" then Number.ToText(Year) & "-" & QuarterName
              else 
                Number.ToText( if QuarterNumber = 4 then Year - 1 else Year) & "-" & QuarterName,
            WeekdayNumber = Date.DayOfWeek(Date, Day.Monday),
            WeekdayName = Date.ToText(Date, "ddd"),
            WeekdayStart = Date.StartOfWeek(Date, Day.Monday)
          ]
        ),
      SkipFirst = List.Skip(Source, 1),
      ToTable = Table.FromList(SkipFirst, each {_}, type table [Items]),
      ExpandRecords = 
        Table.ExpandRecordColumn(ToTable, 
          "Items", 
          Table.ColumnNames(
              Table.FromRecords(
                  List.Select(Table.Column(ToTable, "Items"), each _ <> "" and _ <> null)
                )
            )
        ),
      ChangeDT = 
        Table.TransformColumnTypes(
            ExpandRecords,
            List.Transform(
              Table.ColumnNames(ExpandRecords), 
                each if Text.Contains(_, "Name") then {_, type text} 
                else if
                List.AnyTrue(
                          {
                            Text.Contains(_, "Number"),
                            _ = "Year",
                            _ = "Month",
                            _ = "Day"
                          }
                ) 
                then {_, type number} 
                else {_, type date} ) )


    in
      ChangeDT
in
    Source

コードが長くなってしまいましたが、開始年と終了年を指定するだけでDailyベースの日付テーブルを作ることができます。

データモデルは以下のようになります。

ここで、dCalendar_dailyベースでSalesAmt.PY_dailyというメジャーを作ります。

  • Dailyベース売上メジャー
MEASURE Sales[SalesAmt.PY_daily] =
    CALCULATE(
        [SalesAmt],
        SAMEPERIODLASTYEAR( dCalendar_daily[Date] )
    )
    FormatString = "#,0"

Monthlyベースの日付テーブルとは違い、Dailyベースの日付テーブルではタイムインテリジェンス関数が機能するようです。これが、Dailyベースの日付テーブルを使用したほうが良い理由の1つとなります。

なぜ、Dailyでは問題が発生せず、Monthlyではエラーが発生するのでしょうか？また、どうしてチャートビジュアルは両方の場合でも問題がないのに対し、Monthlyベースの日付においてテーブル（マトリックスも同様）ビジュアルではエラーが発生するのでしょうか？

仕様上の問題、というのが回答になると思いますが、ChatGPTに聞いてみました。

正しいかどうか分かりませんが、納得できそうで納得できない、「俺か、俺以外か」、そんな回答ですね。

Monthlyベースの日付テーブルでエラーの回避

では、Monthlyベースの日付テーブルでエラーを発生させないようにするためにはどうすれば良いか？答えは、カスタムタイムインテリジェンス関数をDAXで作ることです。

  • カスタムMonthlyベース売上メジャー
--------------------------------
-- Measure: [SalesAmt.PY_custom]
--------------------------------
MEASURE Sales[SalesAmt.PY_custom] = 
    var _current = SELECTEDVALUE( dCalendar_monthly[YearMonthNumner] )
    var _last_year = _current - 12
    var _result = 
        CALCULATE(
            [SalesAmt],
            dCalendar_monthly[YearMonthNumner] = _last_year,
            REMOVEFILTERS( dCalendar_monthly )
        )
    return _result
    FormatString = "#,0"

YearMonthNumberは、1ヵ月に1つのユニークな数字であり、これをベースに前年の売上を算出しています。YMでフィルターしているため、REMOVEFILTERSがないと、CALCULATEのフィルター引数から前年同月のYearMonthNumberを抽出することができません。そのため、フィルターを解除する必要があります。これにより、テーブルビジュアルが崩れず、チャートビジュアルと同じ結果を実現できました。

ただし、ここで気を付けないといけないのは、合計値の数字が消えていることです。SELECTEDVALUEはIF + HASONEVALUEと同じ仕様ですので、合計には複数のYearMonthNumberが存在することから、フィルター条件がBLANKとなり、結果的に合計もBLANKとなっています。

また、切り口をCategoryに切り替えても同じく、1つのCategoryに複数のYearMonthNumberが存在することから、こちらも空白となります。

なお、1ヵ月だけを選択した場合、Category別に数字が正しく算出されるようになります。

粒度の異なるデータを追加

これまでの解説で、Monthlyベースの日付テーブルについてPro（日付テーブルがシンプル）とCon（テーブルビジュアルにてエラーが発生する）の両面をそれぞれ1つずつ見てきました。ここで、Conをもう一つ見てみます。

Monthlyベースのテーブルの弱点はずばり、

後から日付ベースのデータに対応することが難しい

ことです。

例えば、月次ベースの分析で十分だったが、後に日次ベースの分析が必要になった場合です。データモデルは以下の通りですが、非常に困ったことになってしまいます。

すなわち、Monthlyベースの日付テーブルは、売上の日次データを取得できるのは各月の初日だけということです。

通常、このようなケースには以下の2つのパターンが考えられますが、1つ目のパターンが現実的なシナリオであるため、この場合は、最初からMonthlyベースの日付テーブルを使用しないことになります。

  1. Dailyベースからスタートし、Monthlyベースのデータを追加
  2. Monthlyベースからスタートし、Dailyベースのデータを追加

とはいえ、Monthlyベースのテーブルだけで何とかしたい場合は、以下のようなモデリングを行います。

  • Power QueryでStartOfMonth列を追加、もしくは
  • （データ量が多くない場合）計算列でStartOfMonth列を追加

今回は計算列を例にします。

追加されたStartOfMonthは各DateKey（Dailyベースの日付）の月初日付となりますので、以下のようにMonthlyベースの日付テーブルとリレーションシップを張れば良いわけです。

これにより、各月の数字は全て正しい数字になります。

この手法のもう一つのメリットは、日次ベースの数字を見ることができることです。さらに、以下のようにマトリックスビジュアルにすると、日次の階層にドリルダウンすることができます。

SalesAmtメジャーは月次ベースの数字となるため、すべての月で同じ数字が出力されます。一方、SalesAmt.DailyはSales[SalesKey]から取得された数字であり、19/01の全売上実績が含まれます。
この方法はメリットが多いように見えますが、実際にはSales[DateKey]を使用するため、同じテーブルにDailyベースの在庫を入れると数字が矛盾してしまいます。本来、Sales[DateKey]とInv[DateKey]の両方に対するリレーションシップを持ち、同じテーブル内にてSalesAmtとInvAmt（在庫金額）の日次別推移を表示させるためには、1つの日付テーブルを使用する必要がありますが、dCalendar_monthlyテーブルではこれができません。リレーションシップを作るためには、dCalendar_dailyテーブルが必要です。つまり、在庫・売上をDailyベースで分析する必要がある場合は、モデルを作り直すことが必要であり、最初からdCalendar_dailyの日付テーブルを使用するほうが良いことになります。

クエリパフォーマンスについて

かなり長くなってしまったので、次の記事にします。

まとめ

私見では、

Monthlyベースの日付テーブルは、データが月次であれば、その制限を理解した上であれば使っても問題ありません

と考えています。ただし、データの粒度が変わる可能性がある場合には、最初からDailyベースの日付テーブルを作ったほうが良いでしょう。結局のところ、モデルを作る人がユーザーエクスペリエンスを阻害しないようなレポート作りにどれだけ注力できるかにかかっていると言えます。