[Power BI] [やってみたシリーズ] DAXはじめの一歩 翌営業日の算出

はじめに

この記事は、Power BIで翌営業日を求める方法を記載しています。

私自身はPower BIもDAXもまだまだ初心者なので、この方法を調べるにあたって躓いたことなども載せています。なので、考え方や調べ方なんかも、共有して同様に学習されている方の参考となればという想いで記載しています。
またこの方法で上手くいかないケースもあると思いますし、この方法がベストではないです。なので実現方法の一例として捉えていただき、本質的にはLearnで学習するのが良いと思います。

そしてこの記事を公開するにあたっては、Power BIのユーザーコミュニティであるPower BI勉強会のDAX Boot Campにてアドバイスいただいた結果も書いております。

この記事の目的

土日や祝日を考慮した翌営業日を算出する方法って？というお題への私なりの回答です。
例えば、とある店舗にて「注文依頼があった日時」と「発送した処理をした日時」を記録しているデータを分析するとして、発送処理の期限である「依頼があった日の翌営業日」中に実施するというルールだったとします。
そのルールが遵守できているかを調べる際に、とある日付の翌営業日を算出する方法が必要となります。

要約（やってみたこと）

祝日テーブルとカレンダーテーブルを作り、土・日・祝日を除く翌営業日を算出する列をDAX式で追加する。

実践内容（やってみよう！）

1. 祝日テーブルの作成（非営業日の一覧テーブル）

既に作っているものを使えば良いです。
祝日と言ってますが、会社やお店独自のお休みの日の一覧です。

( ..)φメモメモ
► 祝日テーブル（内閣府が出しているCSV。翌年分まである。）
https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv
► PowerBIによる祝日判定
PowerBIで祝日判定したり、前年同時の曜日を判定したりする｜藤澤翔馬｜notePowerBIで祝日判定したり、前年同時の曜日を判定したりする｜藤澤翔馬｜note
※上記サイトと祝日テーブル（内閣府CSV）を使って判断する。

ここでは上記を参考に以下の手順で作成します。
（念のためですが、Power BI Desktopでの操作です。）

(1) ホーム > データの取得 > Web

(2) URL欄に以下URLを入力

祝日テーブルCSV

https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv

(3) 「読み込み」を押下

(4) データビューにて確認

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

これも既に作っているものを使えば良いです。
ここでは以下のDAX式で作成したものを使います。

カレンダーテーブル DAX式

カレンダー = 
    VAR BaseCalendar =
        CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2024, 12, 31 ) )   //日付幅
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR FYDate = EDATE(BaseDate,-3 )
        RETURN ROW (
            "日付", BaseDate,
            "曜日", SWITCH ( WEEKDAY ( BaseDate, 1 ) , 1, "日", 2, "月", 3, "火", 4, "水", 5, "木", 6, "金", 7,"土" ),
            "曜日_祝日含", IF ( ISEMPTY ( FILTER ( 'syukujitsu', 'syukujitsu'[国民の祝日・休日月日] = BaseDate ) ), FORMAT ( BaseDate, "aaa" ), FORMAT ( BaseDate, "祝" ) )
        )
    )

(1) データビュー > テーブルツール > 新しいテーブル

(2) 上記DAX式をコピペして、Enterキー or （左上の）✓ボタン を押下

3. カレンダーテーブルを日付テーブルとしてマークする

👆コレは忘れがちなので敢えて章出ししておきます。
（私はいつも忘れます…）

(1) データビュー > （右ペイン）カレンダーテーブルを選択 > 日付テーブルとしてマークする を押下

(2) 日付列として[Data]を選択し、OK を押下

(3) （右ペイン）Date にマークが付いたことを確認　（何マーク？）

4. 翌営業日を算出する

カレンダーテーブルにDAX式で列を追加します。

注意！
追加するのは列です。メジャーと間違わないように！
　○：新しい列
　✖：新しいメジャー

今回使うのは次の式です。

翌営業日 DAX式

翌営業日 = 
    VAR _CurrentDate = 'カレンダー'[Date]
    VAR _NextDate = _CurrentDate + 1
    VAR _NextBusinessDate = 
        CALCULATETABLE ( 
            'カレンダー', 
            NOT ( 'カレンダー'[曜日_祝日含] IN {"土","日","祝"} ), 
            'カレンダー'[Date] >= _NextDate 
        )
RETURN 
    MINX ( _NextBusinessDate, [Date] )

上記が私が自分で書いたもの。
以下がPower BI勉強会にてアドバイスをもらったもの。
まぁスッキリ　わかりやすい記述が大事ですね。

翌営業日 DAX式（改）

翌営業日 = 
    VAR _CurrentDate = 'カレンダー'[Date]
RETURN 
    CALCULATE ( 
        MIN ( 'カレンダー'[Date] ),
        NOT ( 'カレンダー'[曜日_祝日含] IN {"土","日","祝"} ), 
        'カレンダー'[Date] > _CurrentDate
    )

(1) データビュー > テーブルツール > 新しい列

(2) 上記DAX式をコピペして、Enterキー or （左上の）✓ボタン を押下

(3) （右ペイン）翌営業日が追加されていることを確認

(4) （画面下部）テーブルに翌営業日が追加され、全ての行に日付が入っていることを確認

5. 検証、確かめ算

翌営業日にちゃんとなっているか確認します。

(1) データビューでカレンダーテーブルを表示させ、Date列の ▼ ボタンを押下

(2) 昇順で並び替え を選択

(3) 以下のテーブルで確認していきます。

• ①の赤枠（1行目）
  ここは、2020年1月1日(水)の行です。1月1日は祝日ですが、翌日は1月2日(木)が営業日なので、翌営業日に1月2日と入ります。
  （三ヶ日（1/1-1/3）まで休みだよーって場合は、祝日テーブルをExcelとかで個別に作って取り込めばよいかな、と。）
• ②の赤枠
  土日をまたぐ場合の翌営業日の算出を確認します。
  2020年1月3日(金)の翌日・翌々日は土日なので、翌営日は1月6日(月)になります。
• ③の赤枠
  土日に祝日が絡むパターンです。
  2020年1月10日(金)の翌日以降、土日に続き、1月13日(月)は祝日です。
  なので、1月10日～1月13日の翌営業日は1月14日となります。

ここまでで翌営業日の算出は完了です。
いかがでしたか？できました？

解説　( ..)φメモメモ…

ここからは私の備忘がてら、DAX式の解説です。

(1) 改訂前のDAX式の解説

翌営業日 DAX式

翌営業日 = 
    VAR _CurrentDate = 'カレンダー'[Date]
    VAR _NextDate = _CurrentDate + 1
    VAR _NextBusinessDate = 
        CALCULATETABLE ( 
            'カレンダー', 
            NOT ( 'カレンダー'[曜日_祝日含] IN {"土","日","祝"} ), 
            'カレンダー'[Date] >= _NextDate 
        )
RETURN 
    MINX ( _NextBusinessDate, [Date] )

「_BusinessDate」変数の算出式で使用している CALCULATETABLE関数 は、第一引数で渡したテーブル型に対して、第2・第3引数で条件指定をして、戻り値としてテーブル型を返しています。つまり、カレンダーより土日・祝日を除いて、且つ翌日（「_NextDate」変数）以降の一覧が、「_BusinessDate」変数に入っています。

詳しくはこちらを。

そして最後の行、RETURNで返しているのが、「_BusinessDate」の日付の最小値（MINX関数）になります。「_BusinessDate」変数の条件として、翌日以降としているので、最小値は翌日となります。加えて、土日・祝日の除いているので、翌日が土日・祝日にあたる場合は、その翌日が取得できます。

詳細はこちらを。

(2) 改訂後のDAX式の解説

改訂版のこちらも解説

