Power BIで年度/半期/四半期ごとの累計計算をする方法！会計年度はじまり月切替も！

Power BIで年度/半期/四半期ごとの累計計算をしたいときはないでしょうか。

けど、そんな中で悩むことは、

・Power BIで年度/半期/四半期ごとの累計計算をする方法がわからない
・PowerBIの会計年度を4月はじまりや10月はじまりにして累計計算をしたいが難しくて実現できない

ですよね。

今回はそんなお悩みを解決する

・Power BIで年度/半期/四半期ごとの累計計算をする方法
・PowerBIの会計年度のはじまり月を変え累計計算をする方法

についてまとめます！

タカヒロ

DAX（Data Analysis Expressions）を使用するので難しく感じることがありますが、コピペ＆かんたん設定でサクッといけますのでご安心ください。

Power BIでの会計年度単位の累計計算について

Power BIでの会計年度単位の累計計算について説明します。

会計年度単位の累計計算は、特定の期間（例えば、会計年度や半期）におけるデータの合計を求める方法です。

例として、会計年度が4月始まりの場合、4月から9月までの半期累計を計算することができます。この計算では、4月はその月の累計、5月は4月と5月の累計、9月は4月から9月までの累計となります。

このような累計計算を行うために、Power BIでは「DAX（Data Analysis Expressions）」という言語を使用します。

DAXには様々な累計関数が用意されており、その中でもTOTALYTD（年度累計）、TOTALQTD（四半期累計）、TOTALMTD（月累計）があります。
ただし、半期累計のような特定の期間の累計計算には専用の関数がないため、カスタム式を作成する必要があります。

また、累計計算を行うには、対象となるデータテーブル内に日付列が必要です。この日付列を基に、累計値を計算します。

会計年度の期間設定では、日本の会計年度の開始月は4月ですが、Power BIのデフォルト設定では1月から始まります。

この設定を変更するには、DAX式で直接設定する方法と、会計年度カレンダーテーブルを作成して設定する方法があります。
特に後者の方法は、開始月を一箇所変更するだけで、全てのグラフに反映されるため便利です。

タカヒロ

小難しく感じますが、基本コピペですので、サクッと実装してためしてみましょう！

サンプルデータの作成

まずはグラフを作るためにサンプルデータをPowerBIへ取り込みましょう。

サンプルデータはエクセルで、架空の2023年売り上げデータとし、

項目及び内容は以下のようにしました。
シート名は「2023年_売上データ」にするようお願いします。

金額、商品名はランダムにいれてあります。
以下のExcel数式を入れると簡単にダミーデータが作成できますのでぜひご利用ください。

A列：=SEQUENCE(365,,DATE(2023,1,1),1)

B列：=RANDBETWEEN(5000, 20000)　※下までドラッグ

C列：=CHOOSE(RANDBETWEEN(1, 3), "商品A", "商品B", "商品C")　※下までドラッグ

PowerBIに戻り、リボンメニューのエクセルアイコンをクリックし、

ファイルを選択し、

読み込みボタンを押して取り込みます。

会計年度カレンダーテーブルの作成

次に会計年度のカレンダーテーブルを作成します。

DAX式のコードの説明は以下の記事にまとめていますので、こちらもご参考ください。
Power BIで年度/半期/四半期を設定する方法！4月/10月はじまり月切替可！

左ペインの「テーブルビュー」をクリックします。

リボンメニューの「新しいテーブル」をクリックします。

数式フィールドに以下DAX式をコピペします。
デフォルトでは4月はじまりの会計年度が設定されています。

