日付テーブルを作成する

  • 9 分

Power BI でのレポート作成時、一般的なビジネス要件は、日付と時刻に基づいて計算を行うことです。 組織では、数か月、四半期、会計年度などの業績の把握が求められます。 そのため、このような時系列での値を適切な形式で設定することが非常に重要です。 Power BI は日付列とテーブルを自動検出しますが、組織で必要とされる形式で日付を取得するために追加の手順を実行する必要がある状況が発生することがあります。

たとえば、あなたは組織の営業チームに関するレポートを作成しているとします。 データベースには、売上、注文、製品などのテーブルが含まれています。 Sales や Orders などのこれらのテーブルの多くには、Sales テーブルと Orders テーブルの ShipDate 列と OrderDate 列が示すように、独自の日付列が含まれていることに気付くでしょう。 あなたは、年別および月別の売上と注文の合計のテーブルを作成するタスクに取り組むことになりました。 それぞれが独自の日付列を参照する複数のテーブルが含まれるビジュアルをビルドするには、どうすればよいでしょうか?

Sales.ShipDate と Order.OrderDate が強調表示されたセマンティック モデルの抜粋のスクリーンショット。

この問題を解決するには、複数のテーブルで使用できる共通の日付テーブルを作成します。 次のセクションでは、Power BI でこのタスクを実行する方法について説明します。

共通の日付テーブルを作成する

共通の日付テーブルを作成するには、次の方法があります。

  • ソース データ

  • DAX

  • Power Query

ソース データ

場合によっては、ソース データベースとデータ ウェアハウスに独自の日付テーブルが既に含まれています。 データベースをデザインした管理者が綿密な仕事を行っていた場合は、これらのテーブルを使用して次のタスクを実行できます。

  • 会社の休日の識別

  • カレンダーと会計年度の区別

  • 週末と平日との識別

ソース データ テーブルは完成度が高く、すぐに使用できる状態になっています。 そのようなテーブルがある場合、それをセマンティック モデルに取り込みます。このセクションで説明されている他の方法は使用しないでください。 Power BI に加えて使用している可能性のある他のツールと共有されている可能性が高いため、ソース日付テーブルを使用することをお勧めします。

ソース データ テーブルがない場合は、他の方法を使用して共通の日付テーブルを作成することができます。

DAX

Data Analysis Expression (DAX) 関数の CALENDARAUTO() または CALENDAR() を使用して、共通の日付テーブルを作成できます。 CALENDAR() 関数は、この関数の引数として入力される開始日と終了日に基づいて、連続した日付の範囲を返します。 あるいは、CALENDARAUTO() 関数は、セマンティック モデルから自動的に決定される、連続した完全な日付範囲を返します。 開始日は、セマンティック モデル内に存在する最も早い日付として選択されます。終了日は、セマンティック モデル内に存在する最後の日付に、CALENDARAUTO() 関数の引数として含めることができる会計月に入力されたデータを加えたものです。 この例では、2011 年 5 月 31 日 (Sales でこのデータの追跡を開始した最初の日) 以降 10 年間のデータのみを表示するので、CALENDAR() 関数を使用します。

Power BI Desktop で、新しいテーブル を選択した後、次の DAX 式を入力します。

Dates  = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))

Power BI における CALENDAR 式のスクリーンショット。

これで、使用できる日付の列が作成されました。 ただし、この列は少々まばらです。 また、年、月の番号、年間の週、曜日の列も表示する必要があります。 このタスクを実行するには、リボンで 新しい列 を選択して、次の DAX 式を入力します。これにより、Date テーブルから年が取得できます。

Year = YEAR(Dates[Date])

DAX 式を使用して列を追加しているスクリーンショット。

同じプロセスを実行して、月の番号、週の番号、曜日を取得できます。

MonthNum = MONTH(Dates[Date])

WeekNum = WEEKNUM(Dates[Date])

DayoftheWeek = FORMAT(Dates[Date], "DDDD")

完了すると、テーブルには、次の図に示す列が含まれます。

DAX テーブルの最後の列のスクリーンショット。

これで、DAX を使用して、共通の日付テーブルが作成されました。 このプロセスでは、新しいテーブルがセマンティック モデルに追加されるだけです。日付テーブルと Sales テーブルおよび Order テーブル間の関係を確立し、テーブルをセマンティック モデルの公式日付テーブルとしてマークする必要があります。 ただし、これらのタスクを完了する前に、Power Query を使って共通の日付テーブルを構築する別の方法を検討してください。

Power Query

Power Query でクエリを構築するために使用される開発言語である M 言語を使って、共通の日付テーブルを定義できます。

Power BI Desktop で データの変換 を選択します。自動的に Power Query に移動します。 左側の クエリ ペインの空白部分で右クリックして、次のドロップダウン メニューを開きます。このドロップダウンメニューでは、新しいクエリ > 空のクエリ の順に選択します。

Power BI での新しいクエリの構築のスクリーンショット。

生成された 新しいクエリ ビューで、次の M 式を入力して、カレンダー テーブルを作成します。

= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))

M 式を使用したカレンダー テーブル作成のスクリーンショット。

