LoginSignup
22
17

More than 5 years have passed since last update.

Microsoft Power BIAdvent Calendar 2017Day 7
@PowerBIxyz(Takeshi Kagata)

日付テーブル はどうやって作るか、それが問題だ

Last updated at Posted at 2017-12-06

タイトルには勢いがあってもよいと思うんです。
さて、日付テーブルを用意する方法について。手段はふたつある。

  • 日付テーブル を クエリ エディター で準備し、データモデル にロードする
  • 作成された データモデル に 日付テーブル を追加する

いずれであっても、年間、月間など期間での集計やそれらの比較など本来の目的は達成できる。準備に用いる言語が 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

0

列を追加する

カスタム列の追加 もしくは、例からの列の追加 でテーブルに列を追加していく。詳細エディターでコードを記述書してもよい。

1

2

日付(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

3

モデリング機能で用意する

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 関数の 書式文字列について

に網羅されていますので必要なものを使えばよいです。ここにない書式文字列には、"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

その他

22
17
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
22
17