Power BI 勉強帳：日付（カレンダー）テーブル

Microsoft Power BI Advent Calendar 2018
12/4の記事です。

Power BIでレポートを作るようになったとき、最初に戸惑いを覚え、そしてコツをつかむと「わー便利」と感じることの１つが、「時系列の扱い」なんじゃないかと思います。

###日付テーブルでできるようになること

• 四半期＞月＞週など任意の階層で表示する
• 相対的に直近期間を常に表示する
• スライダーで表示期間を可変にする
• 「2018年12月4日(火)」など任意の表示形式を使う

###さらにタイムインテリジェンス関数でできること

• 相対的にずらした期間の値（昨年値、昨月値など）を出す
• 期間ごとの差分や比率（昨年対比、昨月比など）を出す
• 期間ごとの累計値（月別累計、日別累計など）を出す
• 移動平均を出す

などなど…。

この記事では、時系列取り扱いの基本となる日付（カレンダー）テーブルについて、私なりに使ってみて感じたことを紹介していきたいと思います。

##日付テーブルとは？
ごく簡単に表現すると、

• 「連続した日付」列を持ち、「年」「月」などの時系列の階層をもつテーブル
• 日付をキーにデータを紐付けることで、時系列の様々な集計やレポート表現を実現することができる

日付テーブルをつくる（DAX版）

とりあえずさくっと使いたいよ、という方のために、一発コピペできる列多めのバージョンをつくってみました。（後に書いてありますが、コピペ後、データ型の定義をしてください）

Power BI 王子こと清水優吾さんのスライドの39ページを参考に、自分が便利だと思った列を追加しています。

2018/12/5追記：「年月_数値」が最初から数値型になるようVALUEでラップしました。
2019/1/23追記：「相対年度」の式に誤りがあったので修正しました。済みません。。。

日付テーブル　(DAX版).

日付テーブル = 
VAR StartDate="2016/04/01"
VAR EndDate="2020/3/31"
VAR Today=UTCTODAY()+9/24
VAR ThisFY=if(MONTH(Today)>3,YEAR(Today),YEAR(Today)-1)
VAR BaseCalendar =CALENDAR (StartDate,EndDate)
RETURN

ADDCOLUMNS(
    BaseCalendar,
    "年_数値", YEAR ( [Date] ),
    "年_yyyy年",FORMAT([Date],"yyyy年"),
    "月_数値", MONTH ([Date]),
    "月_MM月", FORMAT ([Date], "MM月" ),
    "日_数値", DAY ([Date]),
    "日_DD日", FORMAT([Date],"DD日"),
    "年月_数値", VALUE(FORMAT([Date], "yyyyMM" )),
    "年月_yy年MM月", FORMAT ( [Date], "yy年MM月" ),
    "年度_数値",if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),
    "年度_0年度",FORMAT(if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),"0年度"),
    "年度四半期_数値", if(MONTH([Date])<4,4,if(MONTH([Date])<7,1,if(MONTH([Date])<10,2,3))),
    "年度四半期_0Q", if(MONTH([Date])<4,"4Q",if(MONTH([Date])<7,"1Q",if(MONTH([Date])<10,"2Q","3Q"))),
    "週_数値", WEEKNUM ( [Date], 1 ),
    "週_第00週", FORMAT ( WEEKNUM ( [Date], 1 ), "第00週" ),
    "曜日番号月曜から_数値", WEEKDAY ( [Date], 2 ),
    "曜日_aaa", FORMAT([Date],"aaa"),
    "日と曜日", FORMAT ( [Date], "DDaaa" ),
    "相対日付",DATEDIFF(Today,[Date],DAY),
    "相対月",DATEDIFF(Today,[Date],MONTH),
    "相対年度",if(MONTH([Date])>3,YEAR([Date])-ThisFY,YEAR([Date])-1-ThisFY)
    )

###コピペする場所
「モデリングタブ＞新しいテーブル」の数式バーです。

###コピペした後必ずすること
各列のデータ型の定義を確認し、必要に応じて変更します。

なお、キーとなる日付列（[Date]）は「日付型」にしておく必要があります。
テキスト型にしてはいけません。