売上データの場合、開始日には、データ内の最も早い日付である 2011 年 5 月 31 日を反映させる必要があります。 さらに、今後 10 年間の日付 (将来の日付を含む) を表示する必要があります。 この方法では、新しい売上データが含まれている場合に、このテーブルを再作成する必要が確実になくなります。 また、期間を変更することもできます。 この場合、データ ポイントが毎日必要になりますが、時間、分、秒で増分することもできます。 次の図に結果を示します。

一覧としての販売カレンダーのスクリーンショット。

プロセスが成功したことを確認したら、日付のテーブルではなく、日付のリストがあることに気付くでしょう。 このエラーを修正するには、リボンの 変換 タブに移動して、変換 > テーブルへの変換 の順に選択します。 名前が示すように、この機能では、リストがテーブルに変換されます。 列の名前を DateCol に変更することもできます。

Power Query エディターでリストをテーブルに変換するスクリーンショット。

次に、新しいテーブルに列を追加して、年、月、週、日で日付を表示します。これにより、ビジュアルで階層をビルドできます。 最初のタスクでは、列の名前の横にあるアイコンを選択して列の型を変更し、生成されたドロップダウン メニューで Date 型を選択します。

型を date に変更したスクリーンショット。

Date 型の選択が完了したら、年、月、週、日の列を追加できます。 次の図に示すように、列の追加 に移動し、Date の下にあるドロップダウン メニューを選択して、Year を選択します。

Power Query を通じて列を追加しているスクリーンショット。

Power BI によって、DateCol から取得されたすべての年の列が追加されたことに注意してください。

Power Query を使用してテーブルに列を追加しているスクリーンショット。

月、週、日について、同じプロセスを完了します。 このプロセスを完了すると、テーブルには、次の図に示す列が含まれます。

DateCol、Year、Month、Week of Year、Day Name の各列のスクリーンショット。

これで、Power Query を使用して共通の日付テーブルを構築することができました。

前の手順では、テーブルをセマンティック モデルに取り込む方法を示しました。 次に、テーブルを公式の日付テーブルとしてマークして、Power BI が将来のすべての値を認識し、書式設定が正しいことを確認可能にする必要があります。

正式な日付テーブルとしてマークする

テーブルを正式な日付テーブルとしてマークする場合の最初のタスクは、フィールド ペインで新しいテーブルを見つけることです。 次の図に示すように、テーブルの名前を右クリックして、日付テーブルとしてマーク を選択します。

[日付テーブルとしてマーク] オプションのスクリーンショット。

テーブルを日付テーブルとしてマークすると、Power BI により検証が実行され、データに null 値が含まれていないこと、データが一意であること、一定期間にわたって連続した日付値が含まれていることが確認されます。 テーブル内の特定の列を日付としてマークすることもできます。これは、テーブル内に多くの列がある場合に便利です。 テーブルを右クリックし、日付テーブルとしてマーク を選択してから、日付テーブル設定 を選択します。次のウィンドウが表示され、Date としてマークする列を選択することができます。

[日付テーブルとしてマーク] ダイアログのスクリーンショット。

日付テーブルとしてマーク を選択すると、日付テーブルとしてマークしたテーブルの Date フィールドから自動生成された階層が削除されます。 その他の Date フィールドについては、そのフィールドと日付テーブルの間にリレーションシップを確立するまで、または 自動の日付/時刻 機能をオフにするまで、自動階層が引き続き存在します。 フィールド ペインで Year、Month、Week、または Day の列を右クリックし、新しい階層 を選択することにより、共通の日付テーブルに階層を手動で追加できます。このプロセスについては、このモジュールの後半で詳しく説明します。

ビジュアルをビルドする

Sales テーブルと Orders テーブルの間でビジュアルをビルドするには、この新しい共通の日付テーブルと、Sales テーブルや Orders テーブルの間にリレーションシップを確立する必要があります。 その結果、新しい日付テーブルを使用して、ビジュアルをビルドできるようになります。 このタスクを完了するには、モデル タブ >リレーションシップの管理 の順に移動します。ここでは、OrderDate 列を使用して、共通の日付テーブルと、Orders テーブルや Sales テーブルの間にリレーションシップを作成できます。 次のスクリーンショットでは、そのようなリレーションシップの例を示します。

[リレーションシップの作成] ダイアログのスクリーンショット。

リレーションシップを構築したら、DAX または Power Query メソッドを使って開発した共通の日付テーブルを使用して、売上の合計と注文数量 (時間別) ビジュアルを構築できます。

売上の合計を決定するには、Sales テーブルの Amount 列が売上収入の合計ではなく、各売上の収入のみを表示するため、すべての売上を追加する必要があります。 このタスクを完了するには、次のメジャー計算を使用します。これについては、後で説明します。 このメジャーをビルドする場合に使用する計算は、次のとおりです。

#Total Sales = SUM(Sales[‘Amount’])

完了したら、視覚化 タブに戻り、テーブル を選択して、テーブルを作成できます。 注文と売上の合計を年別および月別に表示するので、日付テーブルの Year 列と Month 列、OrderQty 列、#TotalSales メジャーのみを含めます。 階層について学習する場合には、年から月にドリルダウンできる階層をビルドすることもできます。 この例では、それらを横に並べて表示できます。 これで、共通の日付テーブルを使用して、ビジュアルを作成することができました。

DAX を使用した共通の Date 列のスクリーンショット。