日次進捗管理を実現！日割り予算+日次実績モデリング

この記事は「現場で使えるPower BI 4つのモデリングパターン - 予実管理編」の連載記事です。紹介するモデリングパターンの中でも、唯一日別で予実比較できる「日割り予算+日次実績」を紹介します。

実績は毎日発生しているが、予算が月次単位の場合、きめ細かい進捗管理がやりにくくて「日々の進捗が見えにくい」ということが課題として出てきます。そこで、月次予算を日割りにして進捗を追っていけるためのモデリングが今回ご紹介するものです。

こんな方におすすめ

• 日々の予算進捗を細かく管理したい方
• 月次予算しかないが日次で実績を追いたい方
• 予算の日割り展開方法を知りたい方

この記事で学べること

• 月次予算を日割りに展開する方法
• 日次実績との組み合わせ方
• Power Queryでの日付リスト生成と予算按分

今回加工したデータのイメージは下記のとおりです。

この状態でデータがあれば可視化は難しくありません。問題は、この状態のデータを作り出すことです。

データ加工手順(Power Query)

データの加工はPower BIのデータ変換ツールであるPower Queryで行います。全ての工程をステップバイステップで説明しようとすると大変長くなってしまい、1記事では収まらないボリュームになってしまうため、ポイントに絞った説明になる点はご了承ください。

ステップ1：年月日リスト✕各種マスタで全パターン網羅したリストを作る

月次予算+月次実績の記事でも紹介した通り、予算外で予算データはないけれども実績だけあるパターン、予算データはあるけれども実績はないというパターンを全て網羅できるように、網羅リストを作成します。

なぜ全パターンの網羅が必要か？

前回の記事でも説明しましたが、以下のようなケースが頻繁に発生します：

• 予算はあるが実績がない（新製品など）
• 実績はあるが予算がない（予算外の売上）

通常の左結合だけでデータを突き合わせると、これらのデータが欠落してしまいます。

実装方法

各マスタにダミー列（値は全て1）を追加し、「クエリのマージ」で完全外部結合（FULL OUTER JOIN）を実行します。すべてのパターンを網羅したディメンションのリストを作っておき、日割り予算や日次実績を左結合でマージすれば、紐づかない場合でも行があるので、データの欠落は発生しません。

注意点

前回は月でしたが、今回は日なのでリストのデータ件数が増えます。ディメンション（カテゴリや地域等）が増えると、この増え方が爆発的になるので要注意です。

もし、予算データの予算外でも0円として必ず全項目分をカバーするという前提があれば、網羅リストを作らず、日割りした予算データを基軸に左結合する方がおすすめです。

ステップ2：予算データを日割りにしておく

Power Queryで予算データに必要な各種マスタ類を結合し、必要な項目を取得しておきます。日割りの仕方は、月別の予算金額を各月の日数で単純な割り算しました。

やり方は、日付のリストを作って予算データへマージして、データ件数を膨らませます。その後、予算金額を日数で割り算します。

日付リストの作成

列の追加→カスタム列で、「日付リスト」というカラムを追加します。この中は各月の日のリストです。

日付リスト
=let
    日付値 = [オーダー年月],
    年 = Date.Year(日付値),
    月 = Date.Month(日付値),
    月初日 = #date(年, 月, 1),
    月末日 = Date.EndOfMonth(月初日),
    日付リスト = List.Generate(
        () => 月初日,
        each _ <= 月末日,
        each Date.AddDays(_, 1)
    )
in
    日付リスト

日付リストのヘッダー部分の展開マークをクリックすると中身が展開されて、以下のようになります。月別のデータだったものが日別に膨らんでいます。

この状態で、列の追加→カスタム列で、「日割り予算」というカラムを追加します。下記の関数をいれて、月別予算を各月の日数で割り算します。

日割り予算
=let
    日付値 = [日付リスト],
    年 = Date.Year(日付値),
    月 = Date.Month(日付値),
    月初日 = #date(年, 月, 1),
    月末日 = Date.EndOfMonth(月初日),
    月の日数 = Date.Day(月末日)
in
    [売上予算] / 月の日数

すると、このように日別で予算金額の列が表示されます。単純な日割りなので、同じ月は同じ日数で割り算されます。よって、同じ月は同じ予算金額になっています。

オーダー年月や月別予算のカラムは不要になるので削除し、日付リストのカラム名をリネームしておきます。

ステップ3：実績明細データを日次集計しておく

事前準備

1. 実績明細データに各種マスタを結合
2. カテゴリ・地域情報を取得
3. オーダー日はそのまま使用（日次のため）

集計処理

