カレンダーテーブルを実際に作成する~カレンダーテーブルを活用するツアー~

今回は前回の内容に従って、カレンダーテーブルを実際に作成してみましょう!

カレンダーテーブルを作成する時にDAX(エクセルのワークシート関数のようなもの)も使用します

元のデータはこちらの3つのテーブルになります

ですので当初のリレーションはこのようになっています

こちらにカレンダーテーブルを組み込む形になります

新しいテーブルの作成

カレンダーテーブルの作成は最初、こちらをクリックするところから始まります

すると数式が入力できるようになります

****** ブログ管理人・ガッツ鶴岡のUdemy講座 ******

「Power Queryを極めたい人必見!!M関数講座」

  ⇒ 詳細はこちらから

M言語の世界に少し踏み込むだけで「クエリの関数化」「let式による複数ステップ集約」など、クエリ作成の自由度は格段に上がります!

****************************

左辺をCalendarとし、右辺にCaを入力すると、入力するDAXの候補がでてきます

こちらの候補から「CALENDAR」をTabキーで選択してください

CALENDAR()の〇括弧:()の中には、連続した日付を作成する際の開始日終了日を指定します

YEAR/年とMONTH/月の列の作成

連続した日付データができたので、今度は日付データを使用してYEARとMONTHの列を作成します

上の画像の「新しい列」をクリック後、まずはYEARの列を作成します

YEARの列を作成するにはDAX「YEAR」を指定します

その際に、コツが一つあります

丸括弧:()の中でを指定すると()中に入れる列名の候補が選択できます

今回は’Calendar[Date]の列を指定して、日付列(2021/04/01⇒2021)を年に変換して列を作成します

今、DAX:YEARをして列を作成しましたが、通常のエクセルのワークシート関数を使用した時との違いは、セルではなく列単位で関数を使用しているという点です

MONTHの列もYEARの列と同様に作成します

会計年度の調整

前回も触れましたが、3月決算の場合には会計年度を調整する必要があります

例えば、2022年1月の場合には、上記のYEAR関数で作成した列には「2022」と表示されます

ただ実際には会計年度は2022から1を引いた「2021」になります

ですので、MONTH列をもとにしてIF関数により1月~3月の会計年度を調整します

if(MONTH([Date])<4,YEAR([Date])-1,YEAR([Date]))

ちなみに四半期の列を作成するQUARTER関数を使用すると次のようになります

4月が第2QUARTERとなります

これは12月決算をもとにしてDAXが作成されているからです

この場合も3月決算にあわせてIF関数を使用します

IF(MONTH([Date])<4,4,if(MONTH([Date])<7,1,IF(MONTH([Date])<10,2,3))

リレーションシップの作成

カレンダーテーブルをひとまず、形にはしました

これだけでは、カレンダーテーブルを活用することはできません

他のテーブルとリレーションシップを作成・紐づけを行う必要があります

リレーションシップを作成するには、以下の画面で操作が必要です

こちらの画面で売上データの「日付データ」とカレンダーテーブルの「Date」を紐づけます

操作はいたって簡単です

該当する列の上でドラックをするだけです

これで下の画像のようなリレーション(売上データ:多⇒Calendar:1)が完成します

これで売上データとカレンダーテーブルを一緒に活用できるようになりました

では、レポート画面で「マトリックス」を作成してみましょう

マトリックスの行にカレンダーテーブルの「YEAR」を配置します

YEARの配置もドラックで行います

そしてマトリックスの値に売上データの売上金額を配置します

すると上記の画像のように「年」毎に売上金額の合計が算出されています

これはカレンダーテーブルと「多:1」の紐づけが行われているからです

では試しに、リレーションを消してみます

すると、以下のように2021年も2022年も同じ金額になってしまいます

これは、年ごとの多:1の紐づけが行われないまま、単純に売上金額の合計が算出されるからです

<まとめ>

今回はカレンダーテーブル自体を作成しました

エクセルと違い、テーブルも列も一から作る点がとても斬新だと思います

ただ、これだけだとまだ実用的なカレンダーテーブルとはいえません

次回、調整を加えていきたいと思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です