Power BI DesktopでCalendarテーブルを作成
2種類のテーブルをカレンダーテーブルでつなぐ
時系列データをBIツールで扱う場合、カレンダーテーブルを別に作り、計測データが入っているテーブルと紐付ける（リレーションを設定する）ことがあります。
例えば、以下のデータでは、新型コロナウイルスの日毎の陽性者数テーブルと死亡者数テーブルをカレンダーテーブルに紐づけています。
今回、簡易カレンダーテーブルを作りました。
使っているデータは「Our World in Data」の「Coronavirus Pandemic (COVID-19)」から取得しました。
「Power BI Desktop」を使い加工しています。
DAXを使い新しいテーブルを作成
左側の「データ」アイコン（表の形のアイコン）をクリックし、「新しいテーブル」を選びます。
以下のDAX式を使いました。
取得したテーブルの最小値（MIN関数を使用）と最大値（MAX）を指定することで、カレンダーの期間を設定しています。
calendarテーブル = CALENDAR(MIN('owid-covid-data_case'[date]), MAX('owid-covid-data_case'[date]))
次のテーブルが作成できます。
リレーションシップを設定しカレンダーを共有
後は、左側のパネルから「モデル」アイコンをクリックし、リレーションの設定をすれば、最初の画面の関係付けを付与したモデルが作成できます。
リレーションシップはテーブル間をドラッグ&ドロップすることで設定もできますし、画面上の「リレーションシップの管理」を使ってもできます。
新型コロナウイルスに関する２つのテーブルがカレンダーテーブルを間にはさみ紐づいた形になります。
カレンダーテーブルをx軸、左のy軸に陽性者数テーブル、右のy軸に死亡者数テーブルと設定することで、作成したグラフです。
DAXを使い「MM月」や「曜日」列などを追加
カレンダーテーブルにもう少し情報を追加します。
右側のアイコン「データ」をクリックして「テーブルツール」→「新しい列」を選んで「年」や「月」、「曜日」列などを追加します。
# 「年」列を追加する式
YYYY = YEAR('calendarテーブル'[Date])# 「月」列を追加する式。一桁の数字を返す
MONTH = MONTH('calendarテーブル'[Date])# 「月」列を追加する式。MMの形式で返す
MONTH_MM = FORMAT('calendarテーブル'[Date], "MM")# M月のフォーマットで返す。日付型の「月」を文字型に変更し、"月"を結合
MONTH_月 = CONCATENATE(CONVERT('calendarテーブル'[MONTH], STRING), "月")# 「曜日」列を追加する式。一桁の数字を返す
WEEKDAY = WEEKDAY('calendarテーブル'[Date])# FORMAT関数で"aaa"と指定すると日本語一文字を返す
WEEKDAY_曜日 = FORMAT('calendarテーブル'[Date], "aaa")上記の式を使って追加した列です。
FORMAT関数をもう少し使う
上記で曜日表記のために使用した「FORMAT」関数。
調べてみると、「MM月」の記述スタイルにも対応できるなど便利なことがわか理り、もう少し使ってみました。
# 「YYYY年」表記
YYYY年 = FORMAT('calendarテーブル'[Date], "yyyy年")# 月を「MM」と2桁の数字
MONTH_月2 = FORMAT('calendarテーブル'[Date], "mm")# 「YYYY年MM月」表記
YYYY年MM月 = FORMAT('calendarテーブル'[Date], "yyyy年mm月")上記関数を追加したカレンダーテーブルです。
参考サイト