[Power BI] DAX入門(2) カレンダーテーブルの作成

  • Article

Microsoft Japan Data Platform Tech Sales Team

土井

DAX 入門 第二回目では、カレンダーテーブルを生成するための日付と時刻の関数と、それに付随して文字列関数と論理関数について具体的に触れていきます。 (本記事で作成するサンプルファイルは こちら からダウンロードいただけます。)

■なぜカレンダーテーブルが必要か

カレンダーテーブルを作成することで、年ごと、四半期ごと、月ごと、週ごと、といった形で任意の時系列単位でデータを集計することが可能になります。同様の意味で、 "タイム ディメンション" と言ったり、"時間階層" と言ったり "日付マスタ" と言ったりしますが、ここでは "カレンダーテーブル" として統一します。

Power BI Desktop や PowerPivot も含め、多くの BI ツールでは 分析に使用するデータの中に date 型の列があれば、カレンダーテーブルを自動的に (暗黙的に) 作成する機能を持っていますが、意図した仕様とは異なったものになっており、結局イチからカレンダーテーブルを作成しないといけない、といったことも多々あります。たとえば一週間の曜日始まりを月曜として週単位で集計したいのに、日曜始まりとなってしまっていたり、曜日表記を英語ではなく日本語で表示したかったり、前年同日ではなく前年同曜日を算出したいといった場合等が挙げられます。

 

■CALENDAR 関数 CALENDAR 関数 は SQL Server 2016 Analysis Services (SSAS), Power Pivot in Excel 2016, そして Power BI Desktop で提供されている新しい DAX 関数で、開始日と終了日を指定して、date 型の列を生成することができます。以降、本記事では Power BI Desktop を例に進めますが、SSAS や PowerPivot でも同様のことができるようになっています。

Power BI Desktop を立ち上げて、”モデリング”タブから”新しいテーブル”をクリックします。

image

数式ペインに以下の式を入力します。

 カレンダー = CALENDAR("2015/1/1", TODAY())

image TODAY 関数 は現在の日付を返します。時刻は 0:00:00 となります。

類似の関数として NOW 関数 があります。こちらは時刻もその時点の時刻として反映されます。Power BI Service でスケジュール更新されるレポートに更新日時を表示する際等に最適です。

 

新しいテーブルが作成され、2015/1/1～本日までの date 型の列を持つテーブルが作成されました。

image

 

■日付と時刻の関数

さきほど作成した Date 列を基に、集計したい単位の列を DAX 関数を使って生成します。日付と時刻の関数 (DAX) の中から以下の関数を使用します。

 

YEAR 関数 ・・・ 日付の年を 4 桁の整数として返します。範囲は 1900 ～ 9999 です。

 年 = YEAR([Date])

 

MONTH 関数 ・・・ 1 (1 月) から 12 (12 月) までの数値として月を返します。

 月番号 = MONTH([Date])

 

WEEKDAY 関数 ・・・ 日付に対応する曜日を return_type 値に従って返します。  既定では、曜日の範囲は 1 (日曜日) ～ 7 (土曜日) です。

 曜日番号 = WEEKDAY([Date],  <return_type> )
return_type 週の始まり
1 (既定) 週の始まりを日曜日 (1)、終わりを土曜日 (7) とします。
2 週の始まりを月曜日 (1)、終わりを日曜日 (7) とします。
3 週の始まりを月曜日 (0)、終わりを日曜日 (6) とします。

 

WEEKNUM 関数 ・・・  特定の日付が第何週目に当たるかを返します。 たとえば、1 月 1 日を含む週がその年の最初の週で、第 1 週になります。

この関数で使用されるシステムには、次の 2 種類があります。

  • システム 1    1 月 1 日を含む週がその年の最初の週であり、第 1 週です。
  • システム 2    その年の最初の木曜日を含む週がその年の最初の週であり、第 1 週です。 このシステムは、ヨーロッパ式週番号システムと呼ばれる、ISO 8601 に規定されている方式です。
 週番号 = WEEKNUM([Date],  <return_type> )
return_type 週の始まり システム
1 or 17 (既定) 日曜日 1
2 or 11 月曜日 1
12 火曜日 1
13 水曜日 1
14 木曜日 1
15 金曜日 1
16 土曜日 1
21 月曜日 2 (ISO week date)

※WEEKNUM 関数については Excel 関数におけるガイド も合わせて参照ください。

 

上記 4 つの DAX 関数を使って、Power BI Desktop で新しい列 を作成すると、以下のようになります。

(ここでは WEEKDAY 関数の return_type は 2、WEEKNUM 関数の return_type は 21 としています)

image

 

■文字列関数と論理関数 文字列関数 や 論理関数 を使って日本式なカレンダーテーブルを作成していきます。まず文字列関数の FORMAT 関数 を使って、

 月 = FORMAT([Date],"M月")

 年月 = FORMAT([Date],"YYYY年MM月")

を作成します。FORMAT 関数のカスタム日付/時刻書式 も合わせて参照ください。

以下のように列が追加されます。このとき、”月” 列を選択した状態で、”列で並び替え”をクリックし、”月番号”を選択します。

image

これにより、”月” 列のソート順は “月番号” 列によってコントロールされることになります。たとえば、商品名の列を商品 ID 順にソートしたい、といった場合でも同様に設定が可能です。

 

次に、論理関数の Switch 関数 と IF 関数 を使って日本式の “曜日”、”会計年度”、”四半期” 列を作成します。(ここでは年度始まりを 4 月としています)

 曜日 = SWITCH([曜日番号], 1, "月", 2, "火", 3, "水", 4, "木", 5, "金", 6, "土", 7, "日","")

 会計年度 = IF([月番号]<4,[年]-1,[年])

 四半期 = IF([月番号]<4,"Q4",IF([月番号]<7,"Q1",IF([月番号]<10,"Q2","Q3")))

image

さきほどと同様に、”曜日” 列を選択して ”曜日番号” で並べ替えされるように設定しておきます。

 

最後に、冒頭で自動で生成された “Date” 列の名前を ”日付” に変更しておきます。

image

列の名前が変わっても、これまで作成してきた “Date” 列を参照した式がすべて自動で “日付”に変わるので、エラーは起きません。

image

 

■まとめ

本記事では、よく使われる DAX 関数を使ってカレンダーテーブルを作成する方法についてご紹介いたしました。論理関数や文字列関数等はカレンダーテーブル以外にも応用可能なので、是非お手元のデータで試してみてください。

なお、今回作成したサンプルファイルは こちら からダウンロードいただけます。