PowerPivot の日付テーブルは、時系列でデータを参照して計算する場合に欠かせません。 この記事では、PowerPivot の日付テーブルとその作成方法について詳しく説明します。 具体的には、次の項目について説明します。

  • 日付や時系列でデータを参照して計算する場合に、日付テーブルが重要である理由。

  • PowerPivot を使って、日付テーブルをデータ モデルに追加する方法。

  • 年、月、期間など、新しい日付列を日付テーブルに作成する方法。

  • 日付テーブルとファクト テーブルの間にリレーションシップを作成する方法。

  • 時刻を操作する方法。

この記事は、Power Pivot を初めて使う方を対象としています。 ただし、データのインポート、リレーションシップの作成、計算列やメジャーの作成などについて、あらかじめよく理解しておくことが重要です。

この記事では、メジャー式での DAX タイム インテリジェンス関数の使い方については、説明しません。 DAX タイム インテリジェンス関数を使ってメジャーを作成する方法については、「PowerPivot in Excel のタイム インテリジェンス」を参照してください。

注: Power Pivot では、"メジャー" と "計算フィールド" は同じものです。 この記事では、"メジャー" を使います。 詳細については、「PowerPivot のメジャー」を参照してください。

目次

日付テーブルについて

データ分析には、必ずと言ってよいほど、日付と時系列でデータを参照して比較する処理が含まれています。 たとえば、前会計四半期の売上高を集計して、その合計を他の四半期と比較したり、勘定処理で月末残高を計算したりするような場合です。 このような場合には、特定の期間の売上取引や残高をグループ化して集計する際の基準として日付を使います。

Power View レポート

会計四半期ごとの売上総額ピボットテーブル

日付テーブルには、日付や時刻のさまざまな表現を含めることができます。 たとえば、ピボットテーブルや Power View レポートでデータのスライスやフィルターといった処理を行うときに、フィールド リストから選べるフィールドとして、日付テーブルには多くの場合、会計年度、会計月、会計四半期、会計期間などの列が含まれています。

Power View フィールド リスト

Power View フィールド リスト

それぞれの範囲内の日付をすべて含む、年、月、四半期などの日付列の場合は、連続する日付を含む列を少なくとも 1 つ、日付テーブルに設定することが必須です。 つまり、この列は、日付テーブルに含まれている各年のすべての日を 1 日 1 行の形式で記載した行を含んでいる必要があります。

たとえば、参照するデータに 2010 年 2 月 1 日から 2012 年 11 月 30 日までの日付が含まれていて、カレンダー年度に基づいてレポートを作成する場合、日付テーブルには、少なくとも 2010 年 1 月 1 日から 2012 年 12 月 31 日までの範囲の日付が必要です。 日付テーブルの各年は、その年のすべての日を含んでいる必要があります。 データをさらに新しいデータで定期的に更新する場合、終了日を 1 年か 2 年、後にずらせば、時間の経過に合わせて日付テーブルを更新する必要はなくなります。

連続した日付を含む日付テーブル

連続する日付を含む日付テーブル

会計年度に基づいてレポートを作成する場合、各会計年度の連続した日付を含む日付テーブルを作成します。 たとえば、会計年度が 3 月 1 日から始まり、2010 年会計年度から現在の日付 (たとえば、2013 年会計年度) までのデータがある場合、2009 年 3 月 1 日から始まり、少なくとも各会計年度のすべての日と 2013 年会計年度の最後の日付までを含む日付テーブルを作成できます。

カレンダー年度と会計年度の両方に基づいてレポートを作成する場合、個別の日付テーブルを作成する必要はありません。 単一の日付テーブルに、カレンダー年度、会計年度、さらには 13 × 4 週間形式のようなカレンダー用の列を含めることができます。 重要なポイントは、範囲に含めるすべての年の連続した日付が日付テーブルに含まれているということです。

日付テーブルをデータ モデルに追加する

日付テーブルをデータ モデルに追加するには、いくつかの方法があります。

  • リレーショナル データベース、またはその他のデータ ソースからインポートする。

  • Excel で日付テーブルを作成して、PowerPivot に新しいテーブルをコピーするか、そのリンクを張る。

  • Microsoft Azure Marketplace からインポートする。

それぞれの方法について詳しく見てみましょう。

リレーショナル データベースからインポートする

データ ウェアハウスまたは他の種類のリレーショナル データベースからデータの一部またはすべてのデータをインポートする場合は、日付テーブルと、データとインポートする残りのデータの間にリレーションシップが既にある可能性があります。 日付と形式はファクト データの日付と一致する可能性が高く、日付はおそらく過去に始まり、将来に大きな違いがあります。 インポートする日付テーブルが非常に大きく、データ モデルに含める必要がある範囲を超える日付が含まれている場合があります。 Power Pivot のテーブル インポート ウィザードの高度なフィルター機能を使用して、本当に必要な日付と特定の列のみを選択できます。 これにより、ブックのサイズが大幅に小さめ、パフォーマンスが向上します。

テーブルのインポート ウィザード

[テーブルのインポート ウィザード] ダイアログ ボックス

ほとんどの場合、インポートされたテーブルに列が既に存在するために、会計年度、週、月名などの追加の列を作成する必要はありません。 ただし、場合によっては、データ モデルに日付テーブルをインポートした後、特定のレポートの必要性に応じて、追加の日付列を作成する必要がある場合があります。 幸いなことに、これは DAX を使用して簡単に実行できます。 日付テーブル フィールドの作成の詳細については、後で説明します。 環境は異なります。 データ ソースに関連する日付テーブルまたは予定表テーブルが含まれており、不明な場合は、データベース管理者にお問い合わせください。

Excel で日付テーブルを作成する

Excel で日付テーブルを作成して、データ モデルの新しいテーブルにコピーできます。 この作業はとても簡単であり、さまざまな事例に柔軟に対応できます。

Excel でデータ テーブルを作成するには、連続した日付の範囲を含む単一の列から始めます。 次に、Excel の数式を使って、年、四半期、月、会計年度、期間といった追加の列を Excel ワークシートに作成するか、テーブルをデータ モデルにコピーしてから、追加の列を計算列として作成します。 PowerPivot で追加の日付列を作成する方法については、この記事で後述の「新しい日付列を日付テーブルに追加する」セクションをご覧ください。

Excel で日付テーブルを作成してデータ モデルにコピーする方法

  1. Excel で、空のワークシートのセル A1 に、日付の範囲を特定するための列ヘッダー名を入力します。 一般的な名前は、Date、DateTime、DateKey などです。

  2. セル A2 に、開始日を入力します。 たとえば、2010/1/1 です。

  3. フィル ハンドルをクリックし、終了日を含む行番号までドラッグします。 たとえば、12/31/2016 です。

    Excel の [日付] 列

  4. Date 列 (セル A1 のヘッダー名を含む) のすべての行を選びます。

  5. [スタイル] グループで [テーブルとして書式設定] をクリックし、スタイルを選びます。

  6. [テーブルとして書式設定] ダイアログ ボックスの [OK] をクリックします。

    PowerPivot の Date 列

  7. ヘッダーを含む、すべての行をコピーします。

  8. PowerPivot の [ホーム] タブで、[貼り付け] をクリックします。

  9. [貼り付けプレビュー] の [テーブル名] に、DateCalendar などの名前を入力します。 [先頭の行を列見出しとして使用する] をオンにした状態のまま、[OK] をクリックします。

    貼り付けプレビュー

    PowerPivot の新しい日付テーブル (この例では、Calendar という名前) は、次のように表示されます。

    Power Pivot の日付テーブル

    注: [データ モデルへの追加] を使って、リンク テーブルを作成する方法もあります。 ただし、この方法では、Excel 形式と PowerPivot 形式という 2 つのバージョンの日付テーブルがブック内に作成されるので、ブックのサイズが必要以上に大きくなります。

注:  date という名前は、PowerPivot のキーワードです。 PowerPivot で作成したテーブルに Date という名前を付けた場合、DAX の数式で引数として参照するには、このテーブルの名前を単一引用符で囲む必要があります。 この記事の画像や数式の例はすべて、PowerPivot で作成した Calendar という名前の日付テーブルを参照しています。

これで、日付テーブルがデータ モデルに追加されました。 DAX を使って、Year や Month など、新しい日付列を追加できます。

新しい日付列を日付テーブルに追加する

各年の日を 1 日 1 行の形式で網羅した行を含む、単一の日付列を含む日付テーブルは、日付範囲のすべての日を定義するうえで重要です。 同時に、ファクト テーブルと日付テーブルの間にリレーションシップを作成する必要もあります。 ただし、各年の日を 1 日 1 行の形式で網羅した行を含む、単一の日付列は、ピボットテーブルや Power View レポートで日付に基づいて分析する場合には使いやすくありません。 日付テーブルには、日付の範囲やグループでデータを集計するときに役に立つ列を含める必要があります。 たとえば、月または四半期別の売上高を集計する場合や、売上高の前年比伸び率を求めるメジャーを作成する場合などが考えられます。 このような場合には、対象となる期間のデータを集計できるように、年、月、四半期などの列が日付テーブルに必要です。

リレーショナル データ ソースから日付テーブルをインポートした場合、必要な日付列の種類が既に含まれる場合があります。 場合によっては、このような列のいくつかを変更したり、追加の日付列を作成したりする必要があります。 これに該当するのは、Excel で独自の日付テーブルを作成して、データ モデルにコピーするような場合です。 幸いなことに、DAX の日付/時刻関数を使用すると、Power Pivot で新しい日付列 を簡単に 作成できます。

ヒント: DAX をまだ使用していない場合は、「クイック スタート: Office.com で DAX の基本を30分で学習する」を参照してください

日付と時刻の関数 (DAX)

