Help us understand the problem. What is going on with this article?

Power BI 勉強帳:日付(カレンダー)テーブル

More than 1 year has passed since last update.

Microsoft Power BI Advent Calendar 2018
12/4の記事です。

Power BIでレポートを作るようになったとき、最初に戸惑いを覚え、そしてコツをつかむと「わー便利」と感じることの1つが、「時系列の扱い」なんじゃないかと思います。

日付テーブルでできるようになること

  • 四半期>月>週など任意の階層で表示する
  • 相対的に直近期間を常に表示する
  • スライダーで表示期間を可変にする
  • 「2018年12月4日(火)」など任意の表示形式を使う

さらにタイムインテリジェンス関数でできること

  • 相対的にずらした期間の値(昨年値、昨月値など)を出す
  • 期間ごとの差分や比率(昨年対比、昨月比など)を出す
  • 期間ごとの累計値(月別累計、日別累計など)を出す
  • 移動平均を出す

などなど…。

この記事では、時系列取り扱いの基本となる日付(カレンダー)テーブルについて、私なりに使ってみて感じたことを紹介していきたいと思います。

日付テーブルとは?

ごく簡単に表現すると、
- 「連続した日付」列を持ち、「年」「月」などの時系列の階層をもつテーブル
- 日付をキーにデータを紐付けることで、時系列の様々な集計やレポート表現を実現することができる

日付テーブルをつくる(DAX版)

とりあえずさくっと使いたいよ、という方のために、一発コピペできる列多めのバージョンをつくってみました。(後に書いてありますが、コピペ後、データ型の定義をしてください)

Power BI 王子こと清水優吾さんのスライドの39ページを参考に、自分が便利だと思った列を追加しています。

2018/12/5追記:「年月_数値」が最初から数値型になるようVALUEでラップしました。
2019/1/23追記:「相対年度」の式に誤りがあったので修正しました。済みません。。。

日付テーブル (DAX版).
日付テーブル = 
VAR StartDate="2016/04/01"
VAR EndDate="2020/3/31"
VAR Today=UTCTODAY()+9/24
VAR ThisFY=if(MONTH(Today)>3,YEAR(Today),YEAR(Today)-1)
VAR BaseCalendar =CALENDAR (StartDate,EndDate)
RETURN

ADDCOLUMNS(
    BaseCalendar,
    "年_数値", YEAR ( [Date] ),
    "年_yyyy年",FORMAT([Date],"yyyy年"),
    "月_数値", MONTH ([Date]),
    "月_MM月", FORMAT ([Date], "MM月" ),
    "日_数値", DAY ([Date]),
    "日_DD日", FORMAT([Date],"DD日"),
    "年月_数値", VALUE(FORMAT([Date], "yyyyMM" )),
    "年月_yy年MM月", FORMAT ( [Date], "yy年MM月" ),
    "年度_数値",if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),
    "年度_0年度",FORMAT(if(MONTH([Date])>3,YEAR([Date]),YEAR([Date])-1),"0年度"),
    "年度四半期_数値", if(MONTH([Date])<4,4,if(MONTH([Date])<7,1,if(MONTH([Date])<10,2,3))),
    "年度四半期_0Q", if(MONTH([Date])<4,"4Q",if(MONTH([Date])<7,"1Q",if(MONTH([Date])<10,"2Q","3Q"))),
    "週_数値", WEEKNUM ( [Date], 1 ),
    "週_第00週", FORMAT ( WEEKNUM ( [Date], 1 ), "第00週" ),
    "曜日番号月曜から_数値", WEEKDAY ( [Date], 2 ),
    "曜日_aaa", FORMAT([Date],"aaa"),
    "日と曜日", FORMAT ( [Date], "DDaaa" ),
    "相対日付",DATEDIFF(Today,[Date],DAY),
    "相対月",DATEDIFF(Today,[Date],MONTH),
    "相対年度",if(MONTH([Date])>3,YEAR([Date])-ThisFY,YEAR([Date])-1-ThisFY)
    )

コピペする場所

モデリングタブ>新しいテーブル」の数式バーです。
image.png

コピペした後必ずすること