会計年度カレンダー = 
var CenterYear = 2023 // 中心の年を設定
var FiscalYearStartMonth = 4 // 会計年度の開始月をここで設定
var StartDate = DATE(CenterYear - 1, 1, 1) // 中心の年の1年前を設定
var EndDate = DATE(CenterYear + 1, 12, 31) // 中心の年の1年後を設定
var MotoCalendar = CALENDAR(StartDate, EndDate)
RETURN
    GENERATE(
        MotoCalendar,
        var MotoDate = [Date]
        var YearNumber = YEAR(MotoDate)
        var MonthNumber = MONTH(MotoDate)
        var FiscalYear = IF(MonthNumber >= FiscalYearStartMonth, YearNumber, YearNumber - 1)
        var NendoVal = FiscalYear & "年度"
        var ShihankiVal = CONCATENATE("第", QUARTER(EDATE(MotoDate, (FiscalYearStartMonth-1)*-1)))
        var HankiVal = IF(MonthNumber >= FiscalYearStartMonth && MonthNumber < FiscalYearStartMonth + 6, "上期", "下期")

        RETURN ROW(
            "年度", NendoVal,
            "年度月", CONCATENATE(NendoVal, FORMAT(MotoDate, "MM月")),
            "年度四半期", CONCATENATE(CONCATENATE(NendoVal,ShihankiVal),"四半期"),
            "年度半期", CONCATENATE(NendoVal,HankiVal),
            "半期", HankiVal,
            "四半期", CONCATENATE(ShihankiVal,"四半期"),
            "四半期_Q", CONCATENATE(ShihankiVal,"Q"),
            "年", FORMAT(MotoDate, "yyyy年"),
            "月", FORMAT(MotoDate, "MM月"),
            "日", FORMAT(MotoDate, "dd"),
            "年月", FORMAT(MotoDate, "yyyy年MM月")
        )
    )

以下のように表示されたらカレンダーテーブルの実装は完了です。

データテーブルとカレンダーテーブルを関連付ける

仕上げにデータテーブルとカレンダーテーブルを関連付けていきましょう。

左ペインの「モデルビュー」をクリックします。

データテーブルとカレンダーテーブルの日付列を関連付けます。

サンプルでは売り上げデータテーブルの「日付」を日本語変換カレンダーテーブルの「Date」へドラッグします。

ラインがつながればテーブル間のリレーションが作成されたことになります。

会計年度の累計売上高を表示するグラフを設定

会計年度の累計売上高を表示するグラフを作成してみましょう。

まずは以下のように年度と半期をまとめて表示させます。

年度期間の年間累計表示にする

年度期間の年間表示をしてみましょう。

年度期間の年間累計売上を計算する場合、TOTALYTD関数を使用します。
この関数は、特定の年期間にわたる累計値を計算します。

基本的なDAX式は次のようになります。

累計売上 = TOTALYTD(SUM(売上データ[売上金額]), 売上データ[日付], "3/31")

ここで、TOTALYTD関数は次のパラメータを設定します。

売上金額の合計を計算する式です。

SUM(売上データ[売上金額])

計算に使用する日付フィールドです。

売上データ[日付]

年末の日付を文字列で指定します。
この日付は、年間累計を計算する際の基準となる年末日です。
4月から翌年3月末までの期間における売上金額の累計を計算します。

"3/31"

今回のサンプルテーブルを設定する場合、DAX式は以下となります。

年度年間累計売上 = TOTALYTD(SUM('2023年_売上データ'[売上金額]), '2023年_売上データ'[日付],"3/31")

こちらを

右ペインのデータエリアにある「2023年_売上データ」テーブルの「…」をクリックし、
「新しいメジャー」を選択します。

数式入力フィールドへ上記DAX式を入力します。

次にレポートビューに戻り、可視化エリアから「縦棒グラフ」を選択し、
先ほど作成したメジャー「年度年間累計売上」ををグラフのY軸に設定します。
X軸、凡例は以下を参考に設定してください。

プレビューしてみましょう。

4月を起点に年間累計売上高が表示されましたね！

四半期累計表示にする

次は四半期表示にしてみましょう。

同じく縦棒グラフを選択します。
または先ほどの作成したグラフをコピペして複製してもOKです。

「2023年_売上データ」テーブルの「…」をクリックし、「新しいメジャー」を選択、

数式入力フィールドへ以下DAX式を入力します。

四半期累計売上 = TOTALQTD(SUM('2023年_売上データ'[売上金額]), '2023年_売上データ'[日付])

「2023年_売上データ」テーブルの「四半期累計売上」をグラフのY軸に設定します。

四半期累計表示になりましたね！

半期累計表示にする

次は累計関数にない半期の累計を計算してグラフに表示してみましょう。

半期の計算をDAX式内に記述すると複雑になるため、ちょうど作成した会計年度カレンダーテーブルの
「年度半期」フィールドがありますので、こちらを基準に半期判定をおこない、累計するDAX式に仕立てました。

DAX式は以下になります。

半期累計売上 = 
VAR SelectedHalf = SELECTEDVALUE('会計年度カレンダー'[年度半期])
RETURN
CALCULATE(
    SUM('2023年_売上データ'[売上金額]),
    FILTER(
        ALLSELECTED('2023年_売上データ'[日付]),
        ISONORAFTER('2023年_売上データ'[日付], MAX('2023年_売上データ'[日付]), DESC)
    ),
    '会計年度カレンダー'[年度半期] = SelectedHalf
)

「2023年_売上データ」テーブルの「…」をクリックし、「新しいメジャー」を選択、

数式入力フィールドへ上記DAX式を入力します。

縦棒グラフを選択します。
または先ほどの作成したグラフをコピペして複製してもOKです。

「2023年_売上データ」テーブルの「半期累計売上」をグラフのY軸に設定します。

半期の累計表示になりましたね！

半期累計のDAX式の説明

半期累計のDAX式の説明をします。

この式全体の動作としては、ユーザーが選択した会計年度の半期に基づいて、2023年の売上データから特定の期間（最新日付から選択された日付まで）の売上金額の合計を計算します。

現在選択されている会計年度の半期を取得します。例えば、「上期」や「下期」などの値を持ちます。

SELECTEDVALUE('会計年度カレンダー'[年度半期])

CALCULATE関数は、特定の条件下での合計（この場合は売上金額の合計）を計算します。

CALCULATE

2023年の売上データテーブルから売上金額の合計を計算します。

SUM('2023年_売上データ'[売上金額])

現在フィルタされている日付の範囲を無視し、売上データの全ての日付を選択します。

ALLSELECTED('2023年_売上データ'[日付])

選択された最新日付から過去の日付に向かってデータをフィルタリングするための条件です。
要するに、最新日付から選択した日付の範囲内のデータを取得します。

ISONORAFTER('2023年_売上データ'[日付], MAX('2023年_売上データ'[日付]), DESC)

この条件は、選択された会計年度の半期に一致するデータのみを対象に計算を行うよう指定しています。

'会計年度カレンダー'[年度半期] = SelectedHalf

会計年度のはじまり月を変え累計計算をする方法

会計年度のはじまり月を変え累計計算をする方法について説明します。

イメージとしては、「会計年度カレンダー」のDAX式ではじまり月を設定している値を変更すると、
年度、半期、四半期すべての累計計算グラフが設定したはじまり月から再計算されていきます。

タカヒロ

一か所の修正で会計年度カレンダーと累計計算、そしてすべての累計グラフが一括で変更されるのでとても便利ですね。

会計年度カレンダーのはじまり月を変更する

変更する箇所は、会計年度カレンダーのはじまり月を設定している値です。

var FiscalYearStartMonth = 4

ここを1月に変更してみましょう。
データ欄の「会計年度カレンダー」をクリックし、数式フィールドが表示されたら以下の箇所を変更します。

var FiscalYearStartMonth = 1

設定したら数式フィールドのチェックボタンを押し、反映させましょう。

これで設定は完了です。

会計年度の年間累計売上の数式を変更

次は会計年度の年間累計売上の数式を変更します。

タカヒロ

年間累計はTOTALYTD関数が用意されていますが、はじまり月を変更する際はこの関数の第三引数を都度変更しなければなりません。
今回はどうしても1か所の変更ですべての累計計算とグラフへの反映をしたかったので、カスタム版DAX式とさせていただきました。
こだわりと思ってもらえばと思います…

上記半期の累計計算をベースに期間を1年に変更します。

年度年間累計売上-改 = 
VAR SelectedHalf = SELECTEDVALUE('会計年度カレンダー'[年度])
RETURN
CALCULATE(
    SUM('2023年_売上データ'[売上金額]),
    FILTER(
        ALLSELECTED('2023年_売上データ'[日付]),
        ISONORAFTER('2023年_売上データ'[日付], MAX('2023年_売上データ'[日付]), DESC)
    ),
    '会計年度カレンダー'[年度] = SelectedHalf
)

年間累計売上グラフをコピーするか、縦棒グラフを選択し、
Y軸に「年度年間累計売上-改」を設定します。