翌営業日 DAX式（改）

翌営業日 = 
    VAR _CurrentDate = 'カレンダー'[Date]
RETURN 
    CALCULATE ( 
        MIN ( 'カレンダー'[Date] ),
        NOT ( 'カレンダー'[曜日_祝日含] IN {"土","日","祝"} ), 
        'カレンダー'[Date] > _CurrentDate
    )

CALCULATEの第2・第3引数でフィルターの条件が指定されてます。

NOT ( 'カレンダー'[曜日_祝日含] IN {"土","日","祝"} ),
この部分で土・日・祝以外を除いてます。

'カレンダー'[Date] > _CurrentDate
この部分でカレンダーテーブルの[Date]列、つまりカレンダーテーブルのそれぞれの行の日付よりも大きい日付を条件としています。
要するに、当日より大きい＝翌日以降って条件になってます。

そしてフィルターした結果を基に、CALCULATEの第1引数を実行しています。
MIN ( 'カレンダー'[Date] ),
フィルター部でカレンダーテーブルが絞られた状態、つまり土・日・祝を除いて、且つ翌日以降のカレンダーテーブルの中で、一番小さい[Date]（日付）をとってます。
結果として、土日祝日を除いた翌営業日を取る列が出来上がります。

ちなみにVAR _CurrentDate = 'カレンダー'[Date]この部分が必要なの？と想い。
'カレンダー'[Date] > _CurrentDateの部分を書き換え、以下のようにしてみたら、結果がBLANKでした。。。
（変数に入れるって意味がちゃんと理解できていないので、ここは宿題としてのちほど、記事を更新したいと思います m(_ _)m）

翌営業日 DAX式（ダメな例）

翌営業日（ダメな例） = 
    CALCULATE ( 
        MIN ( 'カレンダー'[Date] ),
        NOT ( 'カレンダー'[曜日_祝日含] IN {"土","日","祝"} ), 
        'カレンダー'[Date] > 'カレンダー'[Date]
    )

(3) 日付テーブルをマークする の話

ちなみに「日付テーブルとしてマークする」のを忘れると、こんな感じで翌営業日がブランクになります。
これは、この部分'カレンダー'[Date] >= _NextDateが、処理できていないためです。
（この辺の説明がまだ上手くできないので、、、精進して記事を更新しようと思います。）

「日付テーブルにマークする」をしなくても、モデルビューで祝日テーブルとカレンダーテーブルをつなげ、
カレンダーテーブルの[Date][翌営業日]の日付型に設定することで、同じDAX式でも算出することができます。

「日付テーブルをマークする」については詳しくはこちらを。

(4) DAX式が返すBLANKの話

これもPBIJP Boot Campでアドバイスもらった内容ですが、ここで作った式（翌営業日の列）がBLANK（空っぽ）になることがあります。
今回の場合、以下のようにカレンダーテーブルに設定した最終日は、翌営業日の列がBLANKになってます。
これはCALCULATE式で'カレンダー'[Date] > _CurrentDateの条件にしているためです。

こんな場合はISBLANK関数で判別してあげる必要があります。

（今回のカレンダーテーブルに入れる翌営業日の列のDAX式だと、最終日のBLANKの回避方法が思い浮かばず…ここも宿題にしたいと思います。やるとしたら最終営業日を使う側のメジャーでBLANK判定を入れておくのを忘れないようにするってことかな？）

BLANKについてはこちらを。

さいごに

つたない記事を最後まで読んでいただき、ありがとうございます！

昨年のMicrosoft Power BI Advent Calendar 2022に続き、2回目(前回はこちら)の投稿です。Power BI Weekly News(Youtube)で紹介いただいき、おススメのやってみたシリーズを書いてみました。
ご指摘やご意見など、何なりとコメントいただけますと幸いです。

読んでいただいた方、このようなきっかけをいただいたコミニティ活動をされている皆様には、感謝申し上げます m(_ _)m