各列のデータ型の定義を確認し、必要に応じて変更します。
image.png

なお、キーとなる日付列([Date])は「日付型」にしておく必要があります。
テキスト型にしてはいけません。

期間の調整

変数の「StartDate」「EndDate」の日付を変えます。
他のテーブルの値から、動的に指定することもできます。

最終日を動的に指定する.
//売上テーブルの最後の売上日を指定
VAR EndDate=MAX('売上'[売上日])

//売上テーブルの最後の売上日の次の年の3月31日までを指定
VAR EndDate=Date(YEAR(MAX('売上'[売上日]) )+1,3,31)

列の調整

いらない列を削除し、足りない列を追加します。

だいたいぱっと見でアレンジできると思いますが、下記の記事で、関数含めカレンダーテーブル全般について解説されていて参考になります。
[Power BI] DAX入門(2) カレンダーテーブルの作成

なお、日本語の曜日名は、、リファレンスには載ってないですが、FORMAT関数の「aaa」で表せました。

並び順を整える(曜日名)

曜日名は、そのままでは月、火、水…という順には並んでくれません。
曜日名列の並び順を、曜日番号を使って行うように設定する必要があります。
image.png

これにより、ビジュアルで曜日名を使ったときに、曜日番号順に並ぶようになります。

image.png

日付テーブルを作ったら…

データテーブルと紐づける

日付テーブルを作ったら、紐づけたいデータテーブルの日付列とリレーションをかけます。

「月別」などメッシュが毎日ではないデータでも、日付テーブルと紐づけるための日付列を用意しておきます。たとえば月別の場合、月初日月末日などを用意します。
(「年月」「年」などで紐づけようとすると「多対多」の関係になってしまい紐づきません)

image.png

これにより、データテーブルのレポートに日付テーブルを使うことができるようになります。

image.png

使ってみて便利だと思った列

相対年、相対月、相対日付

今日、今月、今年を0として、後の場合+、前の場合-の数値で現した列です。

表示したい期間を、ページレベルフィルター・レポートレベルフィルター・ビジュアルレベルフィルターで「直近●ヶ月」「当月の1日から昨日まで」など設定することができて便利です。

また、相対的な時間を参照するメジャーを作る際にも、利用することがあります。
image.png

コンパクトな文字形式の列

好みもあると思うのですが、日付関係はグラフの軸で使うことが多いので、ぎゅっとコンパクトな表現(「01水」「18年05月」など)が目にやさしいなと思います。

注意点としては、文字形式の昇順で時間順通りに並ぶようにする必要があります。(「1月」でなく「01月」とするなど)

image.png

数値形式の列

数値形式の列は、スライサーをスライダー形式にすることができます。
一覧だと選択肢が多すぎる場合に便利かなと思います。(ただしスライダーでは連続した数値を指定できるので、年月をスライダーにすると「201840」のような値が選択できてしまいます)

image.png

補足:列名は、ビジュアル側で修正できます

列名はビジュアル側で好きなように変更できます。
image.png
ですので、私は、元の列名はできるだけ説明的につけるようにして、ビジュアル側でコンパクトな名前に変更するようにしています。

「日付テーブルをマークする」について

作った日付テーブルを右クリックすると、「日付テーブルをマークする」という選択肢が出てきます。
image.png

先人の方々の記事を読むと、

  • 自分で日付テーブルをつくらなくても、日付列のあるデータテーブルを作成すると、Power BIによって自動的に裏側に「日付テーブル」が作成される
  • 自動的につくられた「日付テーブル」は、日付階層やクイックメジャーなどで、良きように使われる
  • とはいえ、自動で作られた「日付テーブル」だと、意図する結果が得られないことがままある
  • そこで、明示的に自分で「日付テーブル」を作成し、置き換えることで、意図する結果が得られる

ということのようです。詳しい説明は下の参考記事の中にあります。

先人の記事

今回のぬるい説明を読んでから、さらにリンク先の詳しい説明を読むと、より理解が深まるのではないかなあと思います。

最後まで読んでくださってありがとうございました。
初めて書いた記事なので、何かおかしいところがありましたら、やさしく教えていただけましたら嬉しいです。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした