⛳

日付テーブルを作成する

wfbi

2023/07/28に公開

日付テーブルを作成する

Power BIでは日付をキーとしてデータを紐づけて時系列の集計やレポートの表現のために、日付テーブル（カレンダーテーブル）を作成することが多い。

日付テーブル作成方法

概要

DAX関数のCALENDAR関数で連続する日付を持つDate列を作成し、その列をベースとして集計したい時系列に合わせた列を作成する。

詳細

Power BI Desktopのデータワークスペースで「新しいテーブル」をクリックする。

下記DAX式で日付テーブルのデータを作成する。

Calendar = 
VAR JSTToday = UTCTODAY() + TIME(9, 0, 0)  // UTCから日本時刻に変更
VAR ThisFiscalYear = if(MONTH(JSTToday) >= 4, YEAR(JSTToday), YEAR(JSTToday) - 1) // 整数型, 4月区切り
VAR BaseCalendar = CALENDAR("2010/01/01", JSTToday) // CALENDAR関数はDateという名前の単一の列があるテーブルを返す。Date列をベースに使用したい時系列の列を作成。

RETURN
ADDCOLUMNS(
    BaseCalendar,
    "Year_Num", YEAR([Date]), // 整数型
    "Year", FORMAT([Date], "yyyy年", "ja"), // テキスト型
    "Month_Num", MONTH([Date]), // 整数型
    "Month", FORMAT([Date], "mm月", "ja"), // テキスト型
    "Day_Num", DAY([Date]), // 整数型
    "Day", FORMAT([Date], "dd日", "ja"), //テキスト型
    "YearMonth_Num", VALUE(FORMAT([Date], "yyyymm", "ja")), // 10進数型
    "YearMonth", FORMAT([Date], "yyyy年mm月", "ja"), // テキスト型
    "FiscalYear_Num", if(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1 ), // 整数型, 4月区切り
    "FiscalYear", FORMAT(if(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1), "0000年度", "ja"), // テキスト型, 4月区切り
    "FiscalYear_FYi", FORMAT(if(MONTH([Date]) >= 4, VALUE(FORMAT([Date], "yy")), VALUE(FORMAT([Date], "yy")) - 1), """FY""00", "ja"), // テキスト型, 4月区切り
    "FiscalQuarters_Num", if(MONTH([Date]) >= 10, 3, if(MONTH([Date]) >= 7, 2, if(MONTH([Date]) >= 4, 1, 4))), // 整数型, 10,11,12月:3 7,8,9月:2 4,5,6月:1 1,2,3月:4 
    "FiscalQuarters", FORMAT(if(MONTH([Date]) >= 10, 3, if(MONTH([Date]) >= 7, 2, if(MONTH([Date]) >= 4, 1, 4))), "0Q", "ja"), // テキスト型, 10,11,12月:3Q 7,8,9月:2Q 4,5,6月:1Q 1,2,3月:4Q
    "Week_Num", WEEKNUM([Date], 2), // 整数型, 第2引数で最初の週をどう扱うかの設定, 2は年の最初の木曜日を含む週を1とする
    "Week", FORMAT(WEEKNUM([Date], 2), "第00週", "ja"), // テキスト型, 第2引数で最初の週をどう扱うかの設定, 2は年の最初の木曜日を含む週を1とする
    "DayofWeek_Num", WEEKDAY([Date], 2), // 整数型, 第2引数はどの曜日を1や0として扱うかの設定, 2は月曜日を1で日曜日を7とする
    "DayofWeek", FORMAT([Date], "aaa曜日", "ja"), // テキスト型
    "DaywithDayofWeek", FORMAT([Date], "ddaaa", "ja"), // テキスト型
    "RelativeDay", DATEDIFF(JSTToday, [Date], DAY), // 整数型
    "RelativeMonth", DATEDIFF(JSTToday, [Date], MONTH), //  整数型
    "RelativeFiscalYear", if(MONTH([Date]) >= 4, YEAR([Date]) - ThisFiscalYear, YEAR([Date]) - ThisFiscalYear - 1) // 整数型
)

日付の範囲を実データテーブル（Table）の日付列（Date）の範囲に合わせたい場合、BaseCaledarの算出式を下記のように変更する。

VAR BaseCalendar = CALENDAR(MIN('Table'[Date]), MAX('Table'[Date])) // 実データテーブル(Table)の日付列(Date)の最小値と最大値を利用して範囲指定する

各列に対してデータ型の変更や列で並べ替えなど必要に応じて修正する。
日付テーブルをデータテーブルの日付をキーにして紐づけることで集計ができる。

参考文献

BIによる業務改善推進Publication

とある企業で主にBI（Power BI）による業務改善推進しているCoEメンバーのPublication。 Power Platformの他サービスもそこそこ活用している。

日付テーブルを作成する

日付テーブル作成方法

概要

詳細

参考文献

Discussion