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

[Microsoft Power BI Advent Calendar 2018](https://qiita.com/advent-calendar/2018/powerbi)
12/4の記事です。

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

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


###さらにタイムインテリジェンス関数でできること
- 相対的にずらした期間の値（昨年値、昨月値など）を出す
- 期間ごとの差分や比率（昨年対比、昨月比など）を出す
- 期間ごとの累計値（月別累計、日別累計など）を出す
- 移動平均を出す

などなど…。

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

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

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

**Power BI 王子**こと清水優吾さんの[スライド](https://www.slideshare.net/yugoes1021/power-bi-91001290)の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)
    )

```
###コピペする場所
「**モデリングタブ＞新しいテーブル**」の数式バーです。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/221646/6b47f32b-35a0-75c3-0b36-db52be35d318.png)

###コピペした後必ずすること
**各列のデータ型の定義**を確認し、必要に応じて変更します。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/221646/00c93bac-f29e-1c0c-5793-5d8430a2b94e.png)

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

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

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

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

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

```

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

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

なお、日本語の曜日名は、、[リファレンス](https://docs.microsoft.com/ja-jp/previous-versions/sql/sql-server-2014/ee634398(v=sql.120))には載ってないですが、FORMAT関数の「aaa」で表せました。

###並び順を整える（曜日名）
曜日名は、そのままでは月、火、水…という順には並んでくれません。
曜日名列の並び順を、曜日番号を使って行うように設定する必要があります。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/221646/33dccce6-9cbe-b636-bab8-255ddb783bae.png)

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

![image.png](https://qiita-image-store.s3.amazonaws.com/0/221646/6ab96613-7f6a-04cc-5518-2cbd400c82b6.png)

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

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

![image.png](https://qiita-image-store.s3.amazonaws.com/0/221646/73d11ce2-f4c3-6416-fe3c-ac7a7cf1f58f.png)

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

![image.png](https://qiita-image-store.s3.amazonaws.com/0/221646/9f7ab1ab-be6c-3c3b-4a71-684574359024.png)

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

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

また、相対的な時間を参照するメジャーを作る際にも、利用することがあります。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/221646/209ba589-ddff-d3f4-47cb-edb169d60bd7.png)

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

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

![image.png](https://qiita-image-store.s3.amazonaws.com/0/221646/9da9ba57-4c55-7b86-0ee9-290b7dd49fbf.png)

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

![image.png](https://qiita-image-store.s3.amazonaws.com/0/221646/efd68b56-eb06-99da-758f-be8292fb514b.png)

###補足：列名は、ビジュアル側で修正できます
列名はビジュアル側で好きなように変更できます。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/221646/667d23b1-b038-bdef-80f4-8c95ae75867f.png)
ですので、私は、**元の列名はできるだけ説明的に**つけるようにして、**ビジュアル側でコンパクトな名前**に変更するようにしています。

##「日付テーブルをマークする」について
作った日付テーブルを右クリックすると、「日付テーブルをマークする」という選択肢が出てきます。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/221646/2da15dca-6471-4b62-8db2-90b02a3e0cca.png)

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

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

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


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

- [\[Power BI\] DAX入門(2) カレンダーテーブルの作成（再掲）](https://blogs.msdn.microsoft.com/dataplatjp/2016/07/11/dax2/)
- [日付テーブル はどうやって作るか、それが問題だ](https://qiita.com/PowerBIxyz/items/41b71651f9ddb9a16419)
- [日付テーブル は必要か、それが問題だ](https://qiita.com/PowerBIxyz/items/375573ebe78a48b6d7de)
- [日付テーブルとしてマークする とは](https://qiita.com/PowerBIxyz/items/4b2b17a091bf7e8bfec6)
- [【PowerBIメモ】時系列データとうまく付き合うための整理・可視化・分析・予測Tips](https://qiita.com/h_kobayashi1125/items/0db6efe91db7df821429)


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