Excel 数式で日付と時刻の関数を操作した場合は、日付と時刻の関数に慣れ親しんだと思います。 これらの関数は Excel の対応する関数とよく似ていますが、次のようにいくつかの重要な違いがあります。

  • DAX の日付と時刻の関数は、datetime データ型を返します。

  • 値を列から引数として取得できます。

  • 日付値を返したり、操作したりする目的で使用できます。

これらの関数は多くの場合、日付テーブルにカスタム日付列を作成するときに使うので、理解しておくことが重要です。 ここでは、これらの関数を使って、Year、Quarter、FiscalMonth といった列を作成します。

注: DAX の日付と時刻の関数は、タイム インテリジェンス関数とまったく同じではありません。 Power Pivot のタイム インテリジェンスの詳細については、Excel 2013 を参照してください

DAX には、次のような日付と時刻の関数があります。

数式には他にも多くの DAX 関数を使用できます。 たとえば、ここで説明する数式の多くは、MODTRUNCなどの数学/ 角関数 、IFなどの論理関数 、FORMATなどのテキスト関数を使用します。その他の DAX 関数の詳細については、この記事の後半の「その他のリソース」セクションを参照してください。

数式の例 (カレンダー年度)

次の例では、Calendar という名前の日付テーブルに追加の列を作成する場合に使う数式について説明します。 2010/1/1 から 2016/12/31 までの連続した範囲の日付を含む、Date という名前の列が既に存在しています。

=YEAR([date])

この数式では 、YEAR 関数 は Date 列の値から年を返します。 Date 列の値は datetime データ型であるので、YEAR 関数は既定の方法に従って年を返します。

Year 列

=MONTH([date])

この数式では、YEAR 関数と同様に 、MONTH 関数を使用して Date 列から月の値を返します。

Month 列

四半期

=INT(([Month]+2)/3)

この数式では 、INT 関数を使用して 日付値を整数として返します。 INT 関数に指定する引数は Month 列の値で、2 を追加し、3 で除算して四半期を 1 から 4 に分割します。

[四半期] 列

MonthName

=FORMAT([date],"mmmm")

この数式では、月の名前を取得するために 、FORMAT 関数を使用して数値を Date 列からテキストに変換します。 引数として Date 列を最初に指定し、さらに書式の引数として、月の名前を省略せずに表示するため、"mmmm" を使っています。 結果は次のように表示されます。

MonthName 列

月の名前を 3 文字の省略形で返すには、書式の引数に "mmm" を使います。

DayofWeek

=FORMAT([date],"ddd")

この数式では、FORMAT 関数を使って、曜日を取得しています。 曜日の省略形が必要なので、書式の引数に "ddd" を指定しています。

DayofWeek 列
ピボットテーブルのサンプル

年、四半期、月といった日付のフィールドを用意したら、ピボットテーブルやレポートで使うことができます。 たとえば、次の画像は、Sales ファクト テーブルの SalesAmount フィールドを VALUES に、Calendar ディメンション テーブルの Year と Quarter を ROWS にそれぞれ表示しています。 SalesAmount は年と四半期のコンテキストで集計されています。

ピボットテーブル例

数式の例 (会計年度)

FiscalYear

=IF([Month]<= 6,[Year],[Year]+1)

この例では、会計年度は 7 月 1 日から始まります。

会計年度の開始日と終了日はカレンダー年の開始日と終了日と異なる場合が多いので、日付の値から会計年度を抽出できる関数はありません。 会計年度を取得するには、まず IF 関数を使用して、Month の値が 6 以下の値をテストします。 2 番目の引数で、Month の値が 6 以下の場合は、Year 列から値を返します。 指定しない場合は、Year から値を返し、1 を追加します。

[会計年度] 列

これ以外に、会計年度の終了月の値を指定して、そのまま月を指定するメジャーを作成する方法もあります。 たとえば、FYE:=6 のように指定します。 次に、月の値の代わりに、メジャーの名前を参照します。 たとえば、=IF([Month]<=[FYE],[Year],[Year]+1) のように参照します。 この方法では、さまざまな数式で、会計年度の終了月を柔軟に参照できます

FiscalMonth

=IF([Month]<= 6, 6+[Month], [Month]- 6)

この数式では、[Month] の値が 6 以下である場合には 6 に [Month] の値を加算し、6 より大きい場合には [Month] の値から 6 を減算するように指定しています。

[会計月] 列

FiscalQuarter

=INT(([FiscalMonth]+2)/3)

FiscalQuarter で使っている数式は、カレンダー年度の Quarter で使った式とほぼ同じです。 唯一の違いは、[Month] の代わりに [FiscalMonth] を指定していることです。

[会計四半期] 列

祝日や特別な日付

場合によっては、特定の日付が祝日またはその他の特別な日付を示す日付列を含める必要があります。 たとえば、ピボットテーブルに祝日フィールドをスライサーまたはフィルターとして追加して、年始の売上総額を集計するような場合です。 これ以外にも、該当する日付を他の日付列やメジャーで除外するような場合も考えられます。