###期間の調整
変数の「StartDate」「EndDate」の日付を変えます。
他のテーブルの値から、動的に指定することもできます。

最終日を動的に指定する.

//売上テーブルの最後の売上日を指定
VAR EndDate=MAX('売上'[売上日])

//売上テーブルの最後の売上日の次の年の3月31日までを指定
VAR EndDate=Date(YEAR(MAX('売上'[売上日]) )+1,3,31)

###列の調整
いらない列を削除し、足りない列を追加します。

だいたいぱっと見でアレンジできると思いますが、下記の記事で、関数含めカレンダーテーブル全般について解説されていて参考になります。
[Power BI] DAX入門(2) カレンダーテーブルの作成

なお、日本語の曜日名は、、リファレンスには載ってないですが、FORMAT関数の「aaa」で表せました。

###並び順を整える（曜日名）
曜日名は、そのままでは月、火、水…という順には並んでくれません。
曜日名列の並び順を、曜日番号を使って行うように設定する必要があります。

これにより、ビジュアルで曜日名を使ったときに、曜日番号順に並ぶようになります。

##日付テーブルを作ったら…
###データテーブルと紐づける
日付テーブルを作ったら、紐づけたいデータテーブルの日付列とリレーションをかけます。

「月別」などメッシュが毎日ではないデータでも、日付テーブルと紐づけるための日付列を用意しておきます。たとえば月別の場合、月初日、月末日などを用意します。
（「年月」「年」などで紐づけようとすると「多対多」の関係になってしまい紐づきません）

これにより、データテーブルのレポートに日付テーブルを使うことができるようになります。

##使ってみて便利だと思った列
###相対年、相対月、相対日付
今日、今月、今年を0として、後の場合+、前の場合-の数値で現した列です。

表示したい期間を、ページレベルフィルター・レポートレベルフィルター・ビジュアルレベルフィルターで「直近●ヶ月」「当月の1日から昨日まで」など設定することができて便利です。

また、相対的な時間を参照するメジャーを作る際にも、利用することがあります。

###コンパクトな文字形式の列
好みもあると思うのですが、日付関係はグラフの軸で使うことが多いので、ぎゅっとコンパクトな表現（「01水」「18年05月」など）が目にやさしいなと思います。

注意点としては、文字形式の昇順で時間順通りに並ぶようにする必要があります。（「1月」でなく「01月」とするなど）

###数値形式の列
数値形式の列は、スライサーをスライダー形式にすることができます。
一覧だと選択肢が多すぎる場合に便利かなと思います。（ただしスライダーでは連続した数値を指定できるので、年月をスライダーにすると「201840」のような値が選択できてしまいます）

###補足：列名は、ビジュアル側で修正できます
列名はビジュアル側で好きなように変更できます。

ですので、私は、元の列名はできるだけ説明的につけるようにして、ビジュアル側でコンパクトな名前に変更するようにしています。

##「日付テーブルをマークする」について
作った日付テーブルを右クリックすると、「日付テーブルをマークする」という選択肢が出てきます。

先人の方々の記事を読むと、

• 自分で日付テーブルをつくらなくても、日付列のあるデータテーブルを作成すると、Power BIによって自動的に裏側に「日付テーブル」が作成される
• 自動的につくられた「日付テーブル」は、日付階層やクイックメジャーなどで、良きように使われる
• とはいえ、自動で作られた「日付テーブル」だと、意図する結果が得られないことがままある
• そこで、明示的に自分で「日付テーブル」を作成し、置き換えることで、意図する結果が得られる

ということのようです。詳しい説明は下の参考記事の中にあります。

先人の記事

今回のぬるい説明を読んでから、さらにリンク先の詳しい説明を読むと、より理解が深まるのではないかなあと思います。

• [Power BI] DAX入門(2) カレンダーテーブルの作成（再掲）
• 日付テーブル はどうやって作るか、それが問題だ
• 日付テーブル は必要か、それが問題だ
• 日付テーブルとしてマークする とは
• 【PowerBIメモ】時系列データとうまく付き合うための整理・可視化・分析・予測Tips

最後まで読んでくださってありがとうございました。
初めて書いた記事なので、何かおかしいところがありましたら、やさしく教えていただけましたら嬉しいです。