実績データも各種マスタ類を結合し、必要なマスタの名称を取得しておき、オーダー日別・地域別・カテゴリ別に集計しておきます。やり方は、前回の記事と同じく、Power Queryでホーム→グループ化→詳細設定→グループ化の追加です。

ステップ4：網羅リストに予算データと実績データをマージする

マージと保存

1. ステップ1で作った網羅リストに日割り予算データを左結合でマージ
2. nullになった予算金額は0に置換
3. 日次実績も左結合でマージ
4. nullになった実績金額は0に置換
5. 「日割り予算+日次実績ファクト」として保存し、Power BI Desktopに戻る

これで、日割り予算+日次実績ファクトの出来上がりです。

モデリング設定(Power BI Desktop)

ここからはPower BI Desktopです。

カレンダーテーブルの作成

日別のカレンダーテーブルをDAX関数で自動生成します。

カレンダーテーブルが必要な理由

1. 年度集計が簡単：4月始まりの会計年度にも対応
2. 時間インテリジェンス関数が使える：前年同月、累計などの計算が容易
3. 期間フィルタが柔軟：四半期、半期などの集計も可能

今回用意したカレンダーテーブルも、DAX関数で作成しています。前回の記事と同じカレンダーテーブルが使えます。

リレーション設定

上記で作った日割り予算+日次実績ファクトを取り込んで、カレンダーマスタ、地域マスタ、カテゴリマスタとリレーションさせました。

メジャーの作成(前年同月実績や予算差等)

テーブルビューでデータを確認し、関数を作ります。テーブルビューを開いたら、ついでに金額や％のフォーマットを整えて設定しておくと、グラフや一覧表を作成した時にきれいに見せることができます。

作った関数は下記の通りです。全て新しいメジャーで作成しています。月別集計と全く同じ計算式で、全く同じ結果が得られました。

// 予算との差額を計算
予算差 = SUM('日割り予算+日次実績ファクト'[売上実績])-SUM('日割り予算+日次実績ファクト'[売上予算])

// 予算達成率を計算（0除算を回避）
予算比 = 
DIVIDE(
    SUM('日割り予算+日次実績ファクト'[売上実績]),
    SUM('日割り予算+日次実績ファクト'[売上予算]),
    BLANK()
)

// 前年同月の実績を取得
前年同月実績 = 
CALCULATE(
    SUM('日割り予算+日次実績ファクト'[売上実績]),
    DATEADD('カレンダーマスタ'[Date], -12, MONTH)
)

// 年度累計を計算（4月始まり）
年度累計実績 = 
CALCULATE(
    SUM('日割り予算+日次実績ファクト'[売上実績]),
    DATESYTD('カレンダーマスタ'[Date],"03/31") //4月始まり
)

可視化する

カード、棒グラフ、テーブルを使って可視化します。データも式もできているので、各項目をドラッグ後に見た目の調整をしただけです。

まとめ

このモデリングパターンの特徴

メリット

• 日々の予算進捗が可視化できる
• データ構造は前回の月次パターンと同じでシンプル
• Power BIの時間インテリジェンス関数が使える
• 前回作成したDAX関数がそのまま使える

デメリット・注意点

• Power Queryでの日割り処理が必要
• データ件数が月次の約30倍に増加する
• ディメンションが多いと爆発的にデータ量が増える
• 単純な日割りのため、実際の予算配分とズレが生じる可能性がある

実装のポイント

今回も、最も難しかったポイントは、データを加工する部分でした。前回と同様「データが抜け落ちないように網羅的にする」というところに難しさがありましたが、さらに日割りするという加工を取り入れたので、そこにポイントがあると思います。

日別か月別かということ以外、データの持ち方が全く同じなので、前回作ったのと全く同じ関数が使えました。

運用面での推奨事項

今回は、単純な日割りでしたが、実際の業務では、以下のような処理を入れて、より現実に沿った予算にしていくことになると思います：

• 予算を営業日だけに配分し、休日は0にする
• キャンペーン実施予定日だけに係数をかけて多く配分する
• 過去の実績比率で按分する

このデータ加工部分は難易度が一気に上がるポイントになります。月別の時もそうでしたが、Power Queryでの全ての加工にこだわらず、あらかじめその日割り計算がされているデータをDBやDWHに保管する方がおすすめです。

次回予告

次回は「月次予算+実績明細」パターンを紹介します。実績明細をそのまま活かしながら予算管理する方法を解説します。

【連載記事一覧】

1. 現場で使えるPower BI 4つのモデリングパターン - 予実管理編
2. Power BI初心者が絶対知っておくべき重要用語を解説
3. 月次予算+月次実績
4. 日割り予算+月次実績（本記事）
5. 月次予算+実績明細（次回）
6. 統合ファクト
7. 振り返り