祝日や特別な日付を含める手順は、とても簡単です。 対象の日付を含むテーブルを Excel で作成できます。 このテーブルをコピーするか、[データ モデルに追加] コマンドでデータ モデルにリンク テーブルとして追加できます。 ほとんどの場合、このテーブルとカレンダー テーブルの間にリレーションシップを作成する必要はありません。 それを参照する数式は 、LOOKUPVALUE 関数を使用して 値を返します。

次の例に示すのは、Excel で作成したテーブルであり、日付テーブルに追加する祝日を含んでいます。

日付

休日

2010/01/01

新年

2010/11/25

感謝祭

2010/12/25

クリスマス

2011/01/01

新年

2011/11/24

感謝祭

2011/12/25

クリスマス

2012/01/01

新年

2012/11/22

感謝祭

2012/12/25

クリスマス

2013/01/01

新年

2013/11/28

感謝祭

2013/12/25

クリスマス

2014/11/27

感謝祭

2014/12/25

クリスマス

2014/01/01

新年

2014/11/27

感謝祭

2014/12/25

クリスマス

2015/01/01

新年

2014/11/26

感謝祭

2015/12/25

クリスマス

2016/01/01

新年

2016/11/24

感謝祭

2016/12/25

クリスマス

日付テーブルに、Holiday という名前の列を作成して、次のような数式を使います。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

この数式をより詳しく見ていきましょう。

LOOKUPVALUE 関数を使って、Holidays テーブルの Holiday 列から値を取得します。 最初の引数には、結果の値になる列を指定します。 具体的には、必要な値が返されるHolidays テーブルの Holiday 列を指定します。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

次に、2 番目の引数には、検索する対象の日付を含んでいる検索列を指定します。 次のように、Holidays テーブルの Date 列を指定します。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

最後に、Holiday テーブルで検索する日付を含んでいる Calendar テーブルの列を指定します。 当然、Calendar テーブルの Date 列です。

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

Holiday 列は、Holidays テーブルの日付と一致した日付値を含んでいる各行の祝日名を返します。

Holiday テーブル

カスタム カレンダー - 13 × 4 週間

小売業や食品サービスなど、一部の組織では、多くの場合、4 週間の期間を 13 回など、さまざまな期間について報告します。 13 の 4 週間のカレンダーでは、各期間は 28 日間です。したがって、各期間には、4 つの月曜日、4 つの火曜日、4 つの水曜日が含まれるなどです。 各期間には同じ日数が含まれるので、通常、祝日は毎年同じ期間に入ります。 任意の日に期間を開始することができます。 予定表や会計年度の日付と同様に、DAX を使用して、ユーザー設定の日付を含む追加の列を作成できます。

次の例では、最初の期間が会計年度の第 1 日曜日で始まります。 この場合、会計年度は 7/1 で始まります。

Week

この値は、会計年度の第 1 週で始まる週番号を返します。 この例では、第 1 週は日曜日で始まるので、Calendar テーブルの最初の会計年度の第 1 週は実際には 7/4/2010 で始まり、Calendar テーブルの最終週まで続きます。 この値自体が分析で使われることはありませんが、他の 28 日の期間を計算する場合に必要です。

=INT([date]-40356)/7)

この数式をより詳しく見ていきましょう。

まず、次のように、Date 列からの値を整数として返す数式を作成します。

=INT([date])

最初の会計年度で第 1 日曜日を検索します。 これに該当するのは 7/4/2010 です。

Week 列

次のように、値から 40356 (前会計年度の最後の日曜日である 6/27/2010 に該当する整数) を減算して、Calendar テーブルの開始日からの日数を取得します。

=INT([date]-40356)

次のように、結果を 7 (1 週間の日数) で除算します。

=INT(([date]-40356)/7)

結果は次のように表示されます。

Week 列

Period

このカスタム カレンダーの期間は 28 日で構成され、常に日曜日で始まります。 この列は、最初の会計年度の第 1 日曜日で始まる期間の番号を返します。

=INT(([Week]+3)/4)

この数式をより詳しく見ていきましょう。

まず、次のように、Week 列からの値を整数として返す数式を作成します。

=INT([Week])

次のように、値に 3 を加算します。

=INT([Week]+3)

次のように、結果を 4 で除算します。

=INT(([Week]+3)/4)

結果は次のように表示されます。

[期間] 列

PeriodFiscalYear

この値は、期間の会計年度を返します。

=INT(([Period]+12)/13)+2008

この数式をより詳しく見ていきましょう。

まず、Period から値を取得して 12 を加算する数式を作成します。

= ([Period]+12)

会計年度には 28 日の期間が 13 個存在するので、結果を 13 で除算します。

=(([Period]+12)/13)

テーブルの最初の年である 2010 を加算します。

=(([Period]+12)/13)+2010

最後に、次のように、INT 関数を使って、結果の小数部を削除し、整数を取得してから 13 で除算します。

=INT(([Period]+12)/13)+2010

結果は次のように表示されます。

PeriodFiscalYear 列

PeriodInFiscalYear

この値は、各会計年度の最初の期間 (日曜日で開始) で始まる、1 ~ 13 の値を返します。

=IF(MOD([Period],13), MOD([Period],13),13)

この数式は少し複雑なので、わかりやすいように説明から始めます。 この数式では、[Period] の値を 13 で除算して、年に含まれる期間の番号 (1 ~ 13) を取得します。 取得した値が 0 である場合は、13 を返します。

まず、Period の値を 13 で除算したときの余りを返す数式を作成します。 MOD ( 数学 関数と三角関数) は次のように使用できます。

=MOD([Period],13)

ほとんどの場合、この例のカレンダー日付テーブルの最初の 5 日間のように、期間の値が 0 である場合を除き、この結果は最初の会計年度内に含まれるためです。 これは IF 関数で行います。 結果が 0 の場合は、次のように 13 を返します。

=IF(MOD([Period],13),MOD([Period],13),13)

結果は次のように表示されます。

PeriodInFiscalYear 列

ピボットテーブルのサンプル

次の画像は、Sales ファクト テーブルの SalesAmount フィールドを VALUES に、Calendar 日付ディメンション テーブルの PeriodFiscalYear および PeriodInFiscalYear フィールドを ROWS にそれぞれ含んでいるピボットテーブルを示しています。 SalesAmount は、会計年度と会計年度の 28 日の期間に基づくコンテキストで集計されます。

会計年度のピボットテーブルの例

リレーションシップ

日付テーブルをデータ モデルに作成したら、ピボットテーブルやレポートでデータを参照し、日付ディメンション テーブルの列に基づいてデータを集計するため、取引データを含むファクト テーブルと日付テーブルの間にリレーションシップを作成する必要があります。

日付に基づいたリレーションシップを作成する必要があるので、間違いなく、値が datetime (Date) データ型である列の間にリレーションシップを作成しましょう。

ファクト テーブルの各日付値に対して、日付テーブル内の関連する参照列は、一致する値を含んでいる必要があります。 たとえば、Sales ファクト テーブル内の行 (取引記録) で、DateKey 列に 8/15/2012 12:00 AM という値を含んでいる場合、日付 (Calendar) テーブルの関連する Date 列に、対応する値が必要です。 これが、ファクト テーブルのあらゆる日付を網羅する、連続した範囲の日付を日付テーブルの日付列に含める最も重要な理由の 1 つです。

ダイアグラム ビューのリレーションシップ

注: 各テーブルの日付列は同じデータ型 (Date) であることが必須ですが、各列の書式が問題になることはありません。

注: PowerPivot で 2 つのテーブル間にリレーションシップを作成できない場合、日付フィールドに格納されている日付や時刻の精度が異なる可能性があります。 列の書式によっては、値の表示は同じに見えても、実際に格納されている値は異なる可能性があります。 詳しくは、「時刻を操作する」をご覧ください。

注: リレーションシップで整数代理キーを使用しないようにします。 リレーショナル データ ソースからデータをインポートする場合、多くの場合、日付と時刻の列は代理キーで表されます。これは、一意の日付を表す整数列です。 Power Pivot では、整数の日付/時刻キーを使用してリレーションシップを作成しないようにし、代わりに、日付データ型の一意の値を含む列を使用する必要があります。 代理キーの使用は従来のデータ ウェアハウスではベスト プラクティスと見なされますが、Power Pivot では整数キーは必要ではなく、異なる日付期間でピボットテーブルの値をグループ化するのが難しくなる可能性があります。

リレーションシップを作成しようとして、型の不一致エラーが発生した場合、ファクト テーブルの列が Date データ型ではない可能性があります。 この現象は、PowerPivot が日付以外のデータ型 (通常はテキスト データ型) を日付データ型に自動的に変換できない場合に発生します。 この場合でも、ファクト テーブルの列を使えますが、新しい計算列で DAX 数式を使ってデータを変換する必要があります。 付録で後述の「テキスト データ型の日付を日付データ型に変換する」をご覧ください。

複数のリレーションシップ

場合によっては、複数のリレーションシップを作成するか、複数の日付テーブルを作成する必要があります。 たとえば、Sales ファクト テーブルに DateKey、ShipDate、ReturnDate などの複数の日付フィールドがある場合、それらはすべてカレンダーの日付テーブルの Date フィールドとのリレーションシップを持つ可能性がありますが、アクティブなリレーションシップは 1 つのみです。 この場合、DateKey はトランザクションの日付、つまり最も重要な日付を表すので、アクティブなリレーションシップとして最適 です 。 他のユーザーには非アクティブなリレーションシップがあります。

次のピボットテーブルでは、会計年度および会計四半期別に売上合計を計算します。 数式 Total Sales:=SUM([SalesAmount]) を含む Total Sales という名前のメジャーは VALUES に配置され、Calendar 日付テーブルの FiscalYear および FiscalQuarter フィールドは ROWS に配置されます。

会計四半期ごとの売上総額ピボットテーブル ピボットテーブル フィールド リスト

DateKey の取引日付で売上合計を集計するので、この単純なピボットテーブルは正常に機能します。 Total Sales メジャーは DateKey の日付を使っており、Sales テーブルの DateKey と Calendar 日付テーブルの Date 列の間にリレーションシップがあるので、会計年度と会計四半期で集計されます。

非アクティブなリレーションシップ

ただし、取引日ではなく出荷日で売上合計を合計する場合 は、どうでしょうか。 Sales テーブルの ShipDate 列とカレンダー テーブルの [日付] 列の間にリレーションシップが必要です。 このリレーションシップを作成しない場合、集計は常にトランザクションの日付に基づいて行います。 ただし、アクティブにできるリレーションシップは 1 つしかなくても複数のリレーションシップを持つ可能性があります。トランザクションの日付が最も重要なので、予定表テーブルとのアクティブなリレーションシップが取得されます。

この場合、ShipDate には非アクティブなリレーションシップが含まれるので、出荷日に基づいてデータを集計するために作成されたメジャー数式では 、USERELATIONSHIP 関数を使用して非アクティブなリレーションシップを指定する必要があります。

たとえば、Sales テーブルの ShipDate 列と Calendar テーブルの Date 列の間に非アクティブなリレーションシップを設定すれば、出荷日に基づいて売上合計を集計するメジャーを作成できます。 このリレーションシップを指定するには、次のような数式を使います。

Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

この数式では単純に、SalesAmount の集計を計算していますが、Sales テーブルの ShipDate 列と Calendar テーブルの Date 列の間のリレーションシップを使ってフィルター処理しています。

これで、ピボットテーブルを作成して、Total Sales by Ship Date メジャーを VALUES に、FiscalYear と FiscalQuarter を ROWS に配置すると、同じ Grand Total が得られます。ただし、会計年度と会計四半期の他の集計値はすべて、取引日ではなく、出荷日に基づいているので、異なる値になります。

出荷日別の売上合計 (ピボットテーブル) ピボットテーブル フィールド リスト

非アクティブなリレーションシップを使うと、日付テーブルを 1 つだけ使うことができますが、(Total Sales by Ship Date の場合のように) メジャーの数式で非アクティブなリレーションシップを参照する必要があります。 これとは別に、複数の日付テーブルを使う方法があります。

複数の日付テーブル

ファクト テーブルの複数の日付列を操作する方法として、複数の日付テーブルを作成して、それぞれの間にアクティブなリレーションシップを個別に作成します。 もう一度、Sales テーブルの例を見てみましょう。 データを集計する条件として、日付を含む、次の 3 つの列があります。

  • DateKey - 各取引の販売日を含む。

  • ShipDate – 販売された商品が顧客に出荷された日付と時刻を含む。

  • ReturnDate – 返された 1 つ以上のアイテムが受信された日付と時刻。

トランザクションの日付を含む DateKey フィールドが最も重要な点に注意してください。 これらの日付に基づいて集計のほとんどを行うので、最も確実にカレンダー テーブルの Date 列との間にリレーションシップが必要になります。 ShipDate と ReturnDate と Calendar テーブルの Date フィールドの間に非アクティブなリレーションシップを作成したくない場合、特別なメジャー数式が必要な場合は、出荷日と返される日付の追加の日付テーブルを作成できます。 その後、その間にアクティブなリレーションシップを作成できます。

ダイアグラム ビューでの複数の日付テーブルとのリレーションシップ

この例では、ShipCalendar という名前の別の日付テーブルを作成しました。 これはもちろん、追加の日付列を作成する意味で、これらの日付列は別の日付テーブルに含まれるため、予定表テーブル内の同じ列と区別する方法で名前を付けしたいと考えます。 たとえば、ShipYear、ShipMonth、ShipQuarter という名前の列を作成しました。

ピボットテーブルを作成して、Total Sales メジャーを VALUES に、ShipFiscalYear および ShipFiscalQuarter を ROWS にそれぞれ配置した場合には、非アクティブなリレーションシップと特別な Total Sales by Ship Date 計算フィールドを作成した場合と同じ結果が得られます。

出荷日別の売上合計 (ピボットテーブル) と出荷日カレンダー ピボットテーブル フィールド リスト

これらの各アプローチでは、慎重に検討する必要があります。 1 つの日付テーブルで複数のリレーションシップを使用する場合は、USERELATIONSHIP 関数を使用して非アクティブなリレーションシップを転送する特別なメジャーを作成する必要がある場合があります。 一方、フィールド リストでは複数の日付テーブルを作成すると混乱を招く可能性があります。また、データ モデルのテーブルが多くなるので、より多くのメモリが必要になります。 最適な機能を試してください。

日付テーブルのプロパティ

日付テーブルのプロパティでは、TOTALYTD、PREVIOUSMONTH、DATESBETWEEN など、タイム インテリジェンス関数が正確に機能するうえで必要なメタデータを設定します。 このような関数を使って計算を実行するときに、PowerPivot の数式エンジンは既定のプロパティに従って、必要な日付を取得します。

警告: このプロパティが設定されていない場合、DAX タイム インテリジェンス関数を使ったメジャーは、正しい結果を返さない可能性があります。

日付テーブルのプロパティを設定するときには、日付テーブルと、その日付テーブルの Date (datetime) データ型の日付列を指定します。

[日付テーブルとしてマーク] ダイアログ

日付テーブルのプロパティを設定する方法

  1. PowerPivot ウィンドウで、Calendar テーブルを選びます。

  2. [デザイン] タブで、[日付テーブルとしてマーク] をクリックします。

  3. [日付テーブルとしてマーク] ダイアログ ボックスで、一意の値を含む列と Date データ型を選びます。

時刻を操作する

Excel または SQL Server では、Date データ型の日付値はすべて、実際には数値です。 この数値に含まれているのは、時刻を参照する数字です。 多くの場合、1 つ 1 つの行の時刻は、深夜 12:00 を基準にしています。 たとえば、Sales ファクト テーブルの DateTimeKey フィールドに 10/19/2010 12:00:00 AM のような値が含まれている場合は、値が日レベルの精度であることを意味しています。 また、10/19/2010 8:44:00 AM のように、DateTimeKey フィールドの値に時刻が含まれている場合は、値が分レベルの精度であることを意味しています。 値は、時間レベルや秒レベルの精度である可能性もあります。 時刻値の精度は、日付テーブルを作成する方法や、日付テーブルとファクト テーブルの間にリレーションシップを作成する方法に、大きな影響を及ぼします。

日レベルの精度または時間レベルの精度のどちらでデータを集計するのか決定する必要があります。 つまり、場合によっては、日付テーブルの Morning、Afternoon、Hour といった列をピボットテーブルの Row、Column、Filter といった領域の日時フィールドとして使う必要があるということです。

注: 日は、DAX タイム インテリジェンス関数で使用できる最小の時間単位です。 時刻値を使う必要がない場合は、データの精度を下げて最小単位として日を使う必要があります。

時間レベルでデータを集計する場合は、時刻を含む日付列が日付テーブルに必要になります。 実際には、時間、さらに場合によっては、分、日、日付範囲の年ごとに 1 つの行を含む列が必要になります。 この理由として、リレーションシップを作成するには、ファクト テーブルの DateTimeKey 列と日付テーブルの日付列の間に、一致する値が必要であることが挙げられます。 もちろん範囲に含まれる年数が増えると、日付テーブルが大きくなります。

ただし、実際には、日単位でデータを集約することがほとんどです。 つまり、Year、Month、Week、Day of Week のような列をピボットテーブルの Row、Column、Filter 領域のフィールドとして使うことになります。 この場合、日付テーブルの日付列には、前述のように、各年の日を 1 日 1 行の形式で網羅した行だけを含んでいる必要があります。

たとえば、日付列の精度が時間レベルであるにもかかわらず、日レベルで集約するだけでよい場合に、ファクト テーブルと日付テーブルの間にリレーションシップを作成するには、ファクト テーブルに変更を加えて、日付列の値を日の値に切り詰めた新しい列を作成する必要があります。 つまり、10/19/2010 8:44:00AM のような値を 10/19/2010 12:00:00 AM に変換します。 これで値が一致するので、この新しい列と日付テーブルの日付列の間にリレーションシップを作成できます。

それでは、実際の例を見てみましょう。 この画像は、Sales ファクト テーブルの DateTimeKey 列を示しています。 このテーブルのデータのすべての集計は、年、月、四半期などの予定表の日付テーブルの列を使用して、日レベルに限定する必要があります。値に含まれる時刻は関係ありません。実際の日付のみです。

DateTimeKey 列

データを時間レベルで分析する必要はないので、あらゆる年、日、時、分を 1 行ずつ網羅した行を Calendar 日付テーブルの Date 列に含める必要はありません。 したがって、日付テーブルの Date 列は、次のようになります。

PowerPivot の Date 列

Sales テーブルの DateTimeKey 列と Calendar テーブルの Date 列の間にリレーションシップを作成するには、Sales ファクト テーブルに新しい集計列を作成し 、TRUNC 関数を使用して DateTimeKey 列の日付と時刻の値を、カレンダー テーブルの Date 列の値と一致する日付値に切り捨てることができます。 数式は次のようになります。

=TRUNC([DateTimeKey],0)

この結果、各行に DateTimeKey 列の日付と 12:00:00 AM という時刻を含む、新しい列 (名前は DateKey) が作成されます。

DateKey 列

これで、新しい列 (DateKey) と Calendar テーブルの Date 列の間にリレーションシップを作成できます。

同様に、Sales テーブルに計算列を作成して、DateTimeKey 列の時刻精度を時間レベルの精度に下げることができます。 この場合、TRUNC 関数は役に立ちませんが、他の DAX の日付と時刻の関数を使って、新しい値を抽出して時間レベルの精度に連結し直すことができます。 次のような数式を使うことができます。

= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)

新しい列は次のようになります。

DateTimeKey 列

日付テーブルの Date 列に時間レベルの精度の値が含まれているとして、その値の間にリレーションシップを作成できます。

日付をより使いやすくする

日付テーブルに作成する日付列の多くは、他のフィールドに必要ですが、分析に役立つすべてではありません。 たとえば、この記事で参照および表示した Sales テーブルの DateKey フィールドは重要です。これは、トランザクションごとに、そのトランザクションが特定の日時に発生すると記録されます。 ただし、分析とレポートの観点から見て、ピボット テーブルまたはレポートの行、列、フィルター フィールドとして使用することはできません。

同様に、この例では、Calendar テーブルの Date 列はとても役に立ち、実際に重要ですが、ピボットテーブルのディメンションとして使うことはできません。

テーブルと列を可能な限り便利に保ち、ピボットテーブルまたは Power View レポートのフィールド リストを簡単に移動するには、クライアント ツールで不要な列を非表示にすることが重要です。 また、特定のテーブルも非表示にすることもできます。 前に示した祝日テーブルには、カレンダー テーブルの特定の列に重要な休日の日付が含まれていますが、ピボットテーブルのフィールドとして祝日テーブル自体の日付列と休日列を使用することはできません。 ここでも、フィールド リストを移動しやすくするために、Holidays テーブル全体を非表示にすることができます。

日付を操作するもう 1 つの重要な側面は、名前付け規則です。 Power Pivot のテーブルと列には、必要な名前を付けできます。 ただし、特にブックを他のユーザーと共有する場合は、適切な名前付け規則を使用すると、フィールド リストだけでなく、Power Pivot や DAX の数式でも、テーブルと日付を簡単に識別できます。

データ モデルに日付テーブルを追加したら、データを最大限に活用するため、メジャーの作成を開始できます。 今年の売上合計を集計するような簡単なものから、一意の日付を特定の範囲でフィルター処理する必要がある複雑なものまで、計算フィールドには、さまざまな種類があります。 詳細については 、「Power Pivot のメジャー」と「タイム インテリジェンス関数 」を参照してください

付録

テキスト データ型の日付を日付データ型に変換する

場合によっては、取引データを含むファクト テーブルに、テキスト データ型の日付が格納されていることがあります。 つまり、2012-12-04T11:47:09 のように表示される日付が日付ではないか、少なくとも PowerPivot で認識できる日付型ではないということです。 実際には日付として読み取ることができるテキストです。 ファクト テーブルの日付列と日付テーブルの日付列の間にリレーションシップを作成するには、この両方の列が Date データ型である必要があります。

通常、日付列のデータ型をテキスト データ型から日付データ型に変更する場合、PowerPivot は日付を解釈して本来の日付データ型に自動的に変換することができます。 PowerPivot がデータ型を変換できない場合、型の不一致エラーが返されます。

ただしその場合でも、日付を本来の日付データ型に変換できます。 新しい計算列を作成し、DAX の数式を使って、テキスト文字列から年、月、日、時刻などを解釈した上で、PowerPivot で本来の日付として読み取ることができるように連結し直すことができます。

この例では、Sales という名前のファクト テーブルを PowerPivot にインポートしました。 このテーブルには、DateTime という名前の列が含まれています。 値は次のようになります。

ファクト テーブルの [日付/時刻] 列

PowerPivot の [ホーム] タブの [書式] グループで [データ型] を確認した場合、テキスト データ型であることがわかります。

リボンでのデータ型

データ型が一致しないので、日付テーブルの DateTime 列と Date 列の間にリレーションシップを作成することはできません。 データ型を Date に変更しようとすると、型の不一致エラーが返されます。

不一致エラー

この場合、PowerPivot はデータ型をテキストから日付に変換できません。 この列をそのまま使うことはできますが、本来の日付データ型に変換するには、新しい列を作成し、テキストを解釈して、PowerPivot で Date データ型に変換できる値に作成し直す必要があります。

この記事で前述の「時刻を操作する」セクションで説明したように、時間レベルの精度で分析する必要がなければ、ファクト テーブルの日付を日レベルの精度に変換してください。 その点を考慮して、新しい列の値を日レベルの精度に設定します (時刻を除外)。 次の数式を使えば、DateTime 列の値を日付データ型に変換すると同時に、時間レベルの精度データを削除できます。

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

これで、新しい列が作成されました (名前は Date)。 PowerPivot は値が日付であると認識して、データ型を Date に自動的に設定します。

ファクト テーブルの Date 列

時間レベルの精度を保つ必要がある場合は、時、分、秒を範囲に含めるように数式を拡張するだけです。

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

これで、Date 列を Date データ型に設定したので、この列と日付テーブルの日付列の間にリレーションシップを作成できます。

その他のリソース

PowerPivot で使用する日付

PowerPivot における計算

クイック スタート: 30 分で学ぶ DAX の基礎

データ分析式リファレンス

DAX リソース センター

ヘルプを表示

スキルを磨く
トレーニングの探索
新機能を最初に入手
Microsoft Office Insider に参加

この情報は役に立ちましたか?

翻訳品質にどの程度満足していますか?
どのような要因がお客様の操作性に影響しましたか?

フィードバックをお送りいただきありがとうございます!

×