スキップしてメイン コンテンツへ

PowerPivot in Excel の日付テーブルとその作成方法について

注: 最新のヘルプ コンテンツをできるだけ早く、お客様がお使いの言語で提供したいと考えております。 このページは、自動翻訳によって翻訳されているため、文章校正のエラーや不正確な情報が含まれている可能性があります。 私たちの目的は、このコンテンツがお客様の役に立つようにすることです。 お客様にとって役立つ情報であったかどうかを、このページの下部でお知らせください。 簡単に参照できるように、こちらに英語の記事があります。

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

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

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

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

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

  • 時刻を操作する方法。

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

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

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

目次

日付テーブルについて

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

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

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

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

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

日付と時刻の関数 (DAX)

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

四半期

月の名前

数式の例 (会計年度)

FiscalYear

FiscalMonth

FiscalQuarter

祝日や特別な日付

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

期間

PeriodFiscalYear

PeriodInFiscalYear

リレーションシップ

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

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

複数の日付テーブル

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

時刻を操作する

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

付録

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

その他のリソース

日付テーブルについて

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

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 からインポートする。

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

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

データ ウェアハウスやその他のリレーショナル データベースから一部またはすべてのデータをインポートする場合は、日付テーブルと残りのデータをインポートしての間のリレーションシップが既にがある可能性があります。 形式の日付とは可能性が高い、ファクト データ内の日付とし、一致日付可能性がありますも、過去の開始後に移動するまで縮小します。 インポートする日付のテーブルは大きくなり、データ モデルに含める必要がありますだけでなく日付の範囲が含まれて可能性があります。 Powerpivot のテーブルのインポート ウィザードの高度なフィルター機能を使用すると、日付だけと必要な特定の列を選択します。 ブックのサイズを縮小して、パフォーマンスを向上させるこのことができますが大幅にします。

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

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

ほとんどの場合、インポートされたテーブルでは既に存在するために、会計年度、週、月の名前などのような追加の列を作成する必要はありません。 ただし、場合によっては、データ モデルにインポートする日付テーブルを作成したらならない場合がある特定のレポートのニーズに応じて、追加の日付の列を作成します。 しかし、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 で独自の日付テーブルを作成して、データ モデルにコピーするような場合です。 しかし、Powerpivot の新しい日付列を作成することが非常に簡単日付と時刻の関数の DAX します。

ヒント: まだ、DAX を使って、機能がしない場合、学ぶがまずクイック スタート: 30 分で DAX の基礎を学習Office.com.

日付と時刻の関数 (DAX)

Excel の数式の日付と時刻の関数の続ける場合は、[可能性が高いなりますについてよく理解、日付と時刻の関数。 これらの関数は Excel の対応する関数とよく似ていますが、次のようにいくつかの重要な違いがあります。

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

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

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

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

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

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

数式では、使用できるその他の多くの DAX 関数があります。 たとえば、説明されている数式の多くここを使用数学/三角関数MODTRUNCなど場合などの論理関数書式設定などの文字列関数の詳細についてはその他の DAX 関数は、この記事の後半の他のリソースのセクションを参照してください。

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

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

=YEAR([date])

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

Year 列

=MONTH([date])

この数式で、どれだけのような YEAR 関数のことができますだけで関数を使用して、Date 列から、月の値を返します。

Month 列

四半期

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

この数式で、 INT関数を使用して整数として日付の値を返します。 指定 INT 関数の引数は、[月] 列の値、2 を追加し、[3、四半期に 1 ~ 4 で除算します。

[四半期] 列

MonthName

=FORMAT([date],"mmmm")

この数式で、月の名前を取得する関数を使用して、書式設定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月の値が以下の値を 6 かどうかをします。 2 番目の引数の月の値が 6 以下の場合は、[から値を返す 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 週間

製品やサービスを食事など、組織によっては、13 × 4 週間など、さまざまな期間の頻度レポートを作成します。 13 週間期間予定表を各期間は 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)

この、ほとんどの場合、結果しますを除く期間の値が 0 日付しない内に収まって最初の会計年度などの例の予定表の日付テーブルの最初の 5 日であるため、目的の結果。 この、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 つのテーブル間にリレーションシップを作成できない場合、日付フィールドに格納されている日付や時刻の精度が異なる可能性があります。 列の書式によっては、値の表示は同じに見えても、実際に格納されている値は異なる可能性があります。 詳しくは、「時刻を操作する」をご覧ください。

注: リレーションシップで整数の代理キーを使用しないでください。 リレーショナル データ ソースからデータをインポートするときに多くの場合、日付し、時刻の列は、一意の日付を表す整数型の列には、代理キーで表されます。 します。 Powerpivot の整数日付/時刻のキーを使用してリレーションシップを作成しないようにしを日付データ型の一意の値を含む列の代わりに、使用して必要があります。 代理キーの使用は、従来のデータ ウェアハウスのベスト プラクティスと見なされます] が整数キーは 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 列と Calendar テーブルの Date 列間のリレーションシップが必要です。 リレーションシップを作成したしない場合、集計は常にに基づいてトランザクションの日付。 ただしがあっても、複数のリレーションシップも関わらず、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 フィールドのトランザクションの日付が最も重要な注意してください。 これらの日付に基づいて、Calendar テーブルの Date 列間のリレーションシップします間違いなく、集計のほとんどが行います。 予定表で ShipDate と ReturnDate、日付フィールドの間の非アクティブなリレーションシップを作成するのには必要ないがある場合必要になるため、特殊なメジャーの数式を作成できますの他の日付テーブル戻り値の日付と出荷日。 [アクティブな関係を作成できます。

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

この例で 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 に変換します。 これで値が一致するので、この新しい列と日付テーブルの日付列の間にリレーションシップを作成できます。

それでは、実際の例を見てみましょう。 売上ファクト テーブルの DateTimeKey 列が示されます。 すべての次の表のデータの集計する必要はのみの曜日にレベル、年、月、四半期などのような予定表の日付テーブルに列を使用しています。値に含まれている時刻ですが、実際の日付のみです。

DateTimeKey 列

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

PowerPivot の Date 列

Calendar テーブルの Sales テーブル内の DateTimeKey 列と Date 列間のリレーションシップを作成するには、売上ファクト テーブルの新しい計算列を作成して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 テーブルには、重要なは、特定のでは、Calendar テーブルの列の日付を使用できないし、休日テーブルの列で、休日自体ピボット テーブルのフィールドとして祝日データが含まれています。 ここで、もう一度するにはフィールド リストを簡単に移動、隠すことができます全体、Holidays テーブル。

データの処理の重要な機能が名前付け規則。 対象 Power Pivot のテーブルと列の名前できます。 点で保持する、他のユーザーとブックを共有している場合に特に適切な名前付け規則やすくテーブルとの日付、だけでなくフィールド リスト] しますが、も Powerpivot の 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 の基礎

Data Analysis Expressions リファレンス

DAX リソース センター

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

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

ご意見をいただきありがとうございます。

フィードバックをお寄せいただき、ありがとうございます。Office サポートの担当者におつなぎいたします。

×