タイトルには勢いがあってもよいと思うんです。
さて、日付テーブルを用意する方法について。手段はふたつある。
- 日付テーブル を クエリ エディター で準備し、データモデル にロードする
- 作成された データモデル に 日付テーブル を追加する
いずれであっても、年間、月間など期間での集計やそれらの比較など本来の目的は達成できる。準備に用いる言語が Power Query か DAX という違いが大きいところ。データモデル内に保存されていく仕組みには異なることがあるけれども、まずは気にしない。
日付テーブル は、
- 集計対象となる日付をすべて含み
- 連続した日付の列を持つ
- 集計に用いる単位(年や月など)は日付に関連するようそれぞれを列として持つ
| 日付 | 年 | 月 | 日 |
|---|---|---|---|
| 2016/01/01 | 2016 | 1 | 1 |
| 2016/01/02 | 2016 | 1 | 2 |
| ... | ... | ... | ... |
| 2017/12/30 | 2017 | 12 | 30 |
| 2017/12/31 | 2017 | 12 | 31 |
クエリ エディターで用意する
連続した日付のリストは こんな感じでも生成できるのだけど、
//
List.Dates( #date( 2017, 1, 1 ), 365, #duration( 1, 0, 0, 0 ) )
//
List.Transform(
{Number.From( #date( 2017, 1, 1 ) ) .. Number.From( #date( 2017, 12, 31 ) )}
, each Date.From(_)
)
せっかくなので、ファクトテーブル(集計対象となるテーブル)としてデータモデルにロードするクエリから日付データを抽出し 日付テーブルとしてロードするクエリを作成。
必要なのは最初と最後
| ID | 受注日 | ... | ... |
|---|---|---|---|
| ... | ... | ... | |
| ... | ... | ... | |
| ... | ... | ... | |
| という"受注"クエリが用意できたとして、"受注日"列から日付の範囲を特定 |
List.Min 関数 で 最も古い日付、 List.Max 関数で もっとも新しい日付を計算し抽出、それらを List.Dates 関数で日付の一覧(list) を作る。そして、list のままでは列の追加ができないのでテーブルに変換しておく
let
Source = 受注,
EarliestDate = List.Min(Source[受注日]),
LatestDate = List.Max(Source[受注日]),
Dates = List.Dates(
EarliestDate
, Number.From(LatestDate - EarliestDate) + 1
, #duration(1, 0, 0, 0)
),
ConvertedToTable = Table.FromList(
Dates
, Splitter.SplitByNothing()
, type table [日付 = date]
)
in
ConvertedToTable
年初や年末などを範囲にする場合には、Date.StartOfYear 関数 / Date.EndOfYear 関数 など使うとよい。
let
Source = 受注,
EarliestDate = List.Min(Source[受注日]),
StartOfYear = Date.StartOfYear(EarliestDate),
LatestDate = List.Max(Source[受注日]),
EndOfYear = Date.EndOfYear(LatestDate),
Dates = List.Dates(
StartOfYear
, Number.From(EndOfYear - StartOfYear) + 1
, #duration(1, 0, 0, 0)
),
ConvertedToTable = Table.FromList(
Dates
, Splitter.SplitByNothing()
, type table [日付 = date]
)
in
ConvertedToTable
列を追加する
カスタム列の追加 もしくは、例からの列の追加 でテーブルに列を追加していく。詳細エディターでコードを記述書してもよい。
日付(date) / 日付時刻(datetime) を変換する関数 はたくさんあってほとんどのことができるようになっている。それぞれつかって列を必要な追加していく。
let
Source = 受注,
EarliestDate = List.Min(Source[受注日]),
StartOfYear = Date.StartOfYear(EarliestDate),
LatestDate = List.Max(Source[受注日]),
EndOfYear = Date.EndOfYear(LatestDate),
Dates = List.Dates(
StartOfYear
, Number.From(EndOfYear - StartOfYear) + 1
, #duration(1, 0, 0, 0)
),
ConvertedToTable = Table.FromList(
Dates
, Splitter.SplitByNothing()
, type table [日付 = date]
),
InsertedYear = Table.AddColumn(
ConvertedToTable
, "年", each Date.Year([日付])
, Int64.Type
),
InsertedMonth = Table.AddColumn(
InsertedYear
, "月", each Date.Month([日付])
, Int64.Type
),
InsertedDay = Table.AddColumn(
InsertedMonth
, "日", each Date.Day([日付])
, Int64.Type
),
InsertedYearMonth = Table.AddColumn(
InsertedDay
, "年月", each Date.ToText([日付], "yyyy年MM月")
, type text
),
InsertedFYYear = Table.AddColumn(
InsertedYearMonth
, "年度"
, each Date.ToText(Date.AddMonths([日付], -3), "yyyy年度")
, type text
)
in
InsertedFYYear
モデリング機能で用意する
Power BI Desktop の モデリング 機能で データモデルにテーブルを追加することができる。この機能を利用し 日付テーブル を追加する。
使用する DAX 関数
日付テーブルを用意するとき使われる関数2つ。いずれも、連続した日付列(列名 "Date") のみのテーブルを返す。
CALENDAR 関数
CALENDAR Function (DAX) | Date and Time Functions
CALENDAR(
<start_date>,
<end_date>
)
// 既定の列名は "Date"
ex.
カレンダー = CALENDAR( DATE( 2015, 1, 1 ), DATE( 2017, 12, 31 ) )
// 2015-01-01 から 2017-12-31 までの連続した日付列をもつテーブルを返す
カレンダー = CALENDAR( MINX( '受注', '受注'[受注日] ), MAXX( '受注', '受注'[請求日] ) )
// "受注"テーブル の "受注日"列でもっとも古い日付から
// "請求日"列の最も新しい日付までの連続した日付列をもつテーブルを返す
CALENDAR 関数 の開始日は終了日より後にすることはできまえんので、データモデルのロード時などで日付範囲を動的にするときは気を付けておくこと。
CALENDARAUTO 関数
日付の範囲は データモデル内のすべての日付列から算出され、すべての日付データが範囲内になる。
CALENDARAUTO Function (DAX) | Date and Time Functions
CALENDARAUTO(
[fiscal_year_end_month] // 1 ~ 12、(既定値：12)
)
ex.
CALENDARAUTO() // 1/1 ~ 12/31
ex.
CALENDARAUTO( 3 ) // 4/1 ~ 3/31
列を追加する
生成された日付列"Date"を持つテーブルに必要な集計範囲になる列を追加する。
YEAR Function (DAX) | Date and Time Functions
MONTH Function (DAX) | Date and Time Functions
や
FORMAT Function (DAX) | Text Functions
を利用することが多いでしょう。
Format 関数の 書式文字列について
- Pre-Defined Numeric Formats for the FORMAT Function
- Custom Numeric Formats for the FORMAT Function
- Pre-defined Date and Time formats for the FORMAT Function
- Custom Date and Time formats for the FORMAT Function
に網羅されていますので必要なものを使えばよいです。ここにない書式文字列には、"ooo" / "oooo"や "aaa" / "aaaa"がありますが、おススメすることはありません。
計算列で
[新しい列]で 日付テーブルに列を追加。必要な列をひとつづつ追加していけばよい。
既定の列名 "Date" は "日付"などに変更しても構わない。変更した場合、列名"Date"を使用している 新しい列の DAX式も一緒に更新される。
一気に ADDCOLUMNS 関数で
ADDCOLUMNS Function (DAX) | Statistical Functions
制限になりそうなこと
Power BI データモデルを構成するということだから、Direct Query や ライブ接続 だとテーブルの追加自体ができないということくらい。いずれ機能拡張などされて自由にできるようになるとよいですが。
情報
Query and Data Modeling Languages | MSDN
Date functions | Power Query M function reference
Date and Time Functions (DAX) | DAX Function Reference
Text Functions (DAX) | DAX